Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Archiving Data


Archiving Data

Author
Message
vishnup
vishnup
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 19
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?

vishnup@geotab.co.za
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42472 Visits: 18876
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7304 Visits: 5314
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search