need advice from the Experts ..
I have been asked to do archieving of the data..
The data in the databases are large in amount.. die to which if some one want to retriev report from that db the performance is very poor, the data takes time to come out
I do regular indexing but still no use..
I have data for almost 5 yrs in the DB & only 3 months data is required for reporting..
I thought of going for log shipping or replication but..
the scene here is that the columns in the tabkle are not fixed.. some times their is a requirenment to add more columns in it..
Not if this is the scenario what should i do & how should i do that i only have 3 months data in production DB & rest data in some other server..
how do i go ahead & perform this activty every sunday??
what should i use & how to do that
Do not get me wrong for the comment I am about to make. But if you see yourself changing the table's schema too frequently (adding new columns) you may have serious database or normalization problems. You or your team may go back and revise the attributes you need and why. Altering table's schema is doable, but if you are saying this is a frequent request, I think you have an issue and will impact any data archiving process later on the road.
Having said that, I just did something similar recently. I created an identical table on same database. Then I altered or created an Index (depends of your case) where the date or identity column will be my Clustered Index. From there... you have two choices ...
-Move data from specific range of date to the archiving table
-Export the source table via BCP and import what you need to new one, via bcp as well
If you are lucky or blessed and you have Ent. edition, you can use SQL partitioning as well.
Once any of above is done, you can DELETE live data (old one or the one that you moved) in chunks, again, taking advantage of the Index on the date or ID column, and delete the old data from the live table.
Just be sure you have a proper backup prior deleting anything. I tested mine on a dummy database. It is easier than you think, depending of how many rows or data you are planning to move or archive.