July 15, 2008 at 7:59 am
Hello,
I hope somebody could tell me what they think it the best way to archive older data out of a production database (SQL2005) and into another DB to alleviate the load on the original database. Is there a standard way of carrying this out or does everyone have there own way of doing it? The archive database would have to be kept readily available to the application. Would it go into a separate file group to keep performance decent? I have a feeling this is a bigger question than I first thought.
Thank you for any feedback.
Kind Regards,
D.
July 15, 2008 at 8:31 am
No standard way of doing this. If you have Enterprise edition, I'd look at table partitioning and moving those older partitions to separate file groups.
You can make a view instead of your table, join to other databases. You can also most to a separate filegroup and just manually move data around.
July 15, 2008 at 8:31 am
The way to do this depends on the purpose of doing this. If the idea is to reduce drive space use on the main server, you handle it differently than if the goal is to speed up access to overfull tables, and you handle that differently than if the idea is to lock data that's past a certain date/status by having it in a table that nobody has update rights on.
If you're trying to speed up access to the tables by reducing rows, which is a common goal of archiving, you might be better off partitioning the table instead of creating a home-grown archiving system. That continues to give you access to the data, but can speed up access to the current data. Take a look at table partitioning in Books Online, and see if that's what you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply