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.).

2015-01-26 21.48.45

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:

IC665510

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

sql1

 

  • 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.

sql01

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

sql2

  • 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.

sql3

sql4

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

sql5

 

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.

sql8

 

Automatically a bunch of user roles are created for you. So you could integrated those roles with you Azure Active Directory for example.

sql11

  • You can also define metrics for easy diagnostics

sql12

 

Click on OK and Save

 

sql13

 

So, after a few more coffees everything is deployed and ready to go!

sql14

 

Let’s remote desktop to my High available SQL servers!

 

SQL Always on pre-configured !  So easy 🙂

sql15

  • You can also connect from your local desktop directly using your SQL management studio as shown below

sqlserverinvmconnectionmap

 

 

Conclusion

I was pretty impressed by how easy it is to deploy a high available SQL 2014 environment in just a few mouse clicks.

Leave a comment