Automating Image-Based Deployment of SQL Server on Azure IaaS VMs – Combining OS Installation and SQL Server Sysprep

In the recent articles published on this forum, we have been describing the process of preparing a Windows Server 2012 R2-based image containing a pre-staged installation of SQL Server 2014. The intention was to streamline deployment of SQL Server 2014 Azure Infrastructure-as-a-Service (IaaS) virtual machines, without relying on somewhat inflexible Gallery images. So far we have stepped through individual stages of the process, starting with the deployment of the virtual machine (that will serve as the basis for our image) with several data disks attached to it, installation of the operating system, setup of Storage Spaces based volume intended to host SQL Server databases and logs, copying an ISO file containing SQL Server 2014 installation media to the target server, mounting it as an additional drive, and finally, launching the unattended setup from the command line via PowerShell Remoting (equivalent to the interactive option described in the Install SQL Server 2014 Using SysPrep MSDN Library article). Now, let’s capture all these steps in the form of a single PowerShell script that you will be able to execute remotely, by relying on the functionality provided by Azure PowerShell module.

Note that in order for the SQL Server 2014 installation to succeed, we will also need to ensure that Microsoft .NET Framework 3.5 Service Pack 1 is present. On Windows Server 2012 R2, this component is available as one of the optional operating system features, which can be deployed either via Add Roles and Features Wizard or the Install-WindowsFeature PowerShell cmdlet (with the -Name parameter set to Net-Framework-Core). The installation requires a reboot, which we will force with the Restart-Computer cmdlet.

For the sake of clarity, it is important to note a few customizations that you need to be aware of before executing the script listed below. Obvioulsy we assume that you have an existing Azure subscription in which the virtual machine will get provisioned. You will be prompted to sign into it with administrative credentials (as the result of invoking the Add-AzureAccount cmdlet). You will also have to modify the script by setting the value of the $subscriptionName variable to the name of your subscription. Similarly, you will need to come up with a unique name of an Azure storage account (assigned to the $storageAccountName variable), cloud service name (stored in in the $serviceName variable), and the preferred Azure data center location (to which $location gets set). The storage account must be enabled for the Azure Files feature, with its file.core.windows.net namespace hosting the share and a folder (which we reference by using $shareName and $directoryName respectively) containing the ISO file with the SQL Server 2014 installation media (refer to Automating Image-Based Deployment of SQL Server on Azure IaaS VMs – Preparing OS Image for a script that allows you to automate the upload process).

We also specify an arbitrary name of the virtual machine ($vmName), set its size to medium (A2), configure administrative credentials (by using $adminUser and $adminPassword), as well as designate the size and number of data disks ($diskSizeInGB and $numberOfDisks), so you might want to review their current values and modify them according to your preferences. Finally, note that for the PowerShell Remoting to succeed, you need to launch it in the elevated security context (due to its dependency on the installation of the WinRM certificate).

The resulting script takes the following form:

function Install-WinRmCertificate($serviceName, $vmName) 
# As per https://gallery.technet.microsoft.com/scriptcenter/Create-a-New-Windows-Azure-5369a432
{ 
    $vm = Get-AzureVM -ServiceName $serviceName -Name $vmName 
    $winRmCertificateThumbprint = $vm.VM.DefaultWinRMCertificateThumbprint 
     
    $winRmCertificate = Get-AzureCertificate -ServiceName $ServiceName -Thumbprint $winRmCertificateThumbprint -ThumbprintAlgorithm sha1 
     
    $installedCert = Get-Item Cert:CurrentUserMy$winRmCertificateThumbprint -ErrorAction SilentlyContinue 
     
    if ($installedCert -eq $null) 
    { 
        $certBytes = [System.Convert]::FromBase64String($winRmCertificate.Data) 
        $x509Cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate 
        $x509Cert.Import($certBytes) 
         
        $store = New-Object System.Security.Cryptography.X509Certificates.X509Store "Root", "LocalMachine" 
        $store.Open("ReadWrite") 
        $store.Add($x509Cert) 
        $store.Close() 
    } 
} 

$subscriptionName 	= 'your_subscription_name'
$currentDir 		= Convert-Path .

Add-AzureAccount
Select-AzureSubscription -SubscriptionName $subscriptionName -Current
Set-AzureSubscription -SubscriptionName $subscriptionName -CurrentStorageAccountName $storageAccountName

$storageAccountName	= 'your_storage_account'
$storageAccountKey 	= (Get-AzureStorageKey -StorageAccountName $storageAccountName).Primary
$serviceName 		= 'your_service_name'
$location 		= 'your_preferred_region'

New-AzureService -ServiceName $serviceName -Location $location

$vmName 		= 'VMSQL01'
$vmSize			= 'Medium'
$adminUser      	= 'SQL_Admin'
$adminPassword  	= '0p3n$35@m3' 

$diskSizeInGB 		= 50
$numberOfDisks 		= 4

