Set up replication distribution database in Always On availability group

Article
09/25/2024

Applies to: SQL Server

This article explains how to set up a SQL Server replication distribution database in an Always On availability group (AG).

SQL Server 2017 CU6 and SQL Server 2016 SP2-CU3 introduces support for replication distribution database in an AG through the following mechanisms:

The distribution database AG needs to have a listener. When the publisher adds the distributor, it uses the listener name as the distributor name.
The replication jobs are created with the listener name as the distributor name. Replication snapshot, log reader and distribution agent (push subscription) jobs created on the distribution server gets created on all secondary replicas of the AG for Distribution DB.

Note

Distribution agent jobs for pull susbcriptions are created on the subscriber server and not on the distribution server.

A new job monitors the state (primary or secondary in AG) of the distribution databases and disables or enables the replication jobs based on the distribution databases state.

After a distribution database in the AG is configured based on the steps described below, replication configuration and run time jobs can run properly before and after distribution database AG failover.

Supported scenarios

Configuring distribution database to be included in an AG.
Configuring replication such as publications and subscriptions before and after AG failover.
Replication jobs functional before and after failover.
Removing replication at distributor and publisher when distribution database is in AG.
Adding or removing nodes to existing distribution database AG.
A distributor may have multiple distribution databases. Each distribution database can be in its own AG and can be not in any AG. Multiple distribution databases can share an AG.
Publisher and distributor need to be on separate SQL Server instances.
If the listener for the availability group hosting the distribution database is configured to use a non-default port, then it's required to set up an alias for the listener and the non-default port.

Limitations or exclusions

Local Distributor (where the Publisher server is also the Distributor) isn't supported. The Publisher and Distributor must be separate SQL Server instances. These instances can be hosted on the same sets of nodes. A local Distributor isn't supported for the following reasons:
- If the Distributor is configured locally, you can't use the availability group listener to route traffic to the Distributor, which causes replication agents to fail after failover.
- If a local Distributor is configured and then the Distributor availability group fails over to the original secondary, the Publisher connection to the Distributor changes from local to remote, which causes replication stored procedures and agents to fail.
Oracle publisher isn't supported.
Merge replication isn't supported.
Transactional replication with immediate or queued updating subscriber isn't supported.
Peer to peer replication isn't supported prior to SQL Server 2019 (15.x) CU 17
All SQL Server 2017 instances hosting distribution database replicas must be SQL Server 2017 CU 6 or later.
All SQL Server 2016 instances hosting distribution database replicas must be SQL Server 2016 SP2-CU3 or later.
All SQL Server instances hosting distribution database replicas must be the same version, except during the narrow timeframe when upgrade takes place.
The distribution database must be in the full recovery model.
For recovery and to allow transaction log truncation, configure full and transaction log backups.
The distribution database AG must have a listener configured.
Secondary replicas in a distribution database AG can be synchronous or asynchronous. Synchronous mode is recommended and preferred.
Bidirectional transactional replication isn't supported.
SSMS does not show Distribution Database as synchronizing/synchronized, when distribution database is added to an availability group.

Note

Before executing any of replication stored procedures (for example - sp_dropdistpublisher, sp_dropdistributiondb, sp_dropdistributor, sp_adddistributiondb, sp_adddistpublisher) on secondary replica, make sure the replica is fully synchronized.
All secondary replicas in a distribution database AG should be readable. If a secondary replica isn't readable, distributor properties in SQL Server Management Studio on the particular secondary replica can't be accessed, however replication will continue to work correctly.
All the nodes in the distribution database AG need to use the same domain account to run SQL Server Agent, and this domain account needs to have the same privilege on each node.
If any replication agents run under a proxy account, the proxy account needs to exist in every node in the distribution database AG and have the same privilege on each node.
Make changes to distributor or distribution database properties in all replicas participating in distribution database AG.
Make replication jobs changes through msdb stored procedures or SQL Server Management Studio in all replicas participating in distribution database AG.
If using a custom profile for any agent, it must be manually created on all secondary replicas by using the procedure sp_add_agent_profile. The profile must have the same id on all replicas. If the profile doesn't exist on a secondary replica, you might get Primary Key violation errors after failover. You will likely need to reinitialize the subscription for the publication to resolve the errors.
Configuring distributor on the publisher needs to be done with scripts. The replication wizard can't be used. Replication wizards and property sheets for other purposes are supported.
Configuring the AG for distribution databases can only be done through scripts.
Setting up distribution databases in an AG needs to be a new replication configuration. Switching an existing distribution database to an AG isn't supported. Also once a distribution database is taken out an AG, it can no longer function as a valid distribution database and should be dropped.

Configuration architecture

The following server names and settings are used in the examples in this article.

DIST1, DIST2, DIST3 are distributor servers;
PUB is publisher server;
After distribution database AG is formed, the listener name is DISTLISTENER;
DIST1 is intended to be the initial primary replica of distribution database AG.

Configure distributor, distribution database, and publisher

This example configures a new distributor and publisher and puts the distribution database in an AG.

Distributors workflow

Configure DIST1, DIST2, DIST3 as distributor with sp_adddistributor @@servername. Specify the password for distributor_admin through the @password. The @password should be identical across DIST1, DIST2, DIST3.
Create the distribution database on DIST1 with sp_adddistributiondb. The name of the distribution database is distribution. Change the recovery model of distribution database from simple to full.
Create an AG for distribution database with replicas on DIST1, DIST2, and DIST3. Preferably all the replicas are synchronous. Configure secondary replicas to be readable or allow read. At this time, the distribution databases are the AG, DIST1 is the primary replica, and DIST2 and DIST3 are secondary replicas.
Configure a listener named DISTLISTENER for the AG.
For recovery and to allow transaction log truncation, configure full and transaction log backups.

On DIST2 and DIST3, run:

EXEC sys.sp_adddistributiondb @database = 'distribution';

To add PUB as publisher on DIST1, run:
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
The value of @working_directory should be a network path independent of DIST1, DIST2, and DIST3.

On DIST2 and DIST3, if the replica is readable as a secondary, run:

EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';

If a replica isn't readable as a secondary, perform failover such that the replica becomes the primary, and run

EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';

The value of @working_directory should be the same as the previous step.

Publisher workflow

To add the distribution database AG listener as the distributor, on PUB, run:

EXEC sys.sp_adddistributor @distributor = 'DISTLISTENER', @password = '<distributor_admin password>' ;

The value of @password should be the one that was specified when distributors were configured in the distributor workflow.

Remove distributor and publisher

This example removes publisher and distributor when distribution database is in AG.

Publisher workflow

On PUB, drop all the subscriptions and publications for this publisher then call sp_dropdistributor.

Distributors workflow

In this example, DIST1 is the current primary of distribution database AG. DIST2 and DIST3 are secondary replicas.

On DIST2 and DIST3, run:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;

On DIST1, run:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB';

Delete the AG.
On DIST2 and DIST3, change the distribution database to read_write mode by restoring the database with recovery.
```
RESTORE DATABASE [distribution] WITH RECOVERY, KEEP_REPLICATION;
```

To drop distribution database and to retain the snapshot directory, run:

EXEC sys.sp_dropdistributiondb @database = 'distribution' , @former_ag_secondary = 1;

This procedure removes all the dangling jobs on this replica.

To drop distribution database on DIST1, run

EXEC sys.sp_dropdistributiondb @database = 'distribution';

If there are no other distribution databases in AG, run sp_dropdistributor on DIST1, DIST2, and DIST3.

Add a replica to distribution database AG

This example adds a new distributor to an existing replication configuration with distribution database in AG. In this example, an existing distribution database is in an AG. DIST1 and DIST2 are the distributors, distribution is the distribution database in AG, and PUB is the publisher. Add DIST3 as a replica in the AG.

Distributors workflow

DIST3 should be configured as distributor through sp_adddistributor @@servername. The password for distributor_admin should be specified through @password parameter. The password should be the same as what was specified for DIST1 and DIST2.
Add DIST3 to the AG for current distribution database.

On DIST3, run:

EXEC sys.sp_adddistributiondb @database = 'distribution';

On DIST3, if the replica is readable as a secondary, run:
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
If the replica isn't readable as a secondary, perform failover such that the replica becomes the primary, and run:
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
The value of @working_directory should be the same as what was specified for DIST1 and DIST2.
On DIST3, you must recreate Linked Servers to the subscribers.

Remove a replica from distribution database AG

This example removes a distributor from a current distribution database AG while the rest of the replicas in distribution database AG are not affected. In this example, a distribution database is in AG. DIST1, DIST2, and DIST3 are the distributors, distribution is the distribution database in AG, and PUB is the publisher. Remove DIST3 from the AG.

Distributors workflow

Make sure DIST3 is a secondary for the distribution database AG.
Remove DIST3 from the distribution database AG.
On DIST3, change the distribution database to read_write mode by restoring the database with recovery. For example, run the following command:
```
RESTORE DATABASE distribution WITH RECOVERY, KEEP_REPLICATION;
```

To remove all the orphaned jobs on DIST3 run:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;

On DIST3, run:

EXEC sys.sp_dropdistributiondb @database = 'distribution', @former_ag_secondary = 1;

On DIST3, run:
```
EXEC sys.sp_dropdistributor;
```

Remove a publisher from distribution database AG

This example removes a publisher from a distributor's current distribution database AG while the rest of the publishers served by this distribution database AG are not affected. In this example, an existing configuration has distribution database in an AG. DIST1, DIST2, and DIST3 are the distributors, distribution is the distribution database in AG, and PUB1 and PUB2 are the publishers served by distribution database. The example removes PUB1 from these distributors.

Publisher workflow

On PUB1, drop all the subscriptions and publications for this publisher, and then call sp_dropdistributor.

Distributor workflow

DIST1 is the current primary of distribution database AG.

On DIST2 and DIST3, run:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB1',  @no_checks = 1;

On DIST1, run:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB1';

At this point, there may be orphaned jobs related to PUB1 on DIST2 or DIST3. Whenever a failover occurs to DIST2 and DIST3, orphaned jobs related to all the publications of PUB1 will be removed by the Monitor and sync replication agent jobs job.

Add subscription

This example is about properly configuring subscriber information among distributors. The example adds a subscriber. DIST1 is the current primary replica of distribution database in the AG, DIST2 and DIST3 are secondary replicas of distribution database in AG. The subscriber name is SUB.

Publisher workflow

On PUB, add subscription as you would normally do to subscriber SUB.

Distributor workflow

On DIST2 and DIST3, add a linked server for 'SUB' if it isn't previously registered with DIST2 or DIST3. Below is a sample TSQL for linked server creation -

EXEC master.dbo.sp_addlinkedserver@server =N'SUB', @srvproduct=N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N'SUB', @useself = N'True',@locallogin = NULL,@rmtuser =N ULL,@rmtpassword = NULL;

Add a pull subscription

Subscriber workflow

To add a pull subscription for a publication with the distribution database in an AG, use the AG listener name in the @distributor parameter of sp_addpullsubscription_agent.

Sample T-SQL Create distribution DB in AG

The following script enables a distribution database in an availability group.

--- WorkFlow to Enable Distribution Database In AG.

-- SECTION 1 ---- CONFIGURE THE DISTRIBUTOR SERVERS

-- Step1 - Configure the Distribution DB nodes (AG Replicas) to act as a distributor
:Connect SQLNode1
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO
:Connect SQLNode2
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO

-- Step2 - Configure the Distribution Database
:Connect SQLNode1
USE [master];
EXEC [sys].[sp_adddistributiondb]
    @database = 'DistributionDB',
    @security_mode = 1;
GO
ALTER DATABASE [DistributionDB] SET RECOVERY FULL;
GO
BACKUP DATABASE [DistributionDB] TO DISK = 'NUL';
GO
-- Step 3 - Create AG for the Distribution DB.
:Connect SQLNode1
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode2
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode1
-- Create the Availability Group
CREATE AVAILABILITY GROUP [DistributionDB_AG]
FOR DATABASE [DistributionDB]
REPLICA ON
N'SQLNode1' WITH (ENDPOINT_URL = N'TCP://SQLNode1.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC),
N'SQLNode2' WITH (ENDPOINT_URL = N'TCP://SQLNode2.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC);
GO


:Connect SQLNode2
ALTER AVAILABILITY GROUP [DistributionDB_AG] JOIN;
GO  
ALTER AVAILABILITY GROUP [DistributionDB_AG] GRANT CREATE ANY DATABASE;
GO

--STEP4 - Create the Listener for the Availability Group. This is very important.
:Connect SQLNode1

USE [master]
GO
ALTER AVAILABILITY GROUP [DistributionDB_AG]
ADD LISTENER N'DistributionDBList' (
WITH IP
((N'10.0.0.8', N'255.255.255.0')) , PORT=1500);
GO

-- STEP 5 - Enable SQLNode2 also as a Distributor
:Connect SQLNode2
EXEC sys.sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;
GO

--STEP 6 - On all Distributor Nodes Configure the Publisher Details 
:Connect SQLNode1
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO
:Connect SQLNode2
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO

-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER
:Connect SQLNode4
EXEC sys.sp_adddistributor @distributor = 'DistributionDBList', -- Listener for the Distribution DB.	
	@password = 'Pass@word1';
GO

-- SECTION 3 ---- CONFIGURE THE SUBSCRIBERS 
-- On Publisher, create the publication as one would normally do.
-- On the Secondary replicas of the Distribution DB, add the Subscriber as a linked server.
:Connect SQLNode2
EXEC master.dbo.sp_addlinkedserver @server = N'SQLNODE5', @srvproduct = N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLNODE5', @useself = N'True',
	@locallogin = NULL,@rmtuser = NULL,@rmtpassword = NULL;

Share via

Set up replication distribution database in Always On availability group

Supported scenarios

Limitations or exclusions

Configuration architecture

Configure distributor, distribution database, and publisher

Distributors workflow

Publisher workflow

Remove distributor and publisher

Publisher workflow

Distributors workflow

Add a replica to distribution database AG

Distributors workflow

Remove a replica from distribution database AG

Distributors workflow

Remove a publisher from distribution database AG

Publisher workflow

Distributor workflow

Add subscription

Publisher workflow

Distributor workflow

Add a pull subscription

Subscriber workflow

Sample T-SQL Create distribution DB in AG

Feedback

Additional resources

Share via

Set up replication distribution database in Always On availability group

Supported scenarios

Limitations or exclusions

Configuration architecture

Configure distributor, distribution database, and publisher

Distributors workflow

Publisher workflow

Remove distributor and publisher

Publisher workflow

Distributors workflow

Add a replica to distribution database AG

Distributors workflow

Remove a replica from distribution database AG

Distributors workflow

Remove a publisher from distribution database AG

Publisher workflow

Distributor workflow

Add subscription

Publisher workflow

Distributor workflow

Add a pull subscription

Subscriber workflow

Sample T-SQL Create distribution DB in AG

Related content

Feedback

Additional resources