Automate Range Right Partition Management with Powershell (Part II)

###############################################################################
# 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()