New Partition Management Tool Available on Codeplex

As anyone working with partitioned tables knows by now, the process of creating stand-alone ‘staging’ tables to support switching data into or out of a partition can be a management challenge, especially if the design of the partition table or its indices change over time. A staging table for new data to be switched-in must include check constraints that match the partition boundaries of the target, and include all columns, constraints and indexes appearing in the partition table. Often, the code to create the staging tables resides in a set of manually created management scripts that must remain carefully synchronized to any changes made in the partition table design. When columns are added, column characteristics change (such as nullability or length) or index definitions are tweaked, the management script creating the staging tables must also be updated. If management scripts fall out of synch, often we discover it only while in the middle of a critical month-end partition switch process, raising the risk of unplanned extension or delay of the maintenance window.

I’ve developed a simple, shared source Partition Management Tool for SQL Server 2005 to make the process of creating the staging tables for partition management nearly foolproof. At runtime, the definition of all columns, indexes, and constraints for the partition table are read using the SMO object model, and a perfect clone table matching a specific partition is created. 

The tool also provides an option for creating a staging table without indexes, and then for building indexes later after the table is populated – for applications that prefer to bulk load into a new partition without indexes present. It also includes a quick, transparent method to very quickly and simply clear the contents of a single partition within a partitioned table – a task that you may want to perform when replacing the complete contents of one partition.

Also rather than requiring you to have knowledge of the specific partition number that you are referencing, you may indicate the appropriate partition by passing a value of the partitioning column that falls within the desired partition range when invoking the tool. 

The Partition Management Tool runs from the command line, so unfortunately it cannot be used from within SQL Server stored procedures or batches. That’s because SMO-based CLR routines cannot run in-proc in SQL Server 2005. But the tool can be invoked from within SSIS (as an Execute Process task), so that runtime-dependent package variables can be integrated and the script can become part of an automated sliding window scenario orchestrated by SSIS. When invoking from SSIS, the command line arguments of the tool can be populated by expressions within SSIS.

You can download the tool and associated C# source code from SQL Server 2005 Partition Management Tool . 

Stuart Ozer