This is the 16^th article for SQL Server Always On Availability Groups series and explores SQL replication integration with AGs.

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.

Step 2: Configure a remote distributor for availability groups

We require the distribution database on the remote server. This remote server should accessible from both the AG replicas.

Connect to the distributor instance (SQLNode3) in SSMS and navigate to Replication -> Configure Distribution -> Configure Distribution Wizard.

In the next page, select the first option SQLNode3\INST3 will act as its own Distributor. SQL Server creates the distribution database in this instance.

You should configure the SQL Server Agent service in the automatic mode. The distribution wizard can change the configuration for you, but the SQL service account must have the administrator permission on the server.

Configure a snapshot folder. Let’s go with the default ReplData folder.

Configure the distribution database name and its data file, log file locations. You must not change the database name in the Distribution database name box. By default, it shows the default data file locations in the wizard.

On the next page, we need to add the publishers that use this distributor. By default, it shows the current server in the list.

Remove the check from the SQLNode3\INST3 and add both AG replica nodes [SQLAG1\INST1] and [SQLAG2\INST2] using the Add-> SQL Server option.

Specify a distributor password. You require this password to connect the remote distributor from the publisher and subscribers.

Configure the distribution or generate the script.

On the next page, we see the status of each task performed by the distribution wizard. You can note here it failed to change the SQL Server agent service to automatic mode because in my demo the service account does not have administrative permissions on the server. We can ignore this error message in the distribution wizard.

You can open the SQL Server Configuration Manager and change the SQL Server Agent in the automatic start-up mode.

Step 3: Configure publishers to use the remote distribution

We need to configure both availability group replicas (SQLAG1, SQLAG2) to use the remote distribution.

Log in to the primary replica SQLAG1 and navigate to Replication-> Configure distribution. In the wizard, select the option – Use the following server as the Distributor and add the remote distributor SQLNode3\INST3 as shown below.

On the next page, enter the distributor administrative password that we set in step 2.

Configure the distributor in the primary replica SQLAG1.

It configured the remote distributor successfully.

Similarly, configure the remote distributor on the secondary replica SQLAG2\INST2.

Step 4: Create a publication on the primary replica

To create a publisher on the primary replica, navigate to replication -> Local Publication ->New Publication. It opens the publication wizard with brief information about the wizard.

Select the publication database. In my demo, DBARepository database is part of the availability group, choose this database and click Next.

Select the publication type on the next page. We require a transaction replication in this article.

In the articles page, select the objects that we wish to replicate. In my case, I have a single article eligible for transactional replication. Select the article and click Next.

You can schedule a Snapshot agent run at a specific schedule or run it immediately. Select the first option and create a snapshot immediately.

In the agent security, we configure the account under which these agent services run. It is good to configure these services under the SQL Server Agent service account however you can change it as per your security requirements.

We have provided all inputs in the publication wizard. Select the option- Create the publication, as shown below.

Specify an appropriate publication name, verify the publication summary and click on Finish.

We configured the SQL publication for this article, as shown below.

Step 5: Create a subscription database and add into the existing SQL Server Always On Availability Group

Usually, for the replication in the standalone instance, we can create a subscription database in the subscription wizard. In the replication on the SQL Server Always On Availability Group, we need to do the following tasks:

• Create a subscription database on the primary replica
• The subscription database should be in full recovery mode
• Add the subscription database in an existing availability group. We should use the same availability group for both publisher and subscriber database

To add a new database into an existing availability group, expand the availability group on primary replica and expand the availability group databases. Right-click on it to add a new database.

You should perform a full backup of the subscription database so that it becomes eligible for availability group configuration. In the demo, I created a new database DBASubscriber and took a full back up before launching the AG wizard.

Connect to an existing secondary replica SQLAG2\INST2.

Select the automatic seeding for the initial data synchronization. It automatically creates a new database on the secondary replica and synchronizes with the primary replica.

Verify the configurations specified in the add database to an availability group wizard.

It adds the database into the existing AG SQLDRAG.

In my case, it quickly synchronizes the primary replica with the automatic seeding because the subscriber database is empty (no objects) on the primary replica.

Step 6: Verify SQL Listener for SQL Server Always On Availability Groups

We require the SQL Listener for configuring the replication in the SQL Server Always On Availability Group. I already have a SQL listener configured, as shown below.

In case you do not have a listener, you can refer to my previous articles and configure it.

• SQL Listener: SQLDR
• Listener port: 2233
• Listener IP address: 10.0.2.80

Step 7: Create a linked server on the SQL Server Always On Availability Groups replica

We need to create the linked server on both availability group replicas. This linked server uses the SQL Listener for the configuration. It ensures that in case of failover, the secondary replica can connect to the listener.

Execute the script on both SQLAG1 and SQLAG2 replicas.

You can expand the linked server, and it should browse the objects as shown below.

Step 8: Create a push subscription in the transactional replication for SQL Server Always On Availability Groups

We cannot use the SSMS wizard to configure the push subscription for the publisher in the availability group.

We use the stored procedure sp_addsubscription and sp_addpushsubscription_agent stored procedure for this purpose. Execute the below script (attached) on the primary replica publication database.

In this command, modify the following arguments as per your configurations:

• @publication: Enter the publication name we created earlier
• @subscriber: Enter the SQL listener name
• @destination_db: Specify the subscription database name

It creates the required jobs to run under the SQL Server Agent service account.

Step 9: Launch replication monitor and view the replication status

Right-click on the publication in the primary replica and launch the replication monitor. Oh! It failed!

Look at the error message in the yellow box, and it could not execute the sp_replcmds on the primary replica SQLAG1\INST1.

As highlighted earlier, we use the listener for the replication configuration in the Availability group. We also specified the listener’s name in the step 8 query.

To fix the error message, connect to the distributor database on the SQLNode3\INST3 instance and run the following script. This script uses a new stored procedure sp_redirect_publisher. Specify the value of the following arguments.

• @original_publisher: Enter the primary SQL replica instance. We configured the publication earlier in the primary replica
• @publisher_db: Specify the publisher database name. In my case, [DBARepository] database is in AG and configured as a publisher database
• @redirected_publisher: Specify the SQL listener along with its port number

Wait for some time, and it starts the replication as shown below. You should give permissions for the service account configured earlier to access the publisher and subscriber database.

Replication Monitor: Publisher to Distributor History

Replication Monitor: Distributor to Subscriber History

Replication Monitor: Undistributed Commands

Step 10: Verify the records in both publisher and subscriber databases

In this step, you should connect to the publisher and the subscriber database and verify the records in the replicated articles. You can insert new records into the publisher as well, and it should replicate immediately in the subscriber database.

Step 11: Perform an SQL Server Always On Availability Group failover and verify the transaction replication

I always recommend testing the availability group failover after a new implementation. Perform an AG failover from the current primary replica [SQLAG1\INST1] to [SQLAG2\INST2].

AG failover completed successfully.

AG dashboard is healthy after failover as well. It shows the primary instance SQLAG2\INST2 as the primary replica.

Insert a new record in the publisher database and verify it in the subscriber. In the below screenshot, row 5 is inserted after failover, and it is reflecting in the subscriber database as well.

You might notice a new identity range starts after the failover. It is a usual replication behavior where SQL Server assigns different identity ranges for the publishers and the subscriber databases. You can refer to the article, Replicate Identity Columns for more details.

Conclusion

In this article, we configured integration of SQL Server replication and SQL Server Always on Availability Groups for local publication and subscription. We will look at a few more replication scenarios in the upcoming article, along with the configuration of the distribution database in the availability group.

Attachment

Table of contents

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023