Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted September 8, 2015

Automated Patching of SQL Server IaaS VMs

By Marcin Policht

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 | 

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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM