SQL Server 2014 AlwaysOn on Massive G-series Azure VMs

5 minute read

In this blog post I’m going to test the recent availability of the massive G-Series VMs and ability to automate High Availability for SQL 2014. The fact that Azure provides high availability mechanisms, such as service healing for cloud services and failure recovery detection for the Virtual Machines, does not itself guarantee you can meet the desired SLA. These mechanisms protect the high availability of the VMs but not the high availability of SQL Server running inside the VMs. It is possible for the SQL Server instance to fail while the VM is online and healthy. Moreover, even the high availability mechanisms provided by Azure allow for downtime of the VMs due to events such as recovery from software or hardware failures and operating system upgrades.

We will deploy SQL 2014 Alwayson on the G series vms!

G-series sizes provide the most memory, the highest processing power and the largest amount of local SSD of any Virtual Machine size currently available in the public cloud. This extraordinary performance will allow customers to deploy very large scale-up enterprise applications. G-series offers up to 32 vCPUs using the latest Intel® Xeon® processor E5 v3 family, 448GB of memory, and 6.59 TB of local Solid State Drive (SSD) space. This powerful VM size easily handles deployments of mission critical applications such as large relational database servers (SQL Server, MySQL etc.,) and large NoSQL databases (MongoDB, Cloudera, Cassandra etc.).

In the table below you can find an overview of the G-series (pretty impressive stuff if you ask me 🙂 )

Standard tier – G series* {.subHeading}

Size – Management Portal\cmdlets & APIs	CPU cores	Memory	Max. disk sizes – virtual machine	Max. data disks (1023 GB each)	Max. IOPS (500 per disk)
STANDARD_G1\(same)	2	28 GB	OS = 127 GBLocal SSD disk = 384 GB	4	4 x 500
STANDARD_G2\(same)	4	56 GB	OS = 127 GBLocal SSD disk = 768 GB	8	8 x 500
STANDARD_G3\(same)	8	112 GB	OS = 127 GBLocal SSD disk = 1,536 GB	16	16 x 500
STANDARD_G4\(same)	16	224 GB	OS = 127 GBLocal SSD disk = 3,072 GB	32	32 x 500
STANDARD_G5\(same)	32	448 GB	OS = 127 GBLocal SSD disk = 6,144 GB	64	64 x 500

In the new Azure portal you can find the new auto HA setup capabilities using the AlwaysOn Portal Template added for SQL Server in Azure VMs. You can also install SQL alwayson manually yourself but it requires quite a lot of configuration and virtual machines. A step by step overview can be found here.

If you don’t want to do it all by yourself and want to get started very quickly you can create a High available SQL Alwayson infrastructure in just a few mouse clicks. The SQL server 2014 Always on template that is available from the Azure galery will install the following:

Let’s get started!

On the Azure Management Portal, at the bottom left of the web page, click +NEW
On the Create a Virtual Machine page, you will see the new SQL Server 2014 Always on template
Click Create

Make sure to select WestUS or East US as region as the G-series VMs are not available in all the regions.
Specify a Resource Group name

Resource groups are enabled by the new management functionality, Azure Resource Manager. Azure Resource Manager allows you to group multiple resources as a logical group which serves as the lifecycle boundary for every resource contained within it. Typically a group will contain resources related to a specific application. For example, a group may contain a Website resource that hosts your public website, a SQL Database that stores relational data used by the site, and a Storage Account that stores non-relational assets.

Click on SQL server settings
Select a G serie VM. I’m going to deploy the G5 Series VMs 😉

Next specify the Availability Group Name and Group Listener Name

Availability Groups, released in SQL Server 2012 and enhanced in SQL Server 2014, detect conditions impacting SQL Server availability (e.g. SQL service being down or losing connectivity). When detecting these conditions, the Availability Group fails over a group of databases to a secondary replica. In the context of Azure Infrastructure Services, this significantly increases the availability of these databases during Microsoft Azure’s VM Service Healing (e.g. due to physical hardware failures), platform upgrades, or your own patching of the guest OS or SQL Server.

Client applications connect to the primary replica of an availability group using an Availability Group Listener. The Listener specifies a DNS name that remains the same, irrespective of the number of replicas or where these are located.

For example: Server=tcp:ListenerName,1433;Database=DatabaseName;

To support this in Azure Virtual Machines, the Listener must be assigned the IP address of an Azure Load Balancer. The Load Balancer routes connections to endpoint of the primary replica of the Availability Group. This is done automatically for you in the template.

Next Create Storage account
For an overview of the different storage options have a look here http://www.mscloud.be/azure-storage-redundancy-options/

Click OK and then Create to launch the creation of the VMs.

This might take a while as beside a new network and 3 new Active directory domains, new storage account and 8 virtual machines will be created!

You can follow the Progress in the event tabs

In the ResourceGroup overview you can see all that will be deployed:

1 new dedicated network
3 new domains
1 new storage account
2 Domain controllers
1 File Witness Server
2 SQL servers

Can you imagine you have to deploy and configure all of this yourself…

If you look at the newly created resource group, you have a quick overview of all the different components and the status. At the moment we are still deploying or creating the vm’s, so that’s why they are in a warning state.