Storing obsolete data reduces the performance of a database server. A well-designed archival strategy minimizes the performance problems that are caused by maintaining excess data.
Archiving is the process of getting rid of obsolete data from the main production databases. By archiving data, you can improve query performance, decrease the disk space usage and reduce the maintenance window. From a performance point of view, if a production database has obsolete data that is never or rarely used, query execution can be time-consuming. This is because queries also scan the obsolete data. To improve query performance, you can move the obsolete data from the production database to another server.
Some of the main reasons for archive:
• Improve query performance
• Decrease the disk space usage
• Faster backup and restore times
• Easier manageability
• Better user experience
By archiving old data, you can keep your databases smaller and more manageable. With smaller tables, your index rebuilds times and backup/restore times will be under control.When designing an archival strategy, you should first determine how much data can be archived, then choose an appropriate structure for the archival data, and finally create a plan for archiving the data.
There is no built-in command or tool for archiving databases. It’s depending on the business needs.
Method 1:
Create a separate script file that will move data from live database to Archive database and delete data from live database.
For example we have records from 2000 to 2013.We can keep data from 2005 to till date and move older data to archive database, maintain a table for archive dates and change the SP in such a way. When we pass dates to SP, it will check with the dates in Archive date table and if the date is less than the archive date then bring the data from archive database otherwise get data from live database.
Method 2:
SQL Partition
Partitioning is a physical database design technique. Partitioning refers to splitting what is logically one large table into smaller physical pieces.Main goal is to reduce the amount of data read for particular SQL operations so that overall response time is reduced.
Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.
References :
No comments:
Post a Comment