CODEWITHSUNDEEP
asp.netsql-serverazureazure-sql-databaserdbms
CSharped
CSharped

Reputation: 1287

SQL Server Data Archiving

I have a SQL Azure database on which I need to perform some data archiving operation.

Plan is to move all the irrelevant data from the actual tables into Archive_* tables.

I have tables which have up to 8-9 million records.

One option is to write a stored procedure and insert data in to the new Archive_* tables and also delete from the actual tables.

But this operation is really time consuming and running for more than 3 hrs.

I am in a situation where I can't have more than an hour's downtime.

How can I make this archiving faster?

Upvotes: 2

Views: 808

Answers (2)

Andrew
Andrew

Reputation: 61

You can't make it faster, but you can make it seamless. The first option is to have a separate task that moves data in portions from the source to the archive tables. In order to prevent table lock escalations and overall performance degradation I would suggest you to limit the size of a single transaction. E.g. start transaction, insert N records into the archive table, delete these records from the source table, commit transaction. Continue for a few days until all the necessary data is transferred. The advantage of that way is that if there is some kind of a failure, you may restart the archival process and it will continue from the point of the failure.

The second option that does not exclude the first one really depends on how critical the performance of the source tables for you and how many updates are happening with them. It if is not a problem you can write triggers that actually pour every inserted/updated record into an archive table. Then, when you want a cleanup all you need to do is to delete the obsolete records from the source tables, their copies will already be in the archive tables.

In the both cases you will not need to have any downtime.

Upvotes: 1

Alberto Morillo
Alberto Morillo

Reputation: 15698

You can use Azure Automation to schedule execution of a stored procedure every day at the same time, during maintenance window, where this stored procedure will archive the oldest one week or one month of data only, each time it runs. The store procedure should archive data older than X number of weeks/months/years only. Please read this article to create the runbook. In a few days you will have all the old data archived and the Runbook will continue to do the job from now and on.

Upvotes: 1

Related Questions