In Part 1 we did an overview of partitioning and related techniques such as
archiving. This time we'll dig deeper into archiving which is perhaps the
easiest technique to use regardless of the platform.
The common archiving strategy is to do so by a date column. To illustrate a
simple archiving strategy we'll use the Person.Contact table in Adventureworks.
Start by creating an archive table with the same structure as Person.Contact and
I like to add two additional columns; ArchiveID which will be an identity and
ArchivedDate which will be a datetime set to a default of GetUTCDate() when the
row is added to the table. You can use the technique of your choice for building
this table but I like the simplicity of select into because I can get a plain
table with no indexes.
select * into person.contactarchive from person.contact where 1=0
I then use the SSMS designer to add ArchiveID and DateAdded, and I make ArchiveID the primary key. This is the final result:
CREATE TABLE [Person].[contactarchive]( [ContactID] [int] NOT NULL, [NameStyle] [dbo].[NameStyle] NOT NULL, [Title] [nvarchar](8) NULL, [FirstName] [dbo].[Name] NOT NULL, [MiddleName] [dbo].[Name] NULL, [LastName] [dbo].[Name] NOT NULL, [Suffix] [nvarchar](10) NULL, [EmailAddress] [nvarchar](50) NULL, [EmailPromotion] [int] NOT NULL, [Phone] [dbo].[Phone] NULL, [PasswordHash] [varchar](128) NOT NULL, [PasswordSalt] [varchar](10) NOT NULL, [rowguid] [uniqueidentifier] NOT NULL, [ModifiedDate] [datetime] NOT NULL, [ArchiveID] [int] IDENTITY(1,1) NOT NULL, [DateAdded] [smalldatetime] NOT NULL CONSTRAINT [DF_contactarchive_DateAdded] DEFAULT (getutcdate()), CONSTRAINT [PK_contactarchive] PRIMARY KEY CLUSTERED ( [ArchiveID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
A few points worth mentioning before we continue. One is that it's worthwhile
to be consistent in your naming convention. I like adding the 'archive' postfix
so that I can tell at a glance if a table has an archive relative in the
database. I also like using archive rather than history as history is often used
for different purposes - auditing being the most common, where you use triggers
to capture some or all changes to a table. I've also
elected to lightly index, which makes sense if you won't be accessing the table
at all, but in many cases you may be hitting the archive table frequently and in
that case you should index to the same degree or more than the base table, since
the only time the indexes will be affected will be during the archive operation.
Another point is that if you follow
my example and use an identity column on the archive table you have to be sure
that all upstream code is using Scope_Identity() rather than @@Identity or bad
things will happen as the incorrect ID is returned to the caller. There
are alternatives to avoid that possibility. One is to just use a
uniqueidentifier, the other is to just continue to use ContactID as the primary
key. The latter works fine if you know you'll be archiving each record once and
only once (which is the scenario we are implementing next and is most common).
Now let's build the archiving code and for this example we'll use
ModifiedDate as our key column.
alter proc usp_PersonContactArchive @ThruDate datetime as set nocount on set xact_abort on begin transaction --first we stabilize the set of records we want to move create table #temp (ContactID int) create unique index ndxMain on #Temp (ContactID) insert into #temp ( ContactID) select ContactId from Person.Contact where ModifiedDate
Pretty vanilla code, the goal is to make sure that we don't delete records from the source table without them being in the archive table. An alternate, and perhaps slightly more robust, implementation would be to join back to the archive table to positively identify records to delete, like this:create proc usp_PersonContactArchive2 @ThruDate datetime as set nocount on set xact_abort on begin transaction --put the records in the archive table INSERT Person.contactarchive (ContactID ,NameStyle ,Title ,FirstName ,MiddleName ,LastName ,Suffix ,EmailAddress ,EmailPromotion ,Phone ,PasswordHash ,PasswordSalt ,rowguid ,ModifiedDate ) select C.ContactID ,NameStyle ,Title ,FirstName ,MiddleName ,LastName ,Suffix ,EmailAddress ,EmailPromotion ,Phone ,PasswordHash ,PasswordSalt ,rowguid ,ModifiedDate from Person.Contact C where ModifiedDate
All that remains is to test it and to set up a job to run it. The nice thing about this implementation is that if the job fails, the next run will catch all the records the previous run should have with no extra tracking needed. The job just needs a single step, though in practice I put all my archiving code into a single job with a separate step for each table. Our calling syntax is simple:declare @ThruDate datetime set @ThruDate = DateAdd(yy, -9, getutcdate()) exec usp_PersonContactArchive @ThruDate
Because we're using Adventureworks and their is no new data being added, I've
elected to archive anything more than a nice odd 9 years old each time it runs.
We can run it at whatever internal makes sense. It's less work to do it daily so
that we move smaller chunks of data, but we might have a business situation
where we want to archive once a year on Jan 1.
This is simple archiving and ignores two possibilities. One is that you'll
have foreign key constraints and the other is that the number of records to be
archived is so large that doing it in a single transaction is prohibitive.
Foreign keys can range from mild pain to severe. Let's look at a not quite so
simple situation where we our target table is part of a foreign key
relationship. I've created a table called TestFKey and populated it with data
from Person.Contact, and then added the foreign key constraint to person.contact.CREATE TABLE [dbo].[TestFkey]( [contactid] [int] IDENTITY(1,1) NOT NULL, [modifieddate] [datetime] NOT NULL, CONSTRAINT [PK_TestFkey] PRIMARY KEY CLUSTERED ( [contactid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]ALTER TABLE [dbo].[TestFkey] WITH CHECK ADD CONSTRAINT [FK_TestFkey_Contact] FOREIGN KEY([contactid]) REFERENCES [Person].[Contact] ([ContactID])
To test, I ran this statement:exec usp_PersonContactArchive '12/30/1998'
And this is the resulting error:
A check of our archive table shows that we're still transactionally
consistent. When the delete failed our transaction failed along with it. This
will happen if even one foreign key relationship is still valid within our
target set. The only way to resolve is to either only move records without a
foreign key relationship (not incredibly useful), or we have to archive our way
from the bottom up, starting with records in the TestFkey table. With a good
number of foreign keys and add in some business rules that are any more complex
than date based and it gets pretty complex. To resolve this one we would just
mirror our archiving process pointed at the TestFkey table, put that step ahead
of our Person.Contact step and let it run.
Let's revisit our other potential issue which is that we may have too many
records to process in one pass. Only experience can tell you what that number
is, but it's easy enough to implement. First we need to modify our archiving
proc a bit (changes are highlighted):alter proc usp_PersonContactArchive2 @ThruDate datetime, @ArchiveCount int output as set nocount on set xact_abort on begin transaction --put the records in the archive table INSERT Person.contactarchive (ContactID ,NameStyle ,Title ,FirstName ,MiddleName ,LastName ,Suffix ,EmailAddress ,EmailPromotion ,Phone ,PasswordHash ,PasswordSalt ,rowguid ,ModifiedDate ) select top 1 C.ContactID ,NameStyle ,Title ,FirstName ,MiddleName ,LastName ,Suffix ,EmailAddress ,EmailPromotion ,Phone ,PasswordHash ,PasswordSalt ,rowguid ,ModifiedDate from Person.Contact C where ModifiedDate order by ModifiedDate set @ArchiveCount = @@rowcount --delete from source delete from person.contact where ContactId in (select c.contactid from person.contact c inner join person.contactarchive ca on c.contactid = ca.contactid) commit transaction
Then we add a higher level calling proc that will call our original proc
multiple times:alter proc usp_PersonContactArchiveLoop as set nocount on declare @ThruDate datetime declare @RecCount int declare @MaxLoop int set @ThruDate = DateAdd(yy, -8, getutcdate()) set @maxLoop = 10 while @MaxLoop > 0 begin exec usp_PersonContactArchive2 '1/1/1999', @RecCount output print 'Archived ' + convert(varchar(10), @RecCount) set @MaxLoop = @MaxLoop - 1 if @RecCount = 0 break end
I've designed this one to loop up to 10 times and to archive one record on
each pass. In practice we might want to let it run until completion, or have it
break once a certain time is reached so that it doesn't continue to run into
Hopefully that will get you started thinking about how you might go about
archiving in your current situation. Archiving can lead to some performance
gains and it can delay having to purchase more expensive disk space if you move
the archive tables to cheaper storage. The downside is that the data is not
quite as easily available as it used to be. Next time we'll dig into partitioned