Rajendra Gupta
health of the availability group dashboard

Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups

September 1, 2020 by

In this 17h article of the SQL Server Always On Availability Group series, we are going to explore the SQL Server replication for the distribution database in the availability group.

Introduction

In the earlier article (see TOC at the bottom), we explored how you can add the publisher and subscriber database in a high availability configuration using SQL Server Always On Availability Groups. It used a remote distributor for transactional replication.

Suppose the publisher and subscriber databases are on [SQLAG1\INST1] node, and failover happens to the node [SQLAG2\INST2]. In this case, the transactional replication works fine without any issues. It provided high resilience for the publisher and subscriber database.

What about the distribution database? Can we add it to the availability group? In this article, let’s explore the steps required for the distribution database AG.

Overview of the distribution database in the SQL Server Always On Availability Groups

We can add the replication distribution database in the availability group for high availability starting from SQL Server 2017 CU6 and SQL Server 2016 SP2-CU3. It requires special considerations for adding the distribution database.

  • You must use a SQL listener for the distribution configuration
  • It creates SQL jobs with the listener’s name
  • It creates a new job to monitor the status of the distribution database in primary or secondary AG
  • SQL Server automatically disables and enables the jobs based on the primary replica. For example, if the distribution database is on the SQLAG1, jobs are enabled on the SQLAG1 instance. In the case of DB failover, jobs got disabled on the SQLAG1 and enabled on the SQLAG2 instance
  • The publisher and distributor database cannot exist in an instance
  • We cannot use merge, Peer-To-Peer replication
  • All replication databases (publisher, subscriber, and distributor) should be a minimum of SQL Server 2017 CU6 and SQL Server 2016 SP2-CU3
  • We can use both synchronous and asynchronous data synchronization for the distribution database
  • We cannot use the SSMS wizard for the distribution database AG configuration. It needs to set up using the scripts
  • We cannot use an existing distribution database for the AG configuration. If the replication is already configured, we need to break the replication first and configure it using the scripts for AG configuration
  • The secondary replica of the distribution database should allow read-only connections
  • You should use the same domain account in all replica of the distribution database AG

Environmental details

In this article, we use the following environment.

Existing AG configuration:

We configure the distribution database in a new availability group on the following replicas.

  • Primary Replica: SQLAG1\INST1
  • Secondary Replica: SQLAG2\INST2
  • Existing AG database: DBARepository

For this article, we use a standalone instance for the publisher and subscriber.

Publisher and Subscriber: SQLNode3\INST3

Therefore, the overall configuration in terms of the SQL Server replication topology is as below.:

  • Publication Server: SQLNode3\INST3
  • Subscription Server: SQLNode3\INST3
  • Distribution Server: AG replica ( SQLAG1\INST1 & SQLAG2\INST2)

You can use the previous articles in the series to prepare a similar environment.

Steps to configure SQL Server replication distribution database in SQL Server Always On Availability Groups

Note: You can find the script attached at the bottom of the article.

Step 1: Configure the AG nodes for the distribution DB nodes

In this step, we connect to both the AG replica instances and configure for the distribution. It registers both replica servers as a distributor.

We use the stored procedure sp_adddistributor for this purpose. You should specify the same password for both the servers.

Configure the AG nodes for the distribution DB nodes

Step 2: Configure the distribution database on the primary replica in SQL Server Always On Availability Groups

In this step, we do the following tasks.

  • Create the distribution database on the primary replica and install the relevant schema. In the query, we run the stored procedure sp_adddistributiondb in the master database
  • Modify the recovery model of the distribution database as FULL
  • Take a full backup of the distribution database so that it becomes eligible for an AG group

In the sp_adddistributiondb output, we can see the database objects it creates for the distribution database.

Configure the distribution database on the primary replica in SQL Server Always On Availability Group

Refresh the object explorer, and you see the [DistributionDB] under the system databases.

system databases

Step 3: Verify the endpoints for an availability Group

We already have a database configured on the availability group for both replicas. Therefore, the endpoint [HADR_ENDPOINT] exists. You can navigate to Server Objects -> Endpoints-> Database Mirroring-> script Hadr_endpoint.

Verify the endpoints for an availability Group

Similarly, verify the endpoint on the secondary replica node as well. In case of a new configuration, you should create the endpoint.

secondary replica node

Step 4: Add the distribution database in an existing SQL Server Always On Availability Group

Let’s try to add the distribution database in the existing availability group. If you launch the add database to the availability group wizard, it does not display the distribution database in the list. It only shows the user databases in this list.

