Tag Archive for SQL Server

Automating SQL Server VM Provisioning on Azure with PowerShell

Azure IaaS provides a flexible way to fire up new VM without worrying about upfront infrastructure cost. We can provision new VM for DEV/TEST quickly, and proceed with development task. The common task I often do is installing new SQL Server VM, setting up, so it will be ready for testing bench or isolated development. It requires couple of clicks and wait, and sometime tedious.

This article explains how to provision new SQL Server VM by using Azure Management portal. It is easy, but still require couple of click and wait between clicks. We can automate those tasks by using PowerShell, so everything can be done with an execution of a script file. This post also has step by step instruction on how to deploy simple SQL VM on Azure, but I will take another approach in this post. The script in this post will do the following stuffs in VM provisioning:

  • Provision a stand alone SQL Server, this means does not join to AD domain
  • Provision a storage account to store VHD
  • Deploy the VM to specific Azure region, by supplying Affinity Group to control its location

Since this is a stand alone deployment, there will be no Virtual Network created. The VM is accessible through public cloud service name and its endpoint port number.

The first step is connect to Azure account from Windows Azure PowerShell console as follow:


A pop up will appear asking for Azure account credential.


Declare all required variables for Affinity Group and storage account

The following lines declares parameter related with cloud service name, affinity group and storage account.


$location="Southeast Asia"


$affinityGroupDescription="SQL SEA Affinity Group"

$affinityGroupLabel="SQL SEA IaaS Group"


$storageAccountLabel="sql2012teststorage Storage Account"



Declare variables for VM configuration

Azure provides various VM image, so the script need to know which image to pick for provisioning. Executing Get-AzureVMImage and filter based on label name is one way to retrieve What We wanted.

$sqlImageName = (Get-AzureVMImage | where {$_.Label -like "SQL Server 2012 SP1 Enterprise*"} | sort PublishedDate -Descending)[0].ImageName
$sqlServerName = "kintamanisql"
$cloudServiceName = "kintamanicloud" 
$vmAdminUser = "myadmin" 
$vmAdminPassword = "Company!000"


Create Affinity Group

An Affinity Group controls the location of cloud service, so We can put the VM in specific region closer to users. USe the following lines to create the Affinity Group:

New-AzureAffinityGroup `
    -Name $affinityGroupName `
    -Location $location `
    -Description $affinityGroupDescription `
    -Label $affinityGroupLabel


Create Azure storage account

A new storage account will be created to store VHD image. This account is located in the Affinity Group specified above, which is Southeast Asia.

New-AzureStorageAccount `
    -StorageAccountName $storageAccountName `
    -Label $storageAccountLabel `
    -AffinityGroup $affinityGroupName


Create cloud service

Cloud service is the public facing component of VM to external world. It is a service with FQDN, so accessible from internet via endpoint at specific port. The cloud service ties to Affinity Group so bound to specific region in Azure data center.

New-AzureService -ServiceName $cloudServiceName -Label "Cloud for SQL VM" -AffinityGroup $affinityGroupName


Create SQL Server VM

Once the storage account and cloud service created, the VM is ready to be provisioned. All required endpoint to access SQL Server services is also included in the VM creation.

New-AzureVMConfig `
    -Name $sqlServerName `
    -InstanceSize Small `
    -ImageName $sqlImageName `
    -MediaLocation "$storageAccountContainer$sqlServerName.vhd" `
    -DiskLabel "OS" | 
    Add-AzureProvisioningConfig `
        -Windows `
        -DisableAutomaticUpdates `
        -AdminUserName $vmAdminUser `
        -Password $vmAdminPassword |
	Add-AzureEndpoint -Name "SQL" `
        -Protocol "tcp" `
        -PublicPort 22001 `
        -LocalPort 1433|
    New-AzureVM -ServiceName $cloudServiceName

We can consolidate all of those lines into one PowerShell script file so it is ready for execution. The following file put all of those tasks together.

It will take a while for VM provisioning to run. Once completed, select the VM name and click Connect to download the RDP file.


Once downloaded, execute the RDP file to access Remote Desktop in the VM.