Muller Jannie

About myself and everyone else.

SQL Archive Framework

I’ve been doing a little work recently todo with SQL archiving.

As with any tools you might build as a developer for internal use you have to follow a few guidelines. Well you don’t have to but generally a tool is only as good when someone other than yourself is using it!

So when you design tools keep in mind.

  • It needs to be easy to use.
  • Should be modular so other people can plug into it.
  • Well documented (or should have plenty of time to explain to people without making them feel dumb)
  • It should be simple. Don’t make tools too complicated, it’s not a showdown.

My tool… the one for archiving.

I’ve created a narrow table with some basic info.

if object_id(‘archive_configuration’) is not null drop table archive_configuration
create table archive_configuration(
config_id int identity(1,1),
query varchar(max),
batch_total int,
batch_size int,
batch_delay varchar(16),
description varchar(max),
enabled bit default 0,
last_run_date datetime default getdate(),
last_run_status varchar(1024))


As you can see from this it’s pretty basic, there are a few things to point out. The query bit is you delete statement which you should make dynamic sql. So just replace ‘ with ” . The batch total is how many rows in total you want to delete before stopping. The size is how many rows to delete at t a time. they delay is used with the waitfor statement.

The batch size and delay should be configured based on your systems contention.

Since I’ve written a report over this table I can see the tech stuff but also publish a description on the rules of why I do the delete in a specific way. This might be something like ‘deleting order notes for orders older than 1 month’

Note: I’m saying deleting but this is because it’s deleted on the source, the term move would probably be more accurate since I’m moving the data from database A which should be nice and streamlined to database B which can be slow and on cheaper hardware. I’m using the delete and output clause to achieve this.

I then have some logging info which again comes in handy in the report since I can see when things are not as expected.

Now that my config table is setup I can stick some values in it.

Insert into archive_configuration(query,batch_total,batch_size,batch_delay,[description],[enabled])
select ‘Delete from a
output deleted.*,getdate() into archive..table1
where date_sent is not null’,
‘this is a test example for archive, it does a straight delete based on an isnull condition’,

I’ve inserted some presets.

This will be read out of the table based in a config id which is where the identity column comes in. The process archive proc then wraps all the presets and variables and executes in a transaction in a while loop.

I’ve also added a noddy error handling proc in order to get some info when it fails.

Since the query is well… free text and you can really write some nice logic in here with joins for archiving relational data etc. I think it’s pretty flexible. You can also handle all the errors and keep track of run statuses and a little bit of the business rules.

Happy days.

The code can be found below

Archiving Framework


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


This entry was posted on July 26, 2012 by in SQL Server and tagged .
%d bloggers like this: