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 April 6, 2015

Automating Image-Based Deployment of SQL Server on Azure IaaS VMs - Preparing OS Image

By Marcin Policht

As we explained in our previous article, there are several different approaches to automating deployment of the SQL Server Infrastructure-as-a-Service (IaaS) virtual machines in Microsoft Azure. To briefly summarize, your options include:

  • using one of the natively available Azure Gallery images that contains a SQL Server installation, ranging from SQL Server 2008 R2 SP2 to SQL Server 2014 RTM (including SQL Server 2014 RTM Standard, SQL Server 2014 RTM Enterprise, SQL Server 2014 Enterprise Optimized for Transactional Workloads, and SQL Server 2014 Enterprise Optimized for DataWarehousing Workloads),
  • using one of the natively available Azure Gallery images that contains a Windows Server installation and provisioning a SQL Server instance after operating system deployment is completed,
  • uploading a custom operating system image to Azure storage, using it to deploy a new virtual machine, and provisioning a SQL Server instance after the operating system deployment is completed,
  • uploading or creating a custom operating system image, including a pre-staged SQL Server installation.

In this article, we will examine the last of these methods, which is well suited for scenarios where you want to deploy a custom-configured SQL Server instance to multiple virtual machines with minimal effort and maximum consistency. As we pointed out, this involves installing Windows Server in a virtual machine (on-premises or in Azure), copying the content of SQL Server installation media to its local drive, running SQL Server setup to prepare an image-based installation, configuring the operating system to automatically complete the SQL Server setup during a subsequent restart, and executing the sysprep command line utility with the generalize and shutdown switches (which prepares the operating system for imaging and shuts it down). Once this is completed, you would capture the image and transfer it to Azure blob storage to be used as your personal template. Subsequently, when deploying a new Azure IaaS virtual machine, you would use your custom image (rather than a generic Gallery image), which during its initialization would complete the SQL Server setup process in the manner that matches your preferences.

Our objective is to create a custom, Windows Server 2012 R2-based image containing SQL Server 2014-based installation, consisting of the Database Engine and its related features (but excluding Analysis Services, Integration Services, Reporting Services, Replication, Full-Text and Semantic Extractions for Search, Data Quality Services, and Master Data Services). We will start by scripting the first part of this process, focusing on automating the steps leading to the SQL Server setup and resulting in the virtual machine that will serve as the basis for our image. To automate configuration of the Azure virtual machine, we will leverage PowerShell Remoting with certificate based authentiation (for the latter, we will utilize the Install-WinRmCertificate function, which code you can find at TechNet Script Repository). We will also follow Performance Best Practices for SQL Server in Azure Virtual Machines and Performance Guidance for SQL Server in Windows Azure Virtual Machines.docx (available from the Microsoft Azure library). Note, however, that some of the initial provisioning considerations (such as, selecting the optimal virtual machine size or ensuring that storage account used to host virtual disks is not geo-replicated) are not relevant in the context of our presentation, but instead need to be taken into account when deploying virtual machines that will be based on our image.

To create the image, we will first deploy a new virtual machine hosting a Windows Server 2012 R2 installation. We will use for this purpose the most recent operating system image and include four 50 GB data disk, which will be used to construct Storage Spaces-based, thin-provisioned simple (striped) volume intended for SQL Server binaries, system databases, and logs. Note that this automatically imposes the lower limit on the tier of virtual machine you can provision (A2 is required to support up to 4 data disks), however, this should not be viewed as a meaningful constraint since at minimum you should implement Standard Tier A2 for your SQL Server Standard Edition workloads (and at least Standard Tier A3 in the case of SQL Server Enterprise Edition). In addition, keep in mind that our example is somewhat simplified and serves primarily to demonstrate the general concept of image-based SQL Server deployment. Typically, in order to maximize disk I/O, you would want to implement multi-disk, multi-column storage space layouts, with the column count matching the number of disks (we will examine these considerations in future articles published on this forum, but in the meantime, for guidance regarding database and log storage recommendations, refer to Performance Guidance for SQL Server in Windows Azure Virtual Machines.docx).

To start, we will need to install Azure PowerShell module on our administrative computer and set up an Azure subscription that will host services used throughout our presentation. Next, we will enable the Azure Files preview feature within our subscription, which we will leverage to temporarily store an image containing SQL Server installation media that will be copied to our custom image (in particular, as part of the initial setup, we will upload an ISO file containing the SQL Server 2014 installation media to the Azure File Storage, which we will afterwards download to one of the local disks on our virtual machine in preparation for the SQL Server setup). After the Azure Files preview feature has been successfully enabled, we also have to create a storage account within our subscription that will serve as a logical container of the ISO and VHD files containing, respectively, the media and disks of the virtual machine we will capture to create the custom image. To accomplish this, we need to provide a unique name that will identify our storage account in the namespace. While this task can be performed programmatically, you can take a simpler approach by picking a random name and verifying its uniqueness by running the Test-AzureName cmdlet, followed by the -Storage parameter with its value set to the name of your choice. You should ensure that the cmdlet returns $false, indicating that the name is not being used.

For the sake of simplicity, we will assume that our subscription is called My_Subscription, the storage account name we chose is mystorageaccount01, and the SQL Server 2014 installation media resides in the D:\Source folder on our administrative computer from which we will be running PowerShell scripts. Effectively, following code will allow us to implement the first part of our setup (note that Add-Account will prompt you to provide administrative credentials for the target Azure subscription).


$subscription 	= 'My_Subscription'
$storageAccount = 'mystorageaccount01'
$location 	= 'East US'
$iso 		= 'en_sql_server_2014_enterprise_edition_x86_dvd_3932296.iso'
$sourceDir 	= 'D:\Source'
$fullFilePath 	= $sourceDir + "\" + $iso
$shareName 	= 'share1'
$directoryName 	= 'dir1'

Select-AzureSubscription -SubscriptionName $subscription -Current
New-AzureStorageAccount -StorageAccountName $storageAccount -Label $storageAccount -Location $location -Type Standard_LRS
$storageAccountKey = (Get-AzureStorageKey -StorageAccountName $storageAccount).Primary
$context = New-AzureStorageContext -StorageAccountName $storageAccount -StorageAccountKey $storageAccountKey
$storageShare = New-AzureStorageShare $shareName -Context $context
New-AzureStorageDirectory -Share $storageShare -Path $directoryName
Set-AzureStorageFileContent -Share $storageShare -Source $fullFilePath -Path $directoryName

The script establishes connection to the target Azure subscription, creates a new, locally replicated storage account in the East US region, creates an Azure Files share named share1 with a directory named dir1, and copies into it an ISO image containing SQL Server 2014 Enterprise Edition installation media.

Now we are ready to proceed with the scripted deployment of the Azure IaaS virtual machine that will serve as the basis for our image. Once again, we need to come up with a unique name, which this time will be assigned to the cloud service functioning as a logical container hosting the virtual machine. Just as before, you can take advantage of the Test-AzureName cmdlet with the -Service and -Name parameters, where the value of the latter contains the string of characters you intend to use as the cloud service name. For the sake of simplicity, we will set it to servicename01 in our script. At this point, we are ready to run the script listed below, which will deploy a new Medium (i.e. Standard Tier A2) virtual machine with four data disks, combined into a single storage pool containing a thinly provisioned virtual disk with simple (i.e. striped) layout (and mounted as the F: drive). We leverage the newly provisioned storage account, which implies that the virtual machine resides in the same region we chose earlier. Afterwards, the script maps the Z: drive to the Azure Files share we created earlier, copies the ISO from its dir1 folder to the newly provisioned volume, and mounts it in preparation for the SQL Server setup. Note that the script should be run from the PowerShell session launched in the context of an account that has Administrator privileges on the local computer.

# As per
function Install-WinRmCertificate($serviceName, $vmName) 
    $vm = Get-AzureVM -ServiceName $serviceName -Name $vmName 
    $winRmCertificateThumbprint = $vm.VM.DefaultWinRMCertificateThumbprint 
    $winRmCertificate = Get-AzureCertificate -ServiceName $ServiceName -Thumbprint $winRmCertificateThumbprint -ThumbprintAlgorithm sha1 
    $installedCert = Get-Item Cert:\CurrentUser\My\$winRmCertificateThumbprint -ErrorAction SilentlyContinue 
    if ($installedCert -eq $null) 
        $certBytes = [System.Convert]::FromBase64String($winRmCertificate.Data) 
        $x509Cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate 
        $store = New-Object System.Security.Cryptography.X509Certificates.X509Store "Root", "LocalMachine" 

$subscription 	= 'My_Subscription'
$storageAccount = 'mystorageaccount01'
$location 	= 'East US'
$iso 		= 'en_sql_server_2014_enterprise_edition_x86_dvd_3932296.iso'
$sourceDir 	= 'D:\Source'
$fullFilePath 	= $sourceDir + "\" + $iso
$shareName 	= 'share1'
$directoryName 	= 'dir1'

Select-AzureSubscription -SubscriptionName $subscription -Current
Set-AzureSubscription -SubscriptionName $subscription -CurrentStorageAccountName $storageAccount

$serviceName 	= 'servicename01'
$location 	= 'East US'

New-AzureService -ServiceName $serviceName -Location $location

$vmName 	= 'VMSQLTemplate'
$vmSize		= 'Medium'
$adminUser      = 'SQLAdmin'
$adminPassword  = 't0P$ecr3T' 

$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

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

$storagePool	= 'SQL_StoragePool1'
$virtualDisk	= 'SQL_VirtualDisk1'
$virtualDiskGB	= 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:storagePool `
		-StorageSubSystemFriendlyName $ss.FriendlyName `
		-PhysicalDisks $pd
	New-VirtualDisk -FriendlyName $using:virtualDisk `
		-StoragePoolFriendlyName $using:storagePool -Size $using:virtualDiskGB `
		-ProvisioningType Thin -ResiliencySettingName Simple
	$vd = Get-VirtualDisk -FriendlyName $using:virtualDisk
    $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

$targetDir 	= $localDrive + ':\Source'
$targetPath 	= $targetDir + '\' + $iso

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

    $sourcePath = 'Z:\' + $using:directoryName + '\' + $using:iso
    $targetPath = $using:targetDir + '\' + $using:iso

    net use Z: \\$\$using:shareName /u:$using:storageAccount $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

This concludes the first part of the image-based deploment, resulting in the fully functional virtual machine that will serve as the basis for our template. In our upcoming articles, we will focus on the SQL Server-specific steps of the setup.

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