Azure 101 Part (2): Understanding Availibility Sets

This post second installments of Azure 101. I posted about affinity groups a while ago to explain how do We control location of cloud services. Understanding availability sets is very important if We care about high availability of the VMs.


Increasing high availability

Availability sets is the way Azure control the physical host location of VMs, so they will be provisioned on different rack in Azure data center. If two VMs were created without specifying availability sets, there is no guarantee that they will be located in different rack. In the case of planned maintenance or rack failure, all of them will be offline.
Grouping multiple VMs in the same availability sets ensures that they will be distributed to different power and network sources. This means If one of them unavailable the one in another rack is still fine. This also ensures the configuration meets 99.95 Azure SLA as stated here.
The following diagram depicts multiple VMs in different rack for the purpose of SQL Server Availability Group cluster.


As outlined in the diagram, each VM connected to different power and network sources. This increases their availability in either planned or unplanned downtime. Multiple VMs with the same purpose should be located in the same availability sets to provide high availability or redundancy, for example:

  • Configuring multiple VMs to create node in SQL Server AlwaysOn Availability Groups or Failover Cluster Instance
  • Providing load balancing between VMs to hosts various services such as IIS or file server



Creating Availability Set

There are several way to create availability set. The easiest one is by defining its name during VM creation. Make sure to choose From Gallery so the following window will appear:


Once multiple VMs in the same availability sets are created, they will appear under the same set as follow:


In the case the VM has been created but it does not belong to any availability sets, the following PowerShell command will update the VM to join availability set:

Get-AzureVM -ServiceName "theNameofVMsCloudService" -Name "VMNamehere" |
 Set-AzureAvailabilitySet -AvailabilitySetName "nameofAvailabilitySet" |

Updating VM’s availability sets causes reboot, so plan accordingly.

Azure 101 Part (1): Understanding Affinity Groups

Azure comes with new keywords that many IT Pro and Developer may not be familiar with. Understanding the key concepts is important in designing and deploying Azure application, especially if We care about High Availability and Disaster Recovery. This post will observe the meaning and relationship between three important concepts: Affinity Groups, Availability Set, and Cloud Service.


Affinity Groups

The idea of Affinity Groups is quite simple: any applications, services, and infrastructure should be in the same data center for best performance. Of course this also depends on the connection between locations, but keeping all of them in the same region will give faster accessibility than separate them to multiple region. An Affinity Groups is the way Azure ties various resources as close as possible, by keeping them in the same region. As the time of this writing, Azure data centers are located at the 9 areas as follow:

  • North Central US
  • South Central US
  • East US
  • West US
  • South America
  • North Europe
  • West Europe
  • East Asia
  • South East Asia


Creating Affinity Groups

An Affinity Groups can be created either in Management Portal or PowerShell. The setup is located under Settings menu of the portal. Once it is created, it can be assigned to other Azure resources to keep them stay close in the same region.


There are 3 fields to fill in when creating Affinity Groups:

  • Name: the name for affinity group.
  • Description: the description for affinity group.
  • Region: the region where this affinity group will be located

create AG

Creating Affinity Groups with PowerShell is straight forward by using the following command:

New-AzureAffinityGroup -Name "SEA IaaS"-Location "Southeast Asia"-Description "IaaS group in SEA" -Label "SEA IaaS Group"

The name must be unique within an Azure Subscription. This does not limit us to create multiple Affinity Groups with the same region, as long as their names are different. Once created, the region location in it cannot be modified.


How Affinity Group relate to another Azure resources?

The relationship between Affinity Groups with other resources can be in many ways, depending on the sequence and approach in provisioning of those resources. Below are two examples:

  • Storage Account; Provisioning storage account requires either Location or Affinity Groups as mandatory parameter. The storage account is where all data related stuff located. These can be BLOB, database file, table, and VHD files belong to Virtual Machine.
  • Cloud Service; Similar with storage account, Cloud Service provisioning also requires some parameters related to location, which can be supplied with Affinity Groups or region name. I will explain this more detail in another post.

So to sum it up, the following diagram depicts logical relationship between Affinity Groups, storage account, cloud service, and another Azure services that may sit on top of that.




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.





Running WordPress with SQL Server on Azure

It has been more than a year this blog runs on Azure platform, since I moved from my Linux hosting (WordPress with PHP – MySQL) to Azure. It’s still WordPress (I love it) but sits on top of WAWS with MySQL. I have been experimenting to run WordPress on IIS with MySQL for quite a while, until I read that I can actually run WordPress with SQL Server instead of MySQL.

I’m a SQL Server guy, and decide it to give it a try. Running it on SQL Server means I can do query, tuning, and everything on database that I really familiar with. Running it with SQL Server is even easier now in Azure by using special installer developed by Brandoo.


Creating Azure SQL Database

So I started the deployment by creating an SQL database in Azure. It is and SQL Server engine, not MySQL. This is and empty database for WordPress storage in the next step.


You may choose Quick Create or Custom, whichever is convenience. Specify the new server If You never create any before.


Once the database is created, You need to take note of database name, SQL login, password and also server name. Those information will be used for WordPress configuration in the next step.


Install WordPress on SQL Server

I use WAWS to deploy my WordPress site. This is the fastest and easiest way to deploy a web site in Azure, because I do not need to bother with all underlying infrastructure. If You have not create one, You need to create a Web Site by selecting Compute > Web Site > From Gallery.


The next screen will show different types of web application available in Azure Web Site. Select Brandoo WordPress under Blog category. This distribution runs on SQL Server instead of default MySQL


The next dialog will ask for web site URL and database user name. Make sure to specify the same user name and password created earlier so You can connect to existing SQL database.


You will be connected to existing database, and WordPress installation should kick in. Once is completed, the website is listed in web site section list as follow:


You can browse to the web site URL, for example and the new WordPress blog will be loaded. As a proof that It really runs on SQL Server, open SSMS and connect to SQL database by using server name and credential created earlier.


Now I have created a new WordPress site on SQL Server. The next step is to import all of my blog contents from old site to new one. This is pretty straight forward by using WordPress importer/exporter plugin.

And runs on WP – SQL Server in Azure now :).