How to archive data into a separate SQL database

Datawarehouse  databases become larger over time.  Problems can develop leading to SQL Server performance issues and unnecessary restricted service. The main problem revolves around archive data existing in the same database, filegroup and drive, leading to Query response time issues. A common solution is  to archive data into a separate database.

Although Sql Server table  partitioning is an effective method for maintaining  large data sets and improving query performance ,  archiving data into a separate database or filegroup can be effective , less complex and cheaper.  This depends on the circumstances  and the DBA must assess the requirements and ask the right questions.

DBA  requirements on a large and growing Datawarehouse. Such as:

 1)      Improve query response time performance of the datawarehouse. Read Datawarehouse Wait Stats

2)      Improve backup times . Read SQL Server - Predict SQL BACKUP DATABASE finish time with sys.dm_exec_requests

3)      Decrease the maintenance window

4)      Maintain copies on lower environments more efficiently

 Some  questions to ask

1) What percentage of queries are for current data (as defined by the organisation) ?  Consider what portion of queries are for the current period. If they are a high portion then archiving older data should be considered

2) What is the frequency of partitioning?  Will it just be every quarter? A scripted and repeatable process will need to be developed.

3) Where is the archived data hosted currently? Is it on the same filegroup drive as the current data?

4) What is the query path currently?  Is it  a SQL View ?  

 Archiving into a separate database  has benefits

1) Allows an archive base database to be set up in different environments – such as Development,Test and QA. Only current data needs to be restored.

2) The architecture is supported by the current SQL Server Licensing – and no new licenses are required. For example , if SQL Server Standard is used – and there is no requirement for table partitioning – supported by Enterprise Edition in – 2005 and 2008, then archiving to a separate database  can solve the problem without extra licensing costs

3) Maintenance operations  performed on subsets of data are performed more efficiently because these operations target only the data that is required, instead of the whole table.

4) Maintaining different data sets can be  maintained on different disk types. For example, archive data on Tier 2 disk , current data on Tier 1 disk. Read more on SQL Server Storage and IO performance - SQL Server DBA

5) Increased IO channels improves query response.  Typically, reports use wider queries. Increasing IO channels improves throughput therefore improved response times.

6)If the current setup uses  SQL Views , maintaining an Archived table in the current database  or maintaining the Archived table in a separate database makes no difference , as long as a procedure is set up to update views if necessary.

 Read More

Query Response Time measure - SQL Server DBA

Data compression in SQL Server 2008 - SQL Server DBA

SQL Server scaling techniques - SQL Server DBA