Changing a SQL Server Partition Date Boundary & Minimizing Data Movement

Question

I have a table (say, AUDIT) with data going back 10 years. Data older than 1 year is queried rarely, and full backups are starting to take too long. So, I decided to employ table partitioning and partial backups, so if (when!) I need to restore the database, I can restore the oft-queried data first, then get the old data restored later.

I partitioned the AUDIT table on it's datetime column (AUDIT_DT), segmenting the most recent 12 months from the older data. The PRIMARY partition holds the most recent 12 months, and the OLD_AUDIT_ARCHIVE (read-only) partition holds all data older than that.

I have gotten this far.

So, a month from now, I want to repartition the data similarly, while minimizing data movement. I think I need to create a staging table, and switch a month of data into it, but how do I switch the staging table data into the OLD_AUDIT_ARCHIVE partition? My goal is to move the boundary date between PRIMARY and OLD_AUDIT_ARCHIVE one month forward (say, from Feb 1, 2013 to Mar 1, 2013), while minimizing data movement.

Jason Whitish Jason Whitish · Accepted Answer · 2014-03-12T21:46:59

The way I've done this is:

Set up a new parameter function and scheme that reflects the change
Set up the "new" table using the new function/scheme
Partition Switch the old table's first partition to the new table's first partition
Insert the one month into the new table from the old
Delete the one month from the old
Partition switch the second partition from the old to the new table's.
Check that the data moved properly
Rename tables
Remove or archive old table

The biggest issue is whether or not you can even partition switch as described in the link above; there are a lot of things that have to be true in order for that to work. And even then, I'd make a point of testing it a good deal; worst issue I've had is getting a range subset error because I defined the new partition incorrectly.

EDIT: In point of fact, I forgot some steps. When I do this, I recreate the clustered index on the old table WITH (DROP_EXISTING = ON) and the new partition scheme AFTER step 5 and before step 6.

Code Example:

create partition function dateRange1 (date)
    as range left for values ('2014-02-01')

create partition function dateRange2 (date)
    as range left for values ('2014-03-01')

create partition scheme testScheme1 as partition daterange1 
    ALL TO ([PRIMARY])

create partition scheme testScheme2 as partition daterange2 
    ALL TO ([PRIMARY])

create table dbo.dates (dt date);
create clustered index CX_date ON dbo.dates (dt) ON testScheme1 (dt)
insert into dates
select dateadd(day, n, '2013-12-31')
from NUMS
where n  < 366

create table dbo.dates_new (dt date);
create clustered index CX_date2 ON dbo.dates_new (dt) ON testScheme2 (dt)

alter table dbo.dates switch partition 1 to dbo.dates_new partition 1

insert into dates_new
select dt from dates where dt <= '2014-03-01'

delete from dates where dt <= '2014-03-01'

create clustered index CX_date ON dbo.dates (dt) WITH (DROP_EXISTING = ON) ON testScheme2 (dt)
alter table dbo.dates switch partition 2 to dbo.dates_new partition 2

select * from dbo.dates
select * from dates_new

exec sp_rename 'dbo.dates', 'dates_old';
exec sp_rename 'dbo.dates_new', 'dates';

select * from dbo.dates
select * from dates_old

drop table dates_old

Changing a SQL Server Partition Date Boundary & Minimizing Data Movement

1 Answers