Automate Range Right Partition Management with Powershell (Part II)

,

In our environment, we have several partitioned tables that share a single partition scheme (with a range right, date partition function). Our objective is to keep X months of data in the partitioned tables (where X is determined by environment). In the last article, I covered removal of the "trailing" edge of the partition.

This script covers adding a new file group to the "leading" edge of the partition. It adds the new file group, the new files and then splits the partition function with the new date. You can specify the number of files to create in the file group (from 1 to 9). The format of the logical file name will be FACT_YYYY_M[MM]. The physical file name will be similar, prefixed with the database name.

This was my first real foray into PowerShell. Some of the concepts were difficult to get my head around, but once I had the basics, it was actually pretty straightforward.

This has only been tested on SQL 2005 (not SQL 2008).

 

The Partition Function is defined as:

/****** Object: PartitionFunction [pf_FACT_DATA_DATE] Script Date: 12/17/2009 12:23:54 ******/
CREATE PARTITION FUNCTION [pf_FACT_DATA_DATE](datetime) AS RANGE RIGHT FOR
VALUES (
N'2007-04-01T00:00:00', N'2007-05-01T00:00:00', N'2007-06-01T00:00:00', N'2007-07-01T00:00:00', N'2007-08-01T00:00:00', N'2007-09-01T00:00:00', N'2007-10-01T00:00:00', N'2007-11-01T00:00:00', N'2007-12-01T00:00:00',
N'2008-01-01T00:00:00', N'2008-02-01T00:00:00', N'2008-03-01T00:00:00', N'2008-04-01T00:00:00', N'2008-05-01T00:00:00', N'2008-06-01T00:00:00', N'2008-07-01T00:00:00', N'2008-08-01T00:00:00', N'2008-09-01T00:00:00', N'2008-10-01T00:00:00', N'2008-11-01T00:00:00', N'2008-12-01T00:00:00',
N'2009-01-01T00:00:00', N'2009-02-01T00:00:00', N'2009-03-01T00:00:00', N'2009-04-01T00:00:00', N'2009-05-01T00:00:00', N'2009-06-01T00:00:00', N'2009-07-01T00:00:00', N'2009-08-01T00:00:00', N'2009-09-01T00:00:00', N'2009-10-01T00:00:00', N'2009-11-01T00:00:00', N'2009-12-01T00:00:00')

The Partition Scheme is define as:

/****** Object: PartitionScheme [ps_FACT_DATA_DATE] Script Date: 12/17/2009 12:24:53 ******/
CREATE PARTITION SCHEME [ps_FACT_DATA_DATE] AS PARTITION [pf_FACT_DATA_DATE] TO (
[FACT_EMPTY], [FACT_2007_M04], [FACT_2007_M05], [FACT_2007_M06], [FACT_2007_M07], [FACT_2007_M08], [FACT_2007_M09], [FACT_2007_M10], [FACT_2007_M11], [FACT_2007_M12],
[FACT_2008_M01], [FACT_2008_M02], [FACT_2008_M03], [FACT_2008_M04], [FACT_2008_M05], [FACT_2008_M06], [FACT_2008_M07], [FACT_2008_M08], [FACT_2008_M09], [FACT_2008_M10], [FACT_2008_M11], [FACT_2008_M12],
[FACT_2009_M01], [FACT_2009_M02], [FACT_2009_M03], [FACT_2009_M04], [FACT_2009_M05], [FACT_2009_M06], [FACT_2009_M07], [FACT_2009_M08], [FACT_2009_M09], [FACT_2009_M10], [FACT_2009_M11], [FACT_2009_M12])

###############################################################################
# File:        SplitPartition.ps1
# Author:     Hugh Scott
# Date:        2009/12/03
# 
# Description:
#
# 
#
# Usage:
#
# .\SplitPartition.ps1 Server Database boundaryDate, partitionSchemeName, [numberOfFiles]
#
###############################################################################
param(
    [string]$serverName ,            # Server
    [string]$databaseName ,            # Database
    [datetime]$boundaryDate ,        # Year for the new file group
    [string]$partitionSchemeName ,    # Partition scheme name
    [int]$numberOfFiles = 2            # number of files to create in each file group
)
## Validate Parameters
## 1. $numberOfFiles must be > 0 and < 10
## 2. $boundaryDate should be the first of the month, and one month higher than the current highest boundary
if ($numberOfFiles -le 0 -or $numberOfFiles -ge 10)
{
    Throw("Number of Files to create must be between 1 and 9 inclusive. Number specified was: $numberOfFiles.")
}
if ($boundaryDate.Day -ne 1)
{
    Throw("Boundary date must be equal to the first of the month. $boundaryDate")
}

## Create a size template for the database files
## DataFile
[double]$fileSizeMB = [double](64.0 * 1024.0)
[string]$fileGrowthType = "KB"
[double]$fileGrowth = [double](64.0 * 1024.0)
[double]$maxSize = [double](4.0 * 1024.0 * 1024.0)

## Load the SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

## Connect to a SQL Server
$srv = new-object ("Microsoft.SQLServer.Management.SMO.Server") $serverName
$db = $srv.Databases[$databaseName]

## Collect the default path for data files; due to a known issue, if this
## default path setting has never been changed, it will be empty; therefore, as a 
## backup, if the defaultFile property is empty, get the path to master database
$pathName = $srv.Settings.DefaultFile
if ($pathName.Length -eq 0) 
{
    $pathName = $srv.Information.MasterDBPath
}

## Create String values for Month and Year of new boundaryDate
[string]$yearAdd = (Get-Date -Date $boundaryDate -format "yyyy")
[string]$monthAdd = (Get-Date -Date $boundaryDate -format "MM")

## Create filegroups, creating two to seperate the sysfiles from the datafiles
$fileGroupName = "FACT_" + $yearAdd + "_M" + $monthAdd
$fileGroup = new-object ("Microsoft.SQLServer.Management.SMO.FileGroup") ($db, $fileGroupName)

## Add filegroups to the database object
$db.FileGroups.Add($fileGroup)
$db.Alter()

## Create an array of unique file suffixes (fileIDs) up to the numberOfFiles specified
$i = 0
while ($i -lt $numberOfFiles) 
{$fileIDs += @("0$i"); $i++}

## Iterate through the array, creating new files for the file group
foreach ($fileID in $fileIDs)
{
    ## Create first additional file object
    $fileNameLogical = "FACT_" + $yearAdd + "_M" + $monthAdd + "_" + $fileID
    $fileNamePhysical = $pathName + "\" + $db.Name + "_" + $fileNameLogical + ".ndf"
    $dataFile = new-object ("Microsoft.SQLServer.Management.SMO.DataFile") ($fileGroup, $fileNameLogical)
    
    ## Set file object properties for File
    $dataFile.FileName = $fileNamePhysical
    $dataFile.Size = $fileSizeMB
    $dataFile.GrowthType = $fileGrowthType
    $dataFile.Growth = $fileGrowth
    $dataFile.MaxSize = $maxSize
    
    $dataFile.Create()
}

## Alter partition scheme to use new file group
$ps = $db.PartitionSchemes[$partitionSchemeName]
$ps.NextUsedFileGroup = $fileGroupName
$ps.Alter()

## Alter partition function; split on the new date
$pfName = $ps.PartitionFunction
$pf = $db.PartitionFunctions[$pfName]
$pf.SplitRangePartition($boundaryDate)
$pf.Alter()

Rate

Share

Share

Rate