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.