Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



Azure Virtual Machines running SQL Server 2016 STD AlwaysON Availability Groups w/ Transactional Replication
by BFarrell (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-02-17








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.







Resources:

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