April 25, 2008 at 9:00 am
When archiving data, would you keep the data in the same database but in a new table or in a different database, altogether?
We have a few tables which can contain millions of records.
After a configurable point in time, our application will move the records older than x days to another location. What we have to decide is should that be a different table in the current database or a different database.
Does anyone have any suggestions. I have been thinking about the cost of index updates, statistics maintenance, etc. The work still has to be done by SQL Server but will it occur all in one database or in two databases--depending on the chosen model.
April 25, 2008 at 9:06 am
If keeping the data in the same DB I would recommend creating another filegroup and file for that data. If possible place it on another physical disk as well. This will help with performance and then you can do filegroup backups and restores as you may not want or need to restore the archived data in an emergency.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 8, 2008 at 9:07 am
Hi
I think the best way for archiving the data is to use SQL 2005 feature i.e. partition tables to split the data across multiple filegroups.
Ali
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply