Sql-server – How to set up a transactional replication to an Always On Availability Group (AG) with two replicas through connecting to the AG Listener

clusteringreplicationsql servertransactional-replication

I want to replicate some tables on a SQL Server instance (Publisher) to an Always On Availability Group (AG) with two replicas (nodes) using its Availability Group Listener. The Distributor is the same as the Publisher. Some tables of a database on the Publisher is supposed to be replicated (Transactional Replication) to both replicas of the remote Always On Availability Group (AG) through connecting to the AG Listener. I successfully built a publication. I could build a subscription and set the AG listener as the Subscription server and didn't see any error but when I checked the AG listener, there was nothing in the Local Subscriptions folder. There was nothing in Local Subscriptions folders of both replicas, either. I tried to use official documents of Microsoft but it didn't resolve the problem. I searched a lot but couldn't find a proper documentation for such a situation.

Best Answer

I want to describe the whole process of setting up the replication that worked for me. I hope it is useful for you too :)

If you create a Publication using GUI of SQL Server Management Studio, you don't need to run scripts mentioned in the steps 1-5 and can quickly jump to step 6. However, you can use the following scripts, too.

  1. Introduce the Distributor. The following query is run on the Distributor that can be the same as the Publisher. The Distributor must be a SQL instance that doesn't currently belong to the Availability Group and won't be part of the Availability Group in the future.

    USE master;
GO
EXEC sys.sp_adddistributor
@distributor = 'WIN-ABCDE123F', -- IP address doesn't work
@password = 'pa$$w0rd'; -- an arbitrary password

  2. Create the distribution database at the Distributor. Run the following commands on the Distributor. The @security_mode parameter is used to determine how the publisher validation stored procedure that is run from the replication agents, connects to the SQL Listener. If set to 1 Windows authentication is used to connect to the SQL Listener. If set to 0, SQL Server authentication is used. If so, @login and @password parameters must be provided.

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

  3. Add the Publisher to the Distributor. Most of the times, the Publisher is the same as the Distributor. Run the following commands on the Distributor.

    USE master;
GO
EXEC sys.sp_adddistpublisher
@publisher = 'WIN-ABCDE123F',
@distribution_db = 'distribution',
@working_directory = '\\WIN-ABCDE123F\WorkingDir',
@security_mode = 1;

    @working_directory is a UNC path. For more information about UNC click here. Its default value is ReplData folder for the instance of SQL Server, for example C:\Program Files\Microsoft SQL Server\MSSQL\MSSQ.1\ReplData.

  4. Add the Distributor to the Publisher. If the Publisher was different from the Distributor, you must have run the following commands on the Publisher. If not, it is the same as step 1 command and there is no need to run it again.

    USE master;
GO
exec sys.sp_adddistributor
@distributor = 'WIN-ABCDE123F',
@password = 'pa$$w0rd';

  5. Create a publication on the Publisher. You can do it using GUI or some scripts.

  6. Set snapshot security account. Create a Windows user account for snapshot security agent, say repl_snapshot. Grant required access to it using SQL Server Management Studio GUI. Then open Replication\Local Publications folder. Right click on the publication and select Properties. From “Select page” panel, select Agent Security, click on Security Settings, select “Run under the following Windows account” and in "Process Account" box type: 'WIN-ABCDE123F\repl_snapshot'. Enter its password and confirm it. You can also use the Administrator account but it is not recommended regarding security issues.

  7. Run the following script on the Publisher to create a subscription and add subscription agent to the publisher.

    use [publication_db]
EXEC sp_addsubscription @publication = N'PUBLICATION_NAME',  
   @subscriber = N'SQL_Listener_Name', -- IP address doesn't work  
   @destination_db = N'db_on_subscriber',  
   @subscription_type = N'Push',  
   @sync_type = N'automatic', @article = N'all', 
   @update_mode = N'read only', @subscriber_type = 0; 
GO 

EXEC sp_addpushsubscription_agent @publication = N'PUBLICATION_NAME',  
   @subscriber = N'SQL_Listener_Name', -- IP address doesn't work 
   @subscriber_db = N'db_on_subscriber',  
   @subscriber_login='SQL_LOGIN_SQL_LISTENER',
   @subscriber_password='PASSWORD', @subscriber_security_mode = 0; 
GO

You can see the subscription has been created just on the SQL Listener, not nodes (replicas). Before running the replication, on the publisher, go to Replication/Local Publications. Select 'Validate Subscriptions...', select the subscription and confirm it. Again go to Replication/Local Publications. Click on View Snapshot Agent Status. On the dialog opened, click on Start and wait until the process finishes. If all things went well, you could start the replication. Go to Replication/Local Publications. Right click on the subscription and select View Synchronization Status. Click Start and wait until the process finishes.

Related Question