Introduction
This series of articles is designed to demonstrate in a practical manner how to implement partitioning in SQL Server. In particular the focus is on designing and maintaining a "sliding window" for the purpose of purging old data from the database with minimal performance overhead.
Fundamentally, there are two administrative tasks to maintaining a sliding window for data partitioning:
- You have to create new partitions to store new data; and
- You have to drop data from the trailing partition.
Each process poses its own unique challenges. In this article, I will review the PowerShell script SplitPartitiong.ps1, which deals with adding a new partition to the upper end (or "leading edge") of the partition scheme.
At first, this seems pretty straightforward: you simply need to alter the existing partition scheme to specify the next file group to use and then alter the partition function to specify the new boundary value (splitting the range). For a comparison, see the notes in SQL Books online for ALTER PARTITION SCHEME and ALTER PARTITION FUNCTION.
The challenge for me came in some of the finer details: making sure that the boundary value was valid (ie, that a boundary did not already exist for that value), specifying the file name and the file location, and adding an option to permit the administrator to specify that more than one file should be created for the file group. At the time, I was operating under the theory that having multiple files in the new partition would be good for performance. In practice, it did not seem to make much practical difference in performance and it sure made the environment "messy" (50 partitions times 2 files per partition, quickly added up to "overkill").
Of note is a feature within the script to determine the default location for user database files. In SQL 2005 there is a "feature" which results in a null value being returned when querying the $srv.Settings.DefaultFile property. If you don't change this value (in the database tab of the Server Properties page), then the value that is present on that screen does not get picked up by this function. You have to change it to a valid value (and then probably change it back to what was originally specified). This is an issue that is resolved in SQL Server 2008.
Using SplitPartition.ps1
With the database set up (with the script from the previous article) and the SplitPartition.ps1 script loaded, now we run a couple of statements. You are ready to start PowerShell. Note that unless you have the ability to sign code, you will need to set PowerShell's execution policy to Unrestricted in order to be able to run this script. To do so, enter Set-ExecutionPolicy Unrestricted.
To add a new, empty partition for the next month (by default, the script will choose the first day of the next month as the new boundary value), enter:
PS C:\> ./SplitPartition.ps1 <server_name> PartitionMaintTest ps_FACT_DATA_DATE
Note: If you are running SQL Server 2005, you may also need to specify a folder where user data files can be created.
Note: If you try running this same script more than once (in the same month), you will get an error. Remember, boundary values must be unique!
If you encounter an error, read through the PowerShell output carefully (I'm an amateur at error handling). Correct whatever the underlying issue is. It will probably be better to drop and re-create the whole database, unless you are familiar with the various moving parts that make up partitioning.
To add a new, empty partition for a specific month, enter:
PS C:\> ./SplitPartition.ps1 <server_name> PartitionMaintTest ps_FACT_DATA_DATE "01/01/2011"
Note: The date must be entered as shown with quotes.
Note: You can enter this script multiple times, so long as each boundary value is unique.
Verifying Results
You can view the results of your actions by running the following select statement:
SELECT * FROM partition_info ORDER BY TableName, partitionNumber
Note that at this point, you will only be adding new (empty) partitions. In order to get data into those partitions, you will have to modify the sample insert statements provided.
Conclusion
To recap, the SplitPartition.ps1 script is used to add new (empty) partitions to your partition scheme.
One feature that I would still like to implement is to create a default properties XML file which would contain the file defaults. Embedding these "hard" values inside the script sort of limits the value of the script in an enterprise setting.
The Series
You can read all the articles in this series:
- Automate Sliding Window Partition Management: Part I - Learn what a partition is and why this technique can be useful.
- Automate Sliding Window Partition Maintenance: Part II - Learn how to use Powershell to add a new parition to the upper end of the partitioned table.