Add the distribution database

As stated earlier, we cannot use the SSMS wizard for the AG configuration of the distribution database. Create a new availability group and specify both replicas, their endpoint in the script. You can note here that we used the automatic seeding for the initial database synchronization.

AG configuration of the distribution database.

Step 5: Add the secondary replica in the SQL Server Always On Availability Group

In this step, connect to the secondary replica and join it for the new availability group. We also permit to create a new database because of the direct seeding configuration in the previous step. SQL Server automatically creates the distribution database in the secondary replica and synchronizes it.

Add the secondary replica

Step 6: Create the SQL Listener for the distribution database availability group

In this step, create a new SQL listener for the distribution database availability group. Now, create the listener on the primary replica. You need to specify the listener IP address and the port. This port should be allowed within your firewall.

Add the secondary replica

Refresh the object explorer on the primary replica, and you see a new availability group, availability database (DistributionDB), and its listener.

new availability group

Step 7: Enable the secondary SQL Server Always On Availability Group replica for the distributor

Connect to the secondary replica and use the stored procedure sp_adddistributiondb to create it on the secondary AG replica.

Enable the secondary SQL Server Always On Availability Group replica for the distributor

Step 8: Configure the publisher to use the distribution database in SQL Server Always On Availability Group

Connect to both AG replica and use the stored procedure sp_adddistpublisher. You should specify a shared directory accessible from both replicas and distributor instances in the @working_directory argument.

Configure the publisher for SQL replication

Step 9: Configure the publisher server for SQL Server Always On Availability Groups

Connect to the publisher instance and execute the sp_addDistributor stored procedure. In the @distributor argument, you must specify the SQL Listener of the distribution database availability group.

Configure the publisher server

Step 10: Create a local publication in SQL Server replication

At this step, create a local publication in the SQLNode3\INST3 instance. You can follow the previous article for the detailed step.

Here, you can see my publication SQLPub_AG configured on the SQLNode3 instance.

Create a local publication

Step 11: Create a linked server on the primary and secondary replica

On both AG replicas, create a linked server, and it should point to the subscriber instance. In my case, both the publisher and subscriber database are in the same SQL instance.

Create a linked server

Step 12: Create a local subscription for SQL Server Always On Availability Group

On the subscription server, right-click on the local subscription and create a new subscription.

In the new subscription wizard, select the publication created earlier.

Create a local subscription

In the distribution agent location, select the first option – Run all the agents at the distributor. Here, you can note that it shows the SQL Listener name for push subscriptions.

distribution agent location

In the subscribers, select the existing subscription database or create a new database.

existing subscription

Use the SQL agent account or any appropriate account for the distribution agent security.

SQL agent account

We can have continuous synchronization or at a fixed schedule. Let’s go with the default option – Run continuously.

continuous synchronization

Initialize the subscription with a snapshot of the publication data, as shown below.

snapshot of the publication data

Create the subscription with the configurations we have done so far.

Create the subscription

It created the subscription successfully, as shown below.

subscription status

Launch the SQL Server replication monitor and view the replication status. It shows failed below because my distribution SQL service account does not have permission to connect to the publisher.

SQL Server replication monitor

Assign the permissions to the service account, and it quickly synchronizes the publisher and the subscriber.

Assign the permissions

It shows excellent performance with minimum latency.

performance with minimum latency

You should review the health of the availability group dashboard. We can verify that the DistributionDB database is in the availability group configuration.

health of the availability group dashboard

Failover the distribution database and monitor SQL Server replication

Let’s perform failover for the [DistributionDB] database from the SQLAG1\INST1 to the SQLAG2\INST2. It should not break the SQL Server replication because the distribution database will be available from the new primary replica in SQL Server Always On Availability Group.

Failover the distribution database for SQL Server replication

The failover is successful, as shown below.

Failover status

Refresh the object explorer of the previous secondary replica (SQLAG1\INST1), and here, you see two availability groups.

  • DistributionDB_AG: Secondary replica
  • SQLDRAG: Primary

Distribution DB replica

Launch the AG dashboard from the new primary replica SQLAG2\INST2. AG dashboard looks good.

Launch the AG dashboard

Verify the SQL Server replication status. We can verify here that the transaction replication is working fine after the distribution database failover as well.

replication status.

Verify the records should be the same in both publisher and subscriber database.

Verify the records

Attachment

Conclusion

In this article, we explored the process of adding a distribution database in the SQL Server Always On Availability Group configuration. It provides high availability for publication, subscription, and the distribution database for the SQL Server replication.

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
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Group
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views