August 19, 2009 at 6:39 am
Hi,
I am trying to achieve the same thing as you. I need to archive data from a Production database into an archive Database. The db still needs to be online. I need to purge the production database regularly so that i can keep the db clean and achieve maximum performance.
I need to also bear in mind that some of our customers use Sql Express which does not support replication, scheduled tasks etc. I tried Replication, but after cleaning up the production database, it filter the deletes to the archive database. I considered partitioning but you need Sql Enterprise for that.
I am moving towards a parameterized stored procedure. I need to get the rollback and the necessary checks in place to avoid duplication and i need to ensure the data integrity.
How do you go about archiving your data and would you have any other ideas?
August 19, 2009 at 8:08 am
If you have good primary keys, that should prevent duplication. Make sure the archive database has the same PKs.
The process I'd use it to move a slice of the data from a table to a staging table, or an archive table with a date/time marker. Then join back to the original table to delete those rows. Wrap that in a transaction and do a slice of data at a time. Maybe 1000 rows, maybe 10,000, depends on hardware and load.
Set this up for all tables you need to archive, use dates somehow to determine what data to move, schedule this as a job.
August 19, 2009 at 8:44 am
Is the archive on the same server? If so, Steve has the right approach. If not it gets more complex.
One note: if the schema changes you have to remember to change the process and the schema in the archive, this often gets forgotten, right up until the moment that the process starts failing..
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply