Azure Virtual Machines and SQL Server Always On Availability Groups – Everything you need to know

Azure Virtual Machines and SQL Server: I am pretty sure that you are familiar with the term “Availability Groups” if you’re working on SQL Server. Availability Group is a set of one or more readable secondary databases that provide an available replica of a primary database. Availability replicas can be configured to support read-only access, thus enabling reporting against backups of the primary databases. From this point of view alone, Availability Groups sounds like something I would love to use for all my production databases (if they were not stored in Azure VMs already). This makes me wonder whether Availability Groups could also work for Virtual Machines. Keep reading and find out!

Note: It’s important to understand that this test was performed using Microsoft internal builds of SQL Server 2012 SP2 and SQL Server 2014 SP1. I am also not saying that Availability Groups should or will replace Database Mirroring for hosting production databases on VMs hosted in Azure, but rather that it could be an option for certain use cases (such as development/test environments).

Let’s first recap what you need to be aware of when it comes to Availability Groups on Azure VMs:

Azure Virtual Machines and SQL Server: The Primary Database must be running the Enterprise Edition of SQL Server The Secondary Databases are read-only, but can still allow writes if they are not part of an Availability Group If you create/use a database on one VM, it automatically becomes an availability replica. You cannot have more than 1 replica for each primary database. Readable secondary replicas are not supported anytime soon (see below)

To set up Availability Groups between Virtual Machines, you basically have 2 options:

Create a Database on the Primary VM and then configure that as the Replica Set Add the Secondary VM(s) directly to an already created Replica Set

In terms of SQL Server, there are a couple of pre-requisites you have to meet before proceeding:

The Primary Database must be running the Enterprise Edition of SQL Server The Secondary Databases cannot run the Enterprise Edition of SQL Server The Secondary Databases can only be configured as read-only (no writes)

Configuring Availability Groups using a Database on the Primary VM

First, up is configuring an Availability Group that uses a database on the primary VM for its replica set. To do this, follow these steps:

Create a new DB on your primary VM. In my example, I named it “Azure Availability” Click on Always on High Availability from within the Management Studio Click New Availability Group Specify your group name and select which databases the Availability Group should contain. I also checked “Allow this availability group to be backed up” Although you can add secondary replicas during creation, I went back and created them later (using the Add Secondary button). For each replica added, make sure they are using read-only access

Once your Availability Group is created, it will automatically start synchronizing your databases with the newly added replicas. Depending on which VM is currently hosting the primary database, different nodes may become active or passive in terms of testing out writes against their local copy of the data. This means that if you’re running SQL Server Management Studio on PrimaryVM1 and want to run a select statement against Azure Availability, you might get errors about not being able to write even though the Availability Group will indicate the SecondaryVM1 is active.

Secondary VM(s) are not displayed as Read-Only when added to an existing Replica Set

When you create a new Availability Group, the Secondaries are automatically added using read-only access, but when adding Secondaries to an existing replica set (no matter whether it’s one running on VMs or in Azure), they will be listed as “Unknown”. This means that if you’re trying to run tests against your local copy of Azure Availability on either PrimaryVM2 or SecondaryVM3, you might end up with errors about not being able to write. To solve this issue, simply go back and remove all secondary replicas from your Availability Group, then add them again.

Configuring Availability Groups using secondary replicas directly on VMs

Although the use of a database hosted on the primary VM is not recommended for production, you might have good reasons for doing so in your development/test environments. For example, if you already have a replica or two created locally in Azure and don’t want to create another one just to add it as a secondary replica in an Availability Group, this might be useful when moving to fail-over testing, etc. in which you swiftly want to switch roles between different VMs in terms of being primary or secondary. Depending on how many IOPS your storage account has available, having more than 1 replica hosted locally might also save you some money if your application is read-heavy.

Conclusion:

Azure Virtual Machines and SQL Server: Creating an Availability Group using a database hosted on the primary VM is not recommended for production, but you can still use it during development and testing if the above requirements are met. The use of replicas directly on VMs as secondaries will save money and IOPS required for storage against your local replica(s) in Azure if you’re looking at running this locally within VMs.

Also Read: Intelligent Character Recognition and its implication in businesses