Introduction

Recently, I received a requirement to configure the SQL Replication for the availability group database. The specific requirements were as below.

• Publisher database [DBARepository] is part of the availability group
• It is a two-node synchronized availability mode. These two nodes are [SQLAG1\INST1] and [SQLAG2\INST2]
• [SQLAG1\INST1] is the primary replica instance
• We require a local subscription database in the same SQL Instance. In the primary replica, [DBARepository] has a subscription for another database
• Both publisher and subscriber database should be part of the availability group
• In the case of AG failover, replication should work fine in the new primary replica as well

Prerequisites

• You should configure a two-node SQL Server Always On Availability Group as per our requirements. You can follow the series (TOC at the bottom) and prepare the environments
• You should understand SQL Replication and its components

Replication with the SQL Server Always On Availability Group

Availability groups help you meet the high availability (HA) and disaster recovery (DR) solutions for your database infrastructures.

A quick overview of the SQL Server replication

SQL Replication is popular to distribute a subset of data from the one database (publisher) to another database (subscriber). We can update the subscriber data continuously or at a defined interval.

We have the following types of replications in SQL Server:

• Transactional replication
• Snapshot Replication
• Merge Replication
• Peer to Peer Replication

In the below image, we can understand the components of transactional replication. You can refer to the article, SQL Server replication: Overview of components and topography for detailed learning.

Requirements and Restrictions for SQL Server replication with availability groups

SQL Server also supports the SQL Server replication on top of the availability groups.

• We can configure a Transactional, Merge and Snapshot replication for AG databases
• The publisher database can be a part of the AG
• We cannot use a secondary replica database as a publisher
• SQL Server does not support Peer-To-Peer replication and publishing data from the Oracle database
• It does not support the transactional replication with immediate or queued updating subscribers
• All the databases involved in the replication should be on the same version
• We can add the distributor database in the availability group from the SQL Server 2017 CU6 and SQL Server 2016 SP2-CU3. Previous versions do not support the distributor database in the AG group. There are a few requirements and restrictions for this, but we will talk about it in a separate article
• We can add a database having change data capture (CDC) in the AG group

Configure the SQL Server replication with SQL Server Always On Availability Groups

Step 1: Install Replication feature on SQLAG1, SQLAG2 and SQLNode3 instances

In this article, we will use the following servers.

• SQLAG1: Primary replica, Publisher and Subscriber
• SQLAG2: Secondary replica, Publisher and Subscriber(after failover)
• SQLNode3: Distributor

You should install the Replication feature on all three SQL nodes using the SQL Server Setup. If you do not have replication components installed, you get the following error 21028.

You can launch the SQL Server setup and select the SQL Server Replication, as shown below.

#always on availability groups #installation #setup and configuration #sql