SQL replication with a Publisher Database in Always On Availability Groups

In this article, we will review how to setup SQL replication with publisher database in Always On availability groups so that replication continuous to sync even after failover to the secondary server in the availability group.

The following are the steps involved in setting up SQL Server replication with publisher database in Always on Availability Groups.

Configuring the remote distribution
Configure publishers to use the remote distribution
Add the publisher database to the Always on Availability Group
Configuring the SQL Server replication
Creating the linked servers to subscribers on the secondary servers
Redirect the original publisher to the Always on Availability Group Listener

Let’s go over these steps one by one.

For the demo purposes, I am using the following servers to configure SQL replication.

SQL01V– Primary server
SQL02V– Secondary server
SQL03V – Remote distribution server
SQL03V – Subscriber

Configuring the remote distribution

We need to configure the distribution database on a remote server and the remote distribution server must be available for both the nodes in the Always on availability group. For demo purposes, I used mssql3 for both distribution servers and as a subscriber server.

Log in to the distribution server mssql3 using SQL Server Management Studio, navigate to the Replication folder, Right-click and click on Configure Distribution. Click Next on Configure Distribution Wizard window.

Select the first option i.e. ‘SQL03V’ will act as its own Distributor; SQL Server will create a distribution database and log and click Next.

In case if your SQL Server Agent service is not set to start automatically, you will see the below window that asks you to select the SQL Server Agent startup behavior. Select “Yes, configure the SQL Server Agent service to start automatically” and click Next.

This is because the replication setup creates replication agent job’s which are run by SQL server agent that runs as per schedule or continuously to synchronize the data from the publisher to the subscriber or from the subscriber to the publisher. In case, if you select a manual option and your SQL server agent is not running, the data synchronization between publisher and subscriber stops. It is advised to select the “Yes, configure the SQL Server Agent service to start automatically” so that SQL replication runs unattended.

Enter the snapshot folder location and click Next. The location you entered will be used to store the initial snapshot generated by the snapshot agent. Use the shared path which is accessible to the subscriber in case if your agent runs on the subscriber (pull subscription).

if you want a custom name, Enter the name of the distribution database else set it to default. Enter the location of the data and log file of the distribution database and click Next.

Click Next. Select configure distribution, Click Next, and Finish.

Once the distribution database setup is complete, right-click on Replication folder and click on Distributor Propertie

Click on Publishers, give the password of your choice. This password will be used when configuring the distribution on both nodes (SQL01V and SQL02V) in the Always on Availability group.

Click on Add -> Add SQL Server Publisher. Enter SQL01V as a server name, enter the login credentials and add SQL01V as a publisher. Similarly, add SQL02V as a publisher. In this case, I have SQL01V and SQL02V as possible failover nodes. If you have more than two nodes participating in Always on Availability Group, Add all the nodes as publishers at the distribution server.

Configure publishers to use the remote distribution

Make sure SQL replication is installed on all the replica nodes in the Always on Availability Group. Executing the following T-SQL script on each replica node. It should return 1.

USE master;

DECLARE @installed int;

EXEC @installed = sys.sp_MS_replication_installed;

SELECT @installed;

Now we need to configure the publishers SQL01V and SQL02V to use remote distribution server SQL03V which was set up above.

Log in to the primary node SQL01V using SQL Server Management Studio. Navigate to the Replication folder. Right-click on the Replication folder and click on Configure Distribution. Click Next. select “Use the following server as distributor”. Click on Add and add the remote distribution server SQL03V which was configured above. Click Next.

Enter the administrative password you set above. Click Next -> Enable Configure Distribution -> Click Next and Finish.

Similarly, configure the secondary node SQL02V to use the same remote distribution server. If you have more nodes in the Availability group, configure all the possible failover nodes to use the same remote distribution server.

Add the publisher database to the availability group

After configuring the publishers to use the remote distribution server, we need to add the database to the availability group on which we are going to configure the SQL Server replication. i.e. Publisher database.

This part can also be done after configuring the SQL replication. Please refer to the below links to configure Always on Availability group and add the database to the availability group.

Configuring the SQL Server replication

Log in to primary node SQL01V using SQL Server Management Studio, Navigate to the Replication folder and then Local Publications. Right-click and click on New Publication.

Click Next and select the database that you want to replicate. Click Next and select the type of replication.

Click Next. Select 2008 or later and click Next.

Select the tables you want to replicate and click Next.

Enable Create Snapshot Immediately and Click Next.

Configure Agent Security and click Next. Enable Create the Publication. Click Next, Enter the name of the publication in SQL Replication and click Finish. Once you click on Finish, the publication is created, and the snapshot agent is started which generates the snapshot.

Once the snapshot is generated add the subscriber and apply the initial snapshot.

To add the subscriber, log in to the publisher SQL01V and navigate to Replication -> Local Publications.

Right-click on the publication you created above and click on New Subscriptions. Click Next. Select the publication and click Next. Select the Agent location and click Next. Click on Add SQL Server subscriber and add SQL03V as a subscriber. Configure agent security and click Next. Schedule the SQL replication agent to run as per your choice. Initialize the subscription Immediately to apply the snapshot immediately after creation. Select the subscription type as Server if you want to republish the subscriber else select the subscription type as Client and create the subscriber.

Once the subscriber is created the initial snapshot is applied and all the incremental data changes are replicated to the subscriber from the publisher and vice versa.

Creating linked servers to the subscribers on the secondary servers

We need to create a linked server for SQL03V (subscriber) on SQL02V (secondary node). Repeat this for all the SQL replication subscribers on all the possible failover nodes in the Always on Availability group.

Please refer to the following sample script to add a linked server.

1 2	EXEC sp_addlinkedserver @server = 'SQL03V';

Redirect the original publisher to the Always on Availability Group Listener

Log in to the remote distribution server SQL03V using SQL Server management studio. Execute the following script on the distribution database to redirect the original publisher to Always on the listener.

USE distribution;

EXEC sp_redirect_publisher

@original_publisher = 'SQL01V',

@publisher_db = 'pub_AOAG',

@redirected_publisher = 'MyAGListenerName';

At the distribution database, Execute the following script to validate the redirection.

USE distribution;

DECLARE @redirected_publisher sysname;

EXEC sys.sp_validate_replica_hosts_as_publishers

@original_publisher = 'SQL01V',

@publisher_db = 'pub_AOAG',

@redirected_publisher = @redirected_publisher output;

Once the validation is completed, manually failover the primary node to the secondary node and test the replication agent synchronization and verify the data on the SQL replication publishers and the subscribers.

Conclusion

In this article, we explored how to create SQL replication with a publisher in Always on Availability Group. In case you have any questions, please feel free to ask in the comment section below.

Author
Recent Posts

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies