Using PowerCLI to Prepare a VM for SQL Server

/ seanpmassey

SQL Server is one of those applications where performance can be greatly impacted by the initial server configuration.  One of the big contributing factors to this is storage configuration.  If this isn’t optimized at the VM or the storage array level, performance will suffer, and an entire book has been dedicated to the subject.

SQL Server may seem ubiquitous in many environments because many applications require a SQL database for storing data.  And in many cases, a new application means deploying a new database server.

Because SQL can require a virtual machine setup that follows a different baseline, each new SQL Server will either need to be deployed from a custom template for SQL or hand-crafted from whatever base template it was deployed from.  If you want to keep your template count to a minimum but still avoid having to hand craft your SQL Servers, we need to turn to PowerCLI and PowerShell 4.0.

Windows Server 2012 R2 and PowerShell 4.0 introduced a number of new Cmdlets that will assist in preparing a brand new VM to run SQL Server.  These cmdlets handle storage and disk operations, and these new cmdlets will be instrumental in provisioning the additional storage resources that the server needs.

The code for this script is up on Github.

Standard SQL Server Configuration

When you virtualize SQL Server, there are a few best practices that should be done to ensure good performance.  Therefore, we want to ensure that the script to prepare the server for SQL implements these best practices.  Most of these best practices relate to storage configuration and disk layout.

One of the other goals of this process is to ensure consistency.  All SQL Servers should be configured similarly, and drive letters, use of mount points, and installation paths should be the same on all SQL Servers to ease administrative overhead. 

I have a couple of preferences when deploying SQL in my environment.  Each instance will have two dedicated volumes – one for SQL data files and one for SQL logs.  I prefer to use mount points to store the data and log files for my databases and TempDB.  This allows me to keep drive letter assignments consistent across all database servers, and if I need to add an instance to a server, I don’t need to find free drive letters for the additional disks. 

I also like to include the VMDK file name in the volume label.  Drive numbers can change on VMs as drives are added and removed, so adding the VMDK file name to the volume label adds an additional value to check if you need to expand a disk or remove one from production. 

Screenshot of Disk Labels

image

Finally, I like to install SQL Server Management Studio prior to installing the database engine.  This gives me one less feature to worry about when configuring my instance deployments.

There are a couple of things that this job will do when preparing a server to run SQL:

  1. Set CPU and Memory reservations based on the currently assigned resources to guarantee performance
  2. Change the Storage Policy to automatically set all newly attached disks to Online so they can be configured.
  3. Create the following disk layout:
    1. E: – SQL Install location
    2. R: – SQL Data Volume
    3. S: – SQL Backup Volume
    4. T: – SQL Log Volume
  4. Copy SQL Installer files to E:\SQLInstall
  5. Create the following volumes as mount points, and attach them to PVSCSI storage controllers. 
    1. TEMPDB Database File Volume under R:
    2. TEMPDB Log File Volume under T:
  6. Add any SQL admin groups or database owners to the local administrator group
  7. Install SQL Server Management Studio

The script doesn’t add or configure any disks that will be used for the actual SQL Server instances that will be installed on the server.  I have another script that handles that.

Working with Disks

In the past, the only ways to manage disks using PowerShell were using the old command line utilities like fdisk or to use WMI.  That changed with Windows Server 2012 R2, and new disk management commands were included with PowerShell 4.0.

Note: These commands only work against Windows Server 2012 R2 and newer.

These commands will take care of all of the disk provisioning tasks once the VMDK has been added to the server, including initializing the disk, creating the partition, and formatting it with the correct block size.  The PowerShell commands also allow us to define whether the disk will be a mount point or be accessed through a drive letter.

Note: When attaching a disk as a mount point, there are some additional options that need to be selected to ensure that it does not get a drive letter assigned after a reboot.  Please see the code snippet below.

One of the neat things about these new cmdlets is that they use the new CIMSession connection type for PowerShell remoting.  Cmdlets that use CIMSessions are run on the local computer and connect to a WMI instance on the remote machine.  Unlike PSSessions, network connections are only utilized when a command is being executed.

An example of these cmdlets in action is the function to mount a VMDK as a mount point.  

Function Create-MountPoint
{
    #Initialize volume, create partition, mount as NTFS Mount Point, and format as NTFS Volume
    Param($Servername,$VolumeName,$VolumeSize,$Path,$CimSession)
        $VolumeSizeGB = [string]$VolumeSize + "GB"
    
    $partition = Get-Disk -CIMSession $CimSession | Where-Object {($_.partitionstyle -eq "raw") -and ($_.size -eq $VolumeSizeGB)} | Initialize-Disk -PartitionStyle GPT -PassThru |  New-Partition -UseMaximumSize -AssignDriveLetter:$False 
    
    $disknumber = $partition.DiskNumber
    $partitionnumber = $partition.partitionnumber
    $diskID = $partition.diskID

    Get-Partition -DiskNumber $disknumber -PartitionNumber $partitionnumber -CimSession $CimSession | Format-Volume -AllocationUnitSize 64KB -FileSystem NTFS -NewFileSystemLabel $VolumeName -Confirm:$false 
    Add-PartitionAccessPath -CimSession $CimSession -DiskNumber $disknumber -PartitionNumber $partitionnumber -AssignDriveLetter:$False
    Add-PartitionAccessPath -CimSession $CimSession -DiskNumber $disknumber -PartitionNumber $partitionnumber -AccessPath $Path 
    Set-Partition -CimSession $CimSession -DiskNumber $disknumber -PartitionNumber $partitionnumber -NoDefaultDriveLetter:$true
}

This function handles configuring a new disk for SQL, including formatting it with a 64KB block size, and attaches it as an NTFS mount point.

If you read through the code, you’ll notice that the disk is configured to not assign a drive letter in multiple places.  While writing and testing this function, all mount points would gain a drive letter when the system was rebooted.  In order to prevent this from happening, the script needed to tell Windows not to assign a drive letter multiple times.

What About Disks for SQL Instances

One thing that this particular script does not do is create the data and log volumes for a SQL instance.  While it wouldn’t be too hard to add that code in and prompt for an instance name, I decided to place that logic in another script.  This allows me to manage and use one script for adding instance disks instead of having that logic in two places.  This also helps keep both scripts smaller and more manageable.

Installing SQL Server

The last step in this process is to install SQL Server.  Unfortunately, that step still needs to be done by hand at this point.  The reason is that the SQL installation requires Kerberos in order work properly, and it throws an error if I try to install using WinRM.