| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

Azure Virtual Machines running SQL Server 2016 STD AlwaysON Availability Groups w/ Transactional Replication
by BF (Principal Consultant; Architecture; Engineering)

AlwaysOn Availability Groups is a High-Availability and Disaster-Recovery enterprise-level solution offered by Microsoft.

An Availability Group provides a failover scenario for user databases. Depending on the SQL Server product version, an Availability Group supports read-write primary databases and up to eight corresponding secondary databases. Secondary databases can also be made available for read-only access and/or some backup operations if needed. An Availability Group will failover over at the level of an Availability Replica, which is database-level.

Availability Group in Azure Virtual Machines requires an Azure Load Balancer and the LB has the same IP as the Availability Group Listener. Each Availability Group will require a Listener.

SQL Server Transactional Replication is supported with AlwaysON Availability Groups. A Remote Distributor is needed to provide a smooth failover process for the Publisher. AlwaysON Availability Groups requires a Windows Server Failover Cluster but it does not require SQL Server Engine to be clustered, but that is an option if you need it.

Azure Virtual Machines running SQL Server 2016 STD AlwaysON Availability Groups w/ Transactional Replication

High-Level Steps:

#1. Create Microsoft Azure Virtual Machines - Node1(PRI), Node2(SEC), Cluster File Share Witness - Independent Storage, Availability Set

#2. Create Microsoft Azure Load Balancer

#3. Create Windows Server Failover Cluster - 2 Nodes, 1 Cluster File Share Witness, 1 Role(AG), 1 Listener(AG)

#4. Install SQL Server 2016 STD on each of the three Nodes

#5. Enable AlwaysON High Availability on Node 1 & Node 2

#6. Create AlwaysON Availability Groups - 1 User Database per AG, 1 Listener per AG

#7. Create Transactional Replication Publications - File Share Witness also serves as a Remote Distributor

The above specs are decided upon due to using SQL Server 2016 Standard Edition and the features supported with AlwaysON Availabiltiy Groups.


Introducing SQL Server Always On availability groups on Azure virtual machines

Always On Availability Groups (SQL Server)

Configure Replication for Always On Availability Groups (SQL Server)

Select Initial Data Synchronization

Basic Availability Groups (Always On Availability Groups)

Basic availability groups use a subset of features compared to advanced availability groups on SQL Server 2016 Enterprise Edition. Basic availability groups include the following limitations:
*Limit of two replicas (primary and secondary).
*No read access on secondary replica.
*No backups on secondary replica.
No integrity checks on secondary replicas.
No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).
No support for adding or removing a replica to an existing basic availability group.
*Support for one availability database.
Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition.
*Basic availability groups are only supported for Standard Edition servers.
Basic availability groups can not be part of a distributed availability group.