Wednesday, April 6, 2016

Transactional Replication Subscription on AlwayOn Availability Group

We had a little team meeting to discuss about the using the AAG database as subscriber for transnational replication. As soon as I came out of meeting started creating POC for this scenario. I have an existing Publisher and Distributor servers, real quick I have created a publication and tried to create the subscription (through GUI) to Always On Availability Group DB and  thought to use the Lister name as the Subscriber server while create the subscription. 

And it thrown the below error:
TITLE: Connect to Server
Cannot connect to AAG_1_Listener.
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, 'PrimaryAGNODE'. (Replication.Utilities)



I have did some research and find below Microsoft article, It has all the instructions on How to Create Transactional Subscription in an AlwaysOn Environment. The trick is " The subscription must be created by using a Transact-SQL script and cannot be created using Management Studio".

Steps to follow for creating Transactional Subscription in AlwaysOn Availability Group:

  1. Before creating the subscription, add the subscriber database to the appropriate AlwaysOn availability group.
  2. Add the subscriber's availability group Listener as a linked server to all nodes of the availability group. This step ensures that all potential failover partners are aware of and can connect to the listener. (This step not really Needed)
  3. Using the below script in the Creating a Transactional Replication Push Subscription section below, create the subscription using the name of the availability group listener of the subscriber. After a failover, the listener name will always remain valid, whereas the actual server name of the subscriber will depend on the actual node that became the new primary.
-- commands to execute at the publisher, in the publisher database:
use [publisher_database_name_Here]
EXEC sp_addsubscription @publication = N'publication_name_Here', 
       @subscriber = N'availability_group_listener_name_Here', 
       @destination_db = N'subscriber_database_name_Here', 
       @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_Here', 
       @subscriber = N'availability_group_listener_name_Here', 
       @subscriber_db = N'subscriber_database_name_Here', 
       @job_login = null, @job_password = null, @subscriber_security_mode = 1;
GO
    4. If creating a pull subscription:

  • In Management Studio, on the primary subscriber node, open the SQL Server Agent tree.
  • Identify the Pull Distribution Agent job and edit the job.
  • On the Run Agent job step, check the -Publisher and -Distributor parameters. Make sure that these parameters contain the correct direct server and instance names of the publisher and distributor server.
  • Change the -Subscriber parameter to the subscriber's availability group listener name.
If we follow above steps, we don't need to do anything even though faiover happens. 

Things That I Noticed: 

I have noticed that the subscriptions are created on all nodes where ever the Subscription DB is participated in AllwaysOn Availability Group.

The distribution agent is responsible for sending the replicated data to the Subscriber Db which is the Current Primary Replica in the AAG. Further AlwaysOn will take care of distributing data from Primary Replica to the other secondary Replicas.  



Below is the link to the original Microsoft article



Thanks, Hope it helps.

No comments:

Post a Comment