As I get called on a lot of to do SQL Server installations, I’ve come up with what I’ve found works best for me. Every location has different infrastructure, security, networks and their way of doing things. Since I’m the one doing the installation, and I know I’ll get called back in the future at some point to upgrade, troubleshoot or manage the SQL Server environment, I like to have a set of standards. Documentation, I actually do enjoy writing it (yes I may be sick), but having a self-documenting PowerShell script is even better!

I have written a PowerShell script with a few functions to help prepare the SQL Server VM or SQL Server cluster node VMs. I’ll copy this script to the management server, edit a template file that contains the variables. This template file is the only file I have to change from install to install. Microsoft does have desired state configurations (DSC) that are wonderful. The problem I run in when I try to use them is that I spend more time tweaking them to get them to work in each environment. This Blog is to highlight some quick and easy ways to configure the virtual hardware settings and drives for the installation of SQL Server.

Here is the process I use to build a SQL Server Hyper-V Virtual Machine (VM):

  1. Hopefully I’m lucky enough to get one of the customers standard builds. I just want a C:\ and a domain joined Windows Server.
  2. Prepare my template file
  3. Run a few of my PowerShell functions against using the template file
  4. Voila, we will now have a VM with the
      5. C:\ = OS
      Q:\ = Quorum
      E:\ = SQulnstanceName_DB
      F:\ = SQulnstanceName_LOG
      G:\ = SQulnstanceName_TEMPDB
      H:\ = SQulnstanceName_TEMPLOG

Template File

Below is my template file. I copy the contents to the customers local management server or a Hyper-V server that hosts the SQL Server VM and save it as SQLInstance_Application01.ps1. I then edit the variables to fit the customers environment. User accounts, passwords, IPs etc.

It has variables that I use to do the following:

  1. Create VHDX files for the SQL Data/Log/TempDB files and a possible cluster Quorum disk
  2. Attach the disks to the VM(s) that will make up the standalone or clustered SQL Server
  3. Prepare the disks on the first VM (Init, Format, Assign Drive Letter)
  4. Create a cluster (I’ll leave this for another Blog Post)
  5. Install SQL Server using the variables (I’ll also leave this for another Blog Post)
# Input Parameters
$HyperVClusterServer = "Hyperv01"
$VHDPath = "F:\vhds\"
$ClusterName = "CLSQL01"
$ClusterCap = "CASQL01"
$InstanceName = "MSSQLSERVER"
$ClusterIP = "192.168.1.50"
$nodes = @("SQL01")

$SQLSource = "\\corp\fileserver\ISO\MSSQL\SQLServer.iso"
$svcSQLUser = "CORP\svc-sqldb"
$svcSQLUserPassword = "MyP@$$w0rd"
$svcSQLAgent = "CORP\svc-sqlagent"
$svcSQLAgentPassword = "AnotherP$assw0rd"
$InstancePort = "1433"
$sqlIP = "192.168.1.51"
$clusterSubnetMask = "255.255.255.0"
$clusterNetwork = "Cluster Network 1"
$SQLAdmins = "CORP\SQL ADMINS" 
$InstanceDir	= "C:\Program Files\Microsoft SQL Server"
$userDbDir		= "E:\MSSQL.$InstanceName\Data"
$userDbLogDir	= "F:\MSSQL.$InstanceName\Log"
$tempDbDir		= "G:\MSSQL.$InstanceName\Data"
$tempDbLogDir	= "H:\MSSQL.$InstanceName\Log"


$Drives = [ordered]@{
			"$VHDPath$InstanceName`_q.vhdx" = 1GB;
			"$VHDPath$InstanceName`_e.vhdx" = 120GB;
			"$VHDPath$InstanceName`_f.vhdx" = 20GB;
			"$VHDPath$InstanceName`_g.vhdx" = 30GB;
			"$VHDPath$InstanceName`_h.vhdx" = 30GB;
		}
$DiskLabels = @{
		"Q" = "Quorum";
		"E" = "$InstanceName`_DB";
		"F" = "$InstanceName`_LOG";
		"G" = "$InstanceName`_TEMPDB";
		"H" = "$InstanceName`_TEMPLOG";
}		

#The functions below will be imported from library-PrepareSQLServer.ps1
& . C:\SQLInstall\library-PrepareSQLServer.ps1
$result1 = Create-SQLDisks -ComputerName $HyperVClusterServer
$result2 = AttachDisks -Nodes $nodes -HyperVCluster $HyperVClusterServer  
$result3 = PrepareDisks -Nodes $nodes -FirstDiskIsQurorum $true

Notice at the bottom of the template file that I include library-PrepareSQLServer.ps1. This is the workhorse that holds all the generic code to act on the variables above.

A look at library-PrepareSQLServer.ps1

The following code I copy into a file and also save this on the customer’s management server in C:\SQLInstall\library-PrepareSQLServer.ps1. This is then imported into each template file I use.

Function Create-SQLDisks {			
	# Create VHD Files
    param (
        [string]$ComputerName = "localhost"
    )

	foreach ($drive in $drives.keys) {
		write-host "Creating $($drives[$drive]) file: $drive"
		$rc = New-VHD -ComputerName $ComputerName -Path $drive -Dynamic -SizeBytes $drives[$drive] 
	}
}

