Rajendra Gupta
SQL Server replication

Configure SQL Server replication for a database in SQL Server Always On Availability Groups

August 31, 2020 by

This is the 16th 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.

SQL Server replication

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.

Replication feature error

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

Install replication feature

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.

Configure a remote distributor for availability groups

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.

Configure distribution database

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.

SQL service account

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

snapshot 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.

Distribution database name and directories

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

Select publisher

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

SQL Server option for SQL Server Always On Availability Group

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

Remote distributor password

Configure the distribution or generate the script.

Configure the distribution

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.

Distribution error

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

SQL Server Configuration Manager

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.

Configure publishers to use the remote distribution

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

Specify remote distributor password

Configure the distributor in the primary replica SQLAG1.

Configure distributor

It configured the remote distributor successfully.

Progress

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.

Create a publication on the primary replica for SQL Server Always On Availability Group

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

publication database

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

publication type

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.

Select articles

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

schedule a Snapshot agent

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.

Agent security

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

Create Publication

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

Specify publication name

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

Monitor publication

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.

Create a subscription database

Connect to an existing secondary replica SQLAG2\INST2.

Connect to an existing secondary replica

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.

automatic seeding

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

Verify the configurations in SQL Server Always On Availability Group

It adds the database into the existing AG SQLDRAG.

Wizard success message

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.

SQL Server Always On Availability Group dashboard

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

Verify SQL Listener for SQL Server Always On Availability Group

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.

Create a linked server

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

Create a push subscription

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

push subscription jobs

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.

view the replication status

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 status

Replication Monitor: Publisher to Distributor History

Publisher to Distributor history

Replication Monitor: Distributor to Subscriber History

Distributor to Subscriber history

Replication Monitor: Undistributed Commands

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.

Verify the records in both publisher and 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].

Perform an SQL Server Always On Availability Group failover

AG failover completed successfully.

AG failover status

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

AG dashboard

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.

Verify records in SQL Server replication

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

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability

Rajendra Gupta
1,934 Views