How to archive data from my production database?
Is there a tool or command built-into SQL Server for this?I often see this type of questions in the SQL Server newsgroups and mailing lists. So, decided to come up with a simple article, that's just good enough to get you started.Archiving is the process of getting rid of old data (or data that is not valid anymore) from the main production databases. There is no built-in command or tool for archiving databases. In this article I'm going to show you how to implement this process.Why archive in the first place? By archiving old data, you can keep your databases smaller and more manageable. With smaller tables, your index rebuild times and backup/restore times will be under control. You will also see performance gains on certain queries that either scan a larger portion of the table or clustered index.Depending on the business needs, one might choose to either delete the unwanted data, or copy the data to a history table and delete that data from the production database.Implementing an archival process is not that complicated. The three logical steps involved in this process are:
Identifying the data to be archived.
Optionally copying the identified data to a history table.
Deleting the identified data from the production database.
You may not always be archiving just a single table. You need to understand how different tables in the database are related to each other through foreign key relationships and then, sequence your delete/archive process, so that you are not violating any of the constraints.
Here's an example scenario of archiving all the orders taken more than 6 months ago, from the Orders and OrderDetails tables. The following stored procedure will copy orders older than 6 months into similar tables of a different database called Archive, and delete those rows from the main database: CREATE PROC dbo.ArchiveData( @CutOffDate datetime = NULL)ASBEGIN SET NOCOUNT ON IF @CutOffDate IS NULL BEGIN SET @CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP) END ELSE BEGIN IF @CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) BEGIN RAISERROR ('Cannot delete orders from last three months', 16, 1) RETURN -1 END END BEGIN TRAN INSERT INTO Archive.dbo.Orders SELECT * FROM dbo.Orders WHERE OrderDate < @CutOffDate IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16, 1) RETURN -1 END INSERT INTO Archive.dbo.OrderDetails SELECT * FROM dbo.OrderDetails WHERE OrderID IN ( SELECT OrderID FROM dbo.Orders WHERE OrderDate < @CutOffDate ) IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error occured while copying data to Archive.dbo.OrderDetails', 16, 1) RETURN -1 END DELETE dbo.OrderDetails WHERE OrderID IN ( SELECT OrderID FROM dbo.Orders WHERE OrderDate < @CutOffDate ) IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16, 1) RETURN -1 END DELETE dbo.Orders WHERE OrderDate < @CutOffDate IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1) RETURN -1 END IF @@TRANCOUNT > 0 BEGIN COMMIT TRAN RETURN 0 END END
You just need to call this stored procedure without any parameters, to get rid of all orders older than 6 months. As you can see, you can pass an optional date parameter, to customize the archiving process. Care is taken not to delete orders placed in the last three months. Depending on your requirements, you can schedule this stored procedure as a job, using SQL Server Agent, so that it runs every week or fortnight.This type of jobs tend to block the table while they are running. So, it's better to schedule these jobs for off-peak hours. Also, keep an eye on the transaction log space utilization. In case of any issues with log space, consider archiving in smaller batches. Here's one thing you should consider: If you are using transactional replication to maintain a reporting database, data will be deleted from the reporting databses also, once you run the archival stored procedure. If that's not what you wanted (that is, maintain complete data in the reporting database), then you might want to consider publishing stored procedures, instead of tables, and customize the archiving stored procedure on the subscriber, so that it does nothing.
Wednesday, August 02, 2006
Best Practices - SQL Server 2000
Best Practices - SQL Server 2000
SQL Server is a very powerful tool when used properly. It can also come to a screeching halt if left to rot with no maintenance and poor planning. While the program itself is highly scalable it is still subject to performance bottlenecks and slow response times caused by inattentive administrators and developers. I have learned much about SQL Server 2000 in the past year many of those are the best practices used by other developers and senior administrators. I hope to share that information with you now so that those just starting out can learn from what I have learned.
Best Practice #1
Download and install and actually use the SQL Server Best Practices Analyzer tool provided by Microsoft.
The Reasoning:
This tool will scan your databases for any code or implementation issues that do not conform to Microsoft Best Practices standards. This should be the starting point on any existing or currently in production database you may have. Now you can take each recommendation with a grain of salt as the tool is probably not aware of every situation a developer may face. So therefore it is always up to the developer or administrator to decide which practices to put into place.
Best Practice #2
Never start the name of any stored procedure with the SP prefix.
The reasoning:
All system stored procedures start with the SP prefix. Naming your stored procedures in this manner will cause potential clashes as service packs are released potentially with the exact same naming as your previous stored procedure. This is highly unwise.
Best Practice #3
Apply the latest service packs and security packs
The reasoning:
With so many potential threats against a database keeping your system up to date will ensure data integrity. Keeping data integrity should be the duty of anyone either developing on the database or administrating the database.
Best Practice #4
Keep your result sets that you return from your database as small as possible.
The Reasoning:
Not only does this greatly improve performance but it makes the database much more scalable and better able to handle more concurrent users.
Best Practice #5
Avoid the Insert statement when performing bulk inserts into your database.
The reasoning:
The DTS or BCP utilities are far better for inserting information in bulk into SQL Server. These utilities are far more flexible then the SQL Bulk Insert statements you may want to use.
Best Practice #6
Keep your stored procedures as small as possible.
The reasoning:
If two users are accessing the same stored procedure at the same time then two query plans will be stored in the cache. It is far better to have smaller stored procedures call other stored procedures then one very large stored procedure. This practice makes maintaining the code a bit easier as well.
Best Practice #7
Analyze all your query plans using the SQL Query Analyzer to make sure they are performing at optimum speed.
The reasoning:
Getting to know how to use the SQL Query Analyzer is one of the best things any serious developer can do to improve performance in an application. Using this tool you can see where are the bottlenecks in your code and thereby increase performance by altering indexes or even re-writing stored procedures.
Best Practice #8
Always code for multiple user scenarios
The Reasoning:
If you plan ahead and design your database with the proper data concurrency issues already solved then the future up keep of your database will be minimized. While the upfront costs associated with this might be higher the potential payback can be great as a application changes in its lifecycle.
Best Practice #9
Use User Defined Functions wisely and sparsely.
The reasoning:
It is far better to use stored procedures in your code then User Defined Functions. While using these functions may at times increase the performance of your database it is more likely that they will be converted into stored procedures in the future as the database matures.
Best Practice #10
Do not use Select * in your query design. Instead make sure you use the proper column names in the query.
The reasoning:
Using the proper column names decreases network traffic, takes less load on the database and hence can greatly improve performance.
Best Practice #11
Avoid the use of nullable columns.
The reasoning:
The use of the nullable column consumes an extra byte on each column used. Furthermore when querying data there is much more overhead with nullable columns. Try to use alternative methods when designing a database to allow for a representation of zero data in the column.
Best Practice #12
Analyze and avoid deadlocks at all costs.
The reasoning:
It is far better to access your data the same way each time you query your database. Doing otherwise will create a deadlock situation when one process takes control while another process is fighting for the same control over the objects. This can greatly tie up resources and can cause your program to crash if not taken into account. Always try to avoid a deadlock before one occurs. Use hints on your queries to make sure they are performing the way you want them to.
Best Practice #13
Create indexes on highly selective columns.
The reasoning:
It is far better to create an index on a highly selective column as this will increase the performance of your database design.
Best Practice #14
Avoid using cursors or use cursors very wisely.
The reasoning:
Cursors consume far too much database resources to be considered a viable option in most cases. There are other options available and proper design of the program will account for this. However when you need to use cursors, when there is no other alternative, then use them very wisely and make sure you research any issues with cursors before implementing them in production. Test the database under a realistic load scenario.
Best Practice #15
Always make sure your database is as normalized as possible.
The Reasoning:
This is database design 101 here folks. If you have an un-normalized database design make sure to normalize the database design to the 3rd normal form as this is considered to be the standard. The only excuse for a non normalized design is for performance reasons. However my argument with this should be that denormalizing a database schema should be considered a last resort.
Best Practice #16
Remember to SET_NOCOUNT_ON at the beginning of your SQL bataches, stored procedures, triggers, etc.
The reasoning:
Doing this will increase performance by reducing network traffic. Setting SET_NOCOUNT_ON suppresses the messages regarding how many rows are affected after executing INSERT,UPDATE, SELECT and DELETE statements.
Best Practice #17
Avoid the use of the TEXT and NTEXT datatypes in your database design.
The reasoning:
There are far too many issues associated with TEXT and NTEXT datatypes for them to be of any great use to you. Instead it is far better to use the varchar and char datatypes instead.
Best Practice #18
Do not store BLOBS in your database.
The reasoning:
A database was not designed to stored datafiles or images. Instead it is far better to store the location of these files inside the database and let the operating system handle the file I/O for you. This is far better way to store large data files in your database.
Best Practice #19
Always perform referential integrity checks and data validations using constraints such as the foreign key and check constraints.
The reasoning:
It is far better to use constraints as opposed to triggers when performing referential integrity checks. The use of triggers should only be used to perform custom data validation that can not be performed using constraints.
Best Practice #20
Make sure all your stored procedures return a value indicating their status.
The reasoning:
Make sure you standardize on the return types your stored procedures should return indicating either success or failure. Doing this will increase the maintainability of the code and make programming against it much easier. This is especially ture if everyone understands and follows the standards.
Best Practice #21
Make sure you start each clause of your SQL statement on a new line.
The reasoning:
This makes the SQL code much more readable. You should consider this especially if you are in a team environment or a visiting consultant.
Best Practice #22
It is always best to avoid the use of column numbers in the ORDER BY clause.
The Reasoning:
Using some column numbers does not increase the performance of your query by any significant amount and also this makes your code harder to read especially in large queries.
SQL Server is a very powerful tool when used properly. It can also come to a screeching halt if left to rot with no maintenance and poor planning. While the program itself is highly scalable it is still subject to performance bottlenecks and slow response times caused by inattentive administrators and developers. I have learned much about SQL Server 2000 in the past year many of those are the best practices used by other developers and senior administrators. I hope to share that information with you now so that those just starting out can learn from what I have learned.
Best Practice #1
Download and install and actually use the SQL Server Best Practices Analyzer tool provided by Microsoft.
The Reasoning:
This tool will scan your databases for any code or implementation issues that do not conform to Microsoft Best Practices standards. This should be the starting point on any existing or currently in production database you may have. Now you can take each recommendation with a grain of salt as the tool is probably not aware of every situation a developer may face. So therefore it is always up to the developer or administrator to decide which practices to put into place.
Best Practice #2
Never start the name of any stored procedure with the SP prefix.
The reasoning:
All system stored procedures start with the SP prefix. Naming your stored procedures in this manner will cause potential clashes as service packs are released potentially with the exact same naming as your previous stored procedure. This is highly unwise.
Best Practice #3
Apply the latest service packs and security packs
The reasoning:
With so many potential threats against a database keeping your system up to date will ensure data integrity. Keeping data integrity should be the duty of anyone either developing on the database or administrating the database.
Best Practice #4
Keep your result sets that you return from your database as small as possible.
The Reasoning:
Not only does this greatly improve performance but it makes the database much more scalable and better able to handle more concurrent users.
Best Practice #5
Avoid the Insert statement when performing bulk inserts into your database.
The reasoning:
The DTS or BCP utilities are far better for inserting information in bulk into SQL Server. These utilities are far more flexible then the SQL Bulk Insert statements you may want to use.
Best Practice #6
Keep your stored procedures as small as possible.
The reasoning:
If two users are accessing the same stored procedure at the same time then two query plans will be stored in the cache. It is far better to have smaller stored procedures call other stored procedures then one very large stored procedure. This practice makes maintaining the code a bit easier as well.
Best Practice #7
Analyze all your query plans using the SQL Query Analyzer to make sure they are performing at optimum speed.
The reasoning:
Getting to know how to use the SQL Query Analyzer is one of the best things any serious developer can do to improve performance in an application. Using this tool you can see where are the bottlenecks in your code and thereby increase performance by altering indexes or even re-writing stored procedures.
Best Practice #8
Always code for multiple user scenarios
The Reasoning:
If you plan ahead and design your database with the proper data concurrency issues already solved then the future up keep of your database will be minimized. While the upfront costs associated with this might be higher the potential payback can be great as a application changes in its lifecycle.
Best Practice #9
Use User Defined Functions wisely and sparsely.
The reasoning:
It is far better to use stored procedures in your code then User Defined Functions. While using these functions may at times increase the performance of your database it is more likely that they will be converted into stored procedures in the future as the database matures.
Best Practice #10
Do not use Select * in your query design. Instead make sure you use the proper column names in the query.
The reasoning:
Using the proper column names decreases network traffic, takes less load on the database and hence can greatly improve performance.
Best Practice #11
Avoid the use of nullable columns.
The reasoning:
The use of the nullable column consumes an extra byte on each column used. Furthermore when querying data there is much more overhead with nullable columns. Try to use alternative methods when designing a database to allow for a representation of zero data in the column.
Best Practice #12
Analyze and avoid deadlocks at all costs.
The reasoning:
It is far better to access your data the same way each time you query your database. Doing otherwise will create a deadlock situation when one process takes control while another process is fighting for the same control over the objects. This can greatly tie up resources and can cause your program to crash if not taken into account. Always try to avoid a deadlock before one occurs. Use hints on your queries to make sure they are performing the way you want them to.
Best Practice #13
Create indexes on highly selective columns.
The reasoning:
It is far better to create an index on a highly selective column as this will increase the performance of your database design.
Best Practice #14
Avoid using cursors or use cursors very wisely.
The reasoning:
Cursors consume far too much database resources to be considered a viable option in most cases. There are other options available and proper design of the program will account for this. However when you need to use cursors, when there is no other alternative, then use them very wisely and make sure you research any issues with cursors before implementing them in production. Test the database under a realistic load scenario.
Best Practice #15
Always make sure your database is as normalized as possible.
The Reasoning:
This is database design 101 here folks. If you have an un-normalized database design make sure to normalize the database design to the 3rd normal form as this is considered to be the standard. The only excuse for a non normalized design is for performance reasons. However my argument with this should be that denormalizing a database schema should be considered a last resort.
Best Practice #16
Remember to SET_NOCOUNT_ON at the beginning of your SQL bataches, stored procedures, triggers, etc.
The reasoning:
Doing this will increase performance by reducing network traffic. Setting SET_NOCOUNT_ON suppresses the messages regarding how many rows are affected after executing INSERT,UPDATE, SELECT and DELETE statements.
Best Practice #17
Avoid the use of the TEXT and NTEXT datatypes in your database design.
The reasoning:
There are far too many issues associated with TEXT and NTEXT datatypes for them to be of any great use to you. Instead it is far better to use the varchar and char datatypes instead.
Best Practice #18
Do not store BLOBS in your database.
The reasoning:
A database was not designed to stored datafiles or images. Instead it is far better to store the location of these files inside the database and let the operating system handle the file I/O for you. This is far better way to store large data files in your database.
Best Practice #19
Always perform referential integrity checks and data validations using constraints such as the foreign key and check constraints.
The reasoning:
It is far better to use constraints as opposed to triggers when performing referential integrity checks. The use of triggers should only be used to perform custom data validation that can not be performed using constraints.
Best Practice #20
Make sure all your stored procedures return a value indicating their status.
The reasoning:
Make sure you standardize on the return types your stored procedures should return indicating either success or failure. Doing this will increase the maintainability of the code and make programming against it much easier. This is especially ture if everyone understands and follows the standards.
Best Practice #21
Make sure you start each clause of your SQL statement on a new line.
The reasoning:
This makes the SQL code much more readable. You should consider this especially if you are in a team environment or a visiting consultant.
Best Practice #22
It is always best to avoid the use of column numbers in the ORDER BY clause.
The Reasoning:
Using some column numbers does not increase the performance of your query by any significant amount and also this makes your code harder to read especially in large queries.
Subscribe to:
Posts (Atom)