Function AttachDisks {
    param (
        [Array]$nodes,
        [String]$HyperVCluster,
        [Boolean]$EnableSharedVhd = $true
    )

	# Attach the VHDX Files
	# This requires us to run it on the Hyper-V Host where the VM is running
	$clusterNodes = Get-ClusterNode -Cluster $HyperVCluster -ErrorAction SilentlyContinue
    if (($clusterNodes.Count -le 0) -or ($EnableSharedVhd -eq $false)) {
        $SupportPersistentReservations = $false
    }
	foreach ($VMName in $nodes) {
		$ControllerLocation = 1
		if ($ClusterNodes) {
			$ComputerName = (Get-VM -ComputerName $clusternodes -VMName $VMName -ErrorAction SilentlyContinue).ComputerName
			If ($ComputerName) {
				Write-Host -ForegroundColor Yellow "[$VMName] Located on Host $ComputerName"
			} else {
				Write-Host -ForegroundColor Red "Could not find $VMName on any Host"
                Return $false
			}
		} else {
			$ComputerName = $HyperVCluster
		}
		foreach ($drive in $drives.keys) {
			Write-Host "[$VMname] Connecting Disk on $ControllerLocation. Shared VHDX: $SupportPersistentReservations"
			write-host "`t$drive"
            Add-VMHardDiskDrive -ComputerName $ComputerName -VMName $VMName -ControllerType SCSI -ControllerNumber 0 -ControllerLocation $ControllerLocation -Path $drive -SupportPersistentReservations:$SupportPersistentReservations
			$ControllerLocation++
		}
		Start-VM -ComputerName $ComputerName -VMName $VMName -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
	}
    return $true
}

Function PrepareDisks {
	param (
		[Array]$nodes,
        [boolean]$FirstDiskIsQurorum = $true
	)
	# Prepare the disks and format them
    $node1 = $nodes[0]

    # Wait for Machine 1 to come online so that we can prep the disks
    if (!(Test-WSMan -ComputerName $node1 -ErrorAction SilentlyContinue)) {
        Write-Host "[$node1] Could not connect using WinRM. Please check configuration on $node1."
        return $false
    }

	Invoke-Command -ComputerName $node1 -ArgumentList @($FirstDiskIsQurorum, $DiskLabels) -ScriptBlock { 
		param (
			$FirstDiskIsQurorum,
            $DiskLabels
		)

        Write-Host "Setting disks to an online state"
        Get-Disk | where {$_.OperationalStatus -eq "Offline"} | Set-Disk -IsOffline $False
        Write-Host "Setting disks to a read/write state"
        Get-Disk | where {$_.IsReadOnly -eq $true} | Set-Disk -IsReadOnly $False
        Write-Host "Clearing disks and data"
        Get-Disk | where {$_.Number -gt 0} | Clear-Disk -RemoveData -Confirm:$false -ErrorAction SilentlyContinue
        Write-Host "Initializing disks"
        Get-Disk | where {$_.Number -gt 0} | Initialize-Disk -PartitionStyle GPT -ErrorAction SilentlyContinue

		$DiskNumber = 1
		$disks = Get-Disk | Where {$_.NumberOfPartitions -eq 1}
		$disks | % {
			if ($FirstDiskIsQurorum) {
				if ($DiskNumber -eq 1) {
					$DriveLetter = "Q"
					$AllocationUnitSize = 4096
				} else {
					$DriveLetter = ([char](67 + $_.Number)).ToString()
					$AllocationUnitSize = 65536
				}
			} else {
					$DriveLetter = ([char](68 + $_.Number)).ToString()
					$AllocationUnitSize = 65536
			}
            Write-Host "[$($DriveLetter):] Creating $($DiskLabels[$DriveLetter])"
			$volume = New-Partition -DiskNumber $_.Number -DriveLetter $DriveLetter -UseMaximumSize -ErrorAction SilentlyContinue | Format-Volume -FileSystem NTFS -NewFileSystemLabel $DiskLabels[$DriveLetter] -AllocationUnitSize $AllocationUnitSize -Confirm:$false
			
			For ($counter=1; $counter -lt $nodes.count) {
                Write-Host "`t[$($DriveLetter):] Setting drive letter on machine $($nodes[$counter])"
				Invoke-Command -ComputerName $nodes[$counter] Set-Partition -NewDriveLetter $DriveLetter -DiskNumber $DiskNumber
			}
			$DiskNumber++
		}		
	}
}

Running the script

PS C:\WINDOWS\system32> Create-SQLDisks -ComputerName $HyperVClusterServer
Creating 1073741824 file: F:\vhds\MSSQLSERVER_q.vhdx
Creating 128849018880 file: F:\vhds\MSSQLSERVER_e.vhdx
Creating 21474836480 file: F:\vhds\MSSQLSERVER_f.vhdx
Creating 32212254720 file: F:\vhds\MSSQLSERVER_g.vhdx
Creating 32212254720 file: F:\vhds\MSSQLSERVER_h.vhdx

PS C:\WINDOWS\system32> $result2 = AttachDisks -Nodes $nodes -HyperVCluster $HyperVClusterServer
[SQL01] Connecting Disk on 1. Shared VHDX: False
        F:\vhds\MSSQLSERVER_q.vhdx
[SQL01] Connecting Disk on 2. Shared VHDX: False
        F:\vhds\MSSQLSERVER_e.vhdx
[SQL01] Connecting Disk on 3. Shared VHDX: False
        F:\vhds\MSSQLSERVER_f.vhdx
[SQL01] Connecting Disk on 4. Shared VHDX: False
        F:\vhds\MSSQLSERVER_g.vhdx
[SQL01] Connecting Disk on 5. Shared VHDX: False
        F:\vhds\MSSQLSERVER_h.vhdx

PS C:\WINDOWS\system32> $result3 = PrepareDisks -Nodes $nodes -FirstDiskIsQurorum $true
Setting disks to an online state
Setting disks to a read/write state
Clearing disks and data
Initializing disks
[Q:] Creating Quorum
[E:] Creating MSSQLSERVER_DB
[F:] Creating MSSQLSERVER_LOG
[G:] Creating MSSQLSERVER_TEMPDB
[H:] Creating MSSQLSERVER_TEMPLOG