Automated Patching of SQL Server IaaS VMs

In a recent article published on this forum, we have described an automated method of backing up user databases hosted on SQL Server instances running in Infrastructure-as-a-Service (IaaS) Azure virtual machines. Now it is time to turn our attention to the equivalent approach to managing Windows updates in the same scenario. Just as before, the functionality we will present here is facilitated by an Azure VM extension SQL Server IaaS Agent, which is a lightweight software component running on top of the VM Agent, installed by default on any image-based virtual machine deployed in Azure. Extensions implement additional Azure virtual machine-related capabilities beyond those inherent to the hosting platform. While they are exclusive to Azure IaaS VMs and, effectively, their benefits are not applicable to Azure SQL Database, this is not truly relevant in the context of our presentation, since the latter (as a Platform-as-a-Service-based solution) is maintained by Microsoft without your involvement.

Just as with automated backup, the most straightforward way of managing automated patching involves the Azure Preview Portal. Desired settings can be applied either during initial deployment of SQL Server image-based virtual machines or once they have been provisioned. The relevant functionality is exposed as the Automated patching blade (accessible via SQL AUTOMATED PATCHING entry in the Optional config blade, which (when deploying a new VM) appears after clicking on the OPTIONAL CONFIGURATION entry in the Create VM blade). From there, you can enable or disable automated patching and specify corresponding settings, including the maintenance schedule, the start of the maintenance window (expressed as a full hour), as well as its duration (in minutes).

Note that even if you decide not to enable automated patching during VM deployment, the newly provisioned virtual machine will still include the SQL Server IaaS Agent VM extension (as long as it is based on a SQL Server gallery image), which you can easily verify by viewing the content of its Extensions blade. This allows you to turn on patching once the SQL Server virtual machine is already online, although its configuration is accessible in this case from the SQL Auto patching tile in the Configuration section of the VM blade. Clicking on the tile will display the same SQL Automated patching blade available during the provisioning process, exposing the same set of options that we described above.

An alternative method of accomplishing the same objective relies on Windows PowerShell (by leveraging the Azure PowerShell module). The relevant functionality is implemented by the New-AzureVMSqlServerAutoPatchingConfig cmdlet. Assuming that our intention is to allow for patching to take place weekly on Sundays, starting at 3 AM and last for 2 hours, then the code that configures automated patching would take the following format:

$vmName 		= 'VMSQL1'
$serviceName 		= 'sqlcloudservice1'
$dayOfWeek		= 'Sunday'
$startHour		= '3'
$durationMinutes	= '120'

$autoPatchingConfig 	= New-AzureVMSqlServerAutoPatchingConfig -Enable -DayOfWeek $dayOfWeek `
			-MaintenanceWindowStartingHour $startHour -MaintenanceWindowDuration -$durationMinutes `
			-PatchCategory 'Important'

Get-AzureVM -ServiceName $serviceName -Name $vmName | 
	Set-AzureVMSqlServerExtension -AutoPatchingSettings $autoPatchingConfig | 
	Update-AzureVM 

Interestingly, at this point, the only acceptable value of the -PatchCategory parameter of the New-AzureVMSqlServerAutoPatchingConfig cmdlet is Important, which identifies the category of patches that qualify for automated deployment. Similarly, the duration of the maintenance window is used to evaluate which patches can be installed based on estimated duration of their setup (since the installation must complete within the allocated time).

Windows PowerShell will also come in handy if you have a virtual machine without VM Agent installed, which might have happened if for some reason you decided to exclude it during deployment. Since the presence of the agent is the prerequisite for SQL Server IaaS Agent extension, you will need to remediate this issue first (as long as you want to use PowerShell or Preview Portal to manage automated patching). Start by downloading the agent executable from the Microsoft Downloads site and installing it on the target virtual machine. Once this is completed, run the following script (assuming that the name of the VM and its cloud service are VMSQL2 and sqlcloudservice2 respectively) and, afterwards, execute the first script we provided (after adjusting values of relevant variables) in order to install the SQL Server IaaS Agent extension and configure automated patching parameters:

$vmName 		= 'VMSQL2'
$serviceName 		= 'sqlcloudservice2'
$vm 			= Get-AzureVM –ServiceName $serviceName –Name $vmName
$vm.VM.ProvisionGuestAgent = $true
Update-AzureVM –Name $vmName –VM $vm.VM –ServiceName $serviceName

Regardless of the approach you take, you should note that automated patching is limited to Azure virtual machines running Windows Server 2012 or Windows Server 2012 R2 operating system and hosting instances of SQL Server 2012 and SQL Server 2014.

While convenient, this functionality might not satisfy update compliance requirements of more demanding environments. In such cases, it is possible to take advantage of enterprise-level solutions, such as, for example, System Center 2012 Configuration Manager, which starting with the Service Pack 1 is supported for management of Azure IaaS virtual machines. This can be an extension of an existing on-premises Configuration Manager infrastructure (leveraging cross-premises connectivity provided by Azure Site-to-Site VPN or ExpressRoute) or an all-in-one (i.e. hosting all required site system roles as well as Microsoft SQL Server without the use of any remote site systems) Primary site running in an Azure-resident virtual machine. For more information regarding this approach, refer to System Center Configuration Manager Team Blog. Another possibility is to deploy an Azure virtual machine running Windows Server Update Services to centralize patch management, as described in the Security Best Practices For Windows Azure Solutions white paper available from the Microsoft Downloads site.

This concludes our overview of automated management features based on the SQL Server IaaS Agent Azure VM extension. In the upcoming articles, we will be covering other ways to optimize administration and maintenance of Azure-based SQL Server deployments.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles