Replication can be a daunting technology to work with at times. Combine that with Always on Availability Groups (AAGs) and you may find yourself in a head scratcher of a situation. When deploying replication on a standalone server, it is not uncommon to see that same server act as a local distributor, meaning the distribution database is located on the same server as the publications. However, when you need to set up replication within an AAG, there are a few extra steps that need to be taken to ensure replication can function in the event of a failover.

Why Does Replication Break When I Failover?

So, when setting up replication in an AAG, you need to have a remote distributor. I mentioned local distributors above which refer to the distribution database being on the same server as the publication databases. A remote distributor is where the distribution database lives on another server separate from the published databases. This serves as a constant for replication so that in the event of a failover, replication can persist. You may have noticed issues in your own environment where if you have replication set up within an AAG, and the distribution database is located on the primary node, in the event of a failover, replication ceases to work.

So, How do I Make It Work?

I’m assuming from this point on you have knowledge of AAGs and replication. If you need more fundamental knowledge, I suggest hitting up Microsoft Learn and checking out some resources there. Here are the steps I take to set up replication within an AAG.

Replication Backward Compatibility Rules

1. A distributor can be any version as long as it is greater than or equal to the publisher version.
2. A publisher can be any version as long as it is less than or equal to the distributor version.
3. A subscriber’s version depends on the type of replication:
   • A subscriber to snapshot publication can be any version within two versions of the publisher version.
   • A subscriber to transactional publication can be any version within two versions of the publisher version.
   • A subscriber to merge publication can be any version equal to or lower than the publisher version.

Step 1: Configure a distributor on a remote server that is not apart of the AAG

In this step, you’ll need an instance of SQL Server to host your remote distributor. This should be a server that is not one of the nodes in the availability group. Once you have your server ready, run the following:

USE master
GO
EXEC sys.sp_adddistributor @distributor = 'Server_Distributor_Name', @password = 'password'

You’ll replace ‘Server_Distributor_Name’ with the name of the server the distribution database will live on, and replace ‘password’ with the password that will be used between the publisher server and the distributor.

Step 2: Create the distribution database

The next step is to create the distribution database. This should be run on the same server you are using as your distributor.

USE master
GO
EXEC sys.sp_adddistributiondb @database = 'distribution', @security_mode = 1

You do not need to make any changes here and can run as is.

Step 3: Add the publisher (primary replica) to the distributor

Here, we want to add our first node as a publisher to our distributor. Run the following on the distribution server:

USE master
GO
EXEC sys.sp_adddistpublisher @publisher = 'Server_Publisher_Name', @distribution_db = 'distribution', @working_directory = 'Network_Share_Path', @security_mode = 1 --This shows windows Authentication, you can use SQL Auth using @login and @password parameters

You’ll want to replace ‘Server_Publisher_Name’ with the name of the primary node in your AAG. Next, replace ‘Network_Share_Path’ with the name of the working directory used to store data and schema files for the publication. This should be a UNC path where all involved accounts have access to.

Step 4: Add the distributor to the publisher (primary replica)

Next step is to configure the publisher server to use our remote distributor for distribution. Run the below on the primary replica in your AAG:

USE master
GO
EXEC sys.sp_adddistributor @distributor = 'Server_Distributor_Name' @password = 'password'

Replace ‘Server_Distributor_Name’ with the remote distributor server name and ‘password’ with the password you used in step 1.

Step 5: Create your publication(s) and subscription(s) on the primary replica

Either use the GUI or T-SQL to create your publication(s) and subscription(s) on the primary replica of your AAG as if you were creating them on a standalone SQL Server.

Step 6: Make sure replication is installed on all secondary replicas

This should really be step 0, but please make sure the replication feature is installed on all SQL Servers. You can quickly check this by running the following on all involved secondary nodes in the AAG:

USE master
GO
DECLARE @installed int
EXEC @installed = sys.sp_MS_replication_installed
SELECT @installed --The result should be 1 if replication is installed.

Step 7: Add all secondary replicas as a possible publisher

Next, we need to add all of our secondary replicas in the AAG in the case of a failover, the distribution server recognizes them as a publisher and replication can continue. Run the following on the remote distribution server:

EXEC sys.sp_adddistpublisher @publisher = 'Server_Secondary_Publisher_Name', @distribution_db = 'distribution', @working_directory = 'Network_Share_Path'

Replace ‘Server_Secondary_Publisher_Name’ with the name of the secondary server name. Then, replace ‘Network_Share_Path’ with the name of the working directory used to store data and schema files for the publication. This should be a UNC path where all involved accounts have access to. Repeat this for all secondary nodes in the AAG.

Step 8: Add the remote distributor to all of the secondary replicas

Just like we did for the primary node in step 4, we will want to do a similar process for all secondary nodes in the AAG. On each secondary node in the AAG, run the following:

EXEC sp_adddistributor @distributor = 'Server_Distributor_Name', @password = 'password'

You can replace ‘Server_Distributor_Name’ with the name of the remote distribution server, and ‘password’ with the password used in step 1.

Step 9: Add your subscriber server(s) as a linked server on all secondary nodes

Use GUI to create your linked server(s) with a SQL Server login that has sysadmin to add linked servers. Make sure the connection test succeeds. Using a SQL Server login makes it easier to make sure that connections will succeed.

Step 10: Redirect original publisher to AG listener

Almost done! Next, we redirect our original publisher to utilize the AG listener instead of the primary node name. Run the following on the remote distributor:

USE distribution
GO
EXEC sys.sp_redirect_publisher @original_publisher = 'Server_Publisher_Name', @publisher_db = 'Published_Database_Name', @redirect_publisher = 'Listener_Name'

Replace ‘Server_Publisher_Name’ with the primary node name in the AAG. Replace ‘Published_Database_Name’ with the name of the published database on your primary server. Lastly, replace ‘Listener_Name’ with the listener that your AAG uses. This should be repeated for each published database.

Conclusion

This one was a doozy but can be accomplished in roughly 10 steps. If implemented correctly, you should now be able to failover from one node to the other in your AAG and replication should be able to persist. As always, please test this in non-production environments before you deploy this to a production setup.