$imageName = (Get-AzureVMImage | Where-Object {$_.label -like "Windows Server 2012 R2 Datacenter*"} |
				Sort-Object –Descending PublishedDate)[0].ImageName

$vmConfig = New-AzureVMConfig -Name $vmName -InstanceSize $vmSize -ImageName $imageName |
	Add-AzureProvisioningConfig -Windows -AdminUsername $adminUser -Password $adminPassword

for ($index = 0; $index -lt $numberOfDisks; $index++)
{ 
	$diskLabel = "Disk_" + $index
	$vmConfig = $vmConfig | Add-AzureDataDisk -CreateNew -DiskSizeInGB $diskSizeInGB `
			-DiskLabel $diskLabel -LUN $index        
}

New-AzureVM -ServiceName $serviceName -VMs $vmConfig -WaitForBoot

Get-AzureRemoteDesktopFile -ServiceName $serviceName -Name $vmName -LocalPath "$currentDir$vmName.rdp" 

Install-WinRmCertificate -serviceName $serviceName -vmName $vmName
$winRmUri = Get-AzureWinRMUri -ServiceName $ServiceName -Name $vmName
$securePassword = $adminPassword | ConvertTo-SecureString -AsPlainText -Force
$credential = New-Object -typename System.Management.Automation.PSCredential -argumentlist $adminUser,$securePassword

$storagePoolName 	= 'SQL_StoragePool1'
$virtualDiskName 	= 'SQL_VirtualDisk1'
$virtualDiskSizeInGB 	= 50GB
$localDrive 		= "F"

Invoke-Command -ConnectionUri $winRmUri.ToString() -Credential $credential -ScriptBlock {
    $ss = Get-StorageSubSystem -FriendlyName *Space*
    $pd = Get-PhysicalDisk -CanPool $true
    New-StoragePool -FriendlyName $using:storagePoolName `
		-StorageSubSystemFriendlyName $ss.FriendlyName `
		-PhysicalDisks $pd
    New-VirtualDisk -FriendlyName $using:virtualDiskName `
		-StoragePoolFriendlyName $using:storagePoolName -Size $using:virtualDiskSizeInGB `
		-ProvisioningType Thin -ResiliencySettingName Simple
    $vd = Get-VirtualDisk -FriendlyName $using:virtualDiskName
    $d = $vd | Get-Disk
    $dn = $d.Number
    Set-Disk -Number $dn -IsReadOnly 0
    Set-Disk -Number $dn -IsOffline 0
    Initialize-Disk -Number $dn -PartitionStyle MBR
    New-Partition -DiskNumber $dn -DriveLetter $using:localDrive -UseMaximumSize
    Start-Sleep -Seconds 30
    Initialize-Volume -DriveLetter $using:localDrive -FileSystem NTFS -Confirm:$false
    Install-WindowsFeature -Name Net-Framework-Core -Restart
    Restart-Computer -Force
    Start-Sleep -Seconds 300
}

$mappedDrive 		= 'Z'
$shareName 		= 'share'
$directoryName 		= 'isos'

$iso 			= 'en_sql_server_2014_enterprise_edition_x86_dvd_3932296.iso'
$targetDir 		= $localDrive + ':SourceMicrosoftSQL_Server_2014'
$targetPath 		= $targetDir + '' + $iso

Invoke-Command -ConnectionUri $winRmUri.ToString() -Credential $credential -ScriptBlock {

    $sourcePath = $using:mappedDrive + ':' + $using:directoryName + '' + $using:iso
    $targetPath = $using:targetDir + '' + $using:iso

    net use Z: \$using:storageAccountName.file.core.windows.net$using:shareName /u:$using:storageAccountName $using:storageAccountKey
    New-Item -Path $using:targetDir -ItemType Directory
    Copy-Item -Path $sourcePath -Destination $using:targetDir -Force
    $mountResult = Mount-DiskImage $targetPath -PassThru
    $driveLetter = ($mountResult | Get-Volume).DriveLetter

    $setup = $driveLetter + ":Setup.exe" 
    $parameters = @"
    /ACTION=PrepareImage /ENU=true /QUIET=true /FEATURES=SQLENGINE,Tools /UpdateEnabled=false /INSTALLSHAREDDIR="F:Program FilesMicrosoft SQL Server" /INSTALLSHAREDWOWDIR="F:Program Files (x86)Microsoft SQL Server" /INSTANCEID=MSSQLSERVER /INSTANCEDIR="F:Program FilesMicrosoft SQL Server" /IAcceptSQLServerLicenseTerms=true
"@
    $cmdLine = $setup + $parameters
    Invoke-Expression -Command:$cmdLine

}

In the next article, we will conclude this process by capturing the resulting installation into a custom gallery image and setting it up for automatic completion of SQL Server setup once an Azure IaaS virtual machine based on it is provisioned.

See all articles by Marcin Policht