How to setup Replication with AlwaysOn Availability Group in SQL Server Part1 - DBA Tutorial

In this  Part 1 video you will learn following:

1- How to setup replication with AlwaysOn Availability Group in SQL Server
2- How to configure Remote Distributor in SQL Server
3- How to enable Replication on all Replica in AlwaysOn Availability Group
4- How to add possible publisher at remote Distributor in AlwaysOn Availability Group
5- How to Validate replication
6- Resolution of below Error:
Msg 21892, Level 16, State 1, Procedure sp_hadr_validate_replica_hosts_as_publishers, Line 60
Unable to query sys.availability replicas at availability group primary associated with virtual network name '' for server names of the member replicas:error = 18456, Level 14, State 1, Message: Login Failed for user 'NT AUTHORITY\ANONYMOUS' ..'

Step by Step Document used in the video

Step1:


Configure Distributor on remote Server which is not part of AG Replicas ( primary or Secondary)

USE master;
GO
EXEC sys.sp_adddistributor
    @distributor = 'CLT01\SQLDistributor',
    @password = 'Pa$$w0rd';

Step2:


-- Create Distributor database

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

Step3:


-- Add Publisher (Primary Replica) to the distributor, Run on Distributor below script

USE master;
GO
EXEC sys.sp_adddistpublisher
    @publisher = 'TBSSQLCLUSTER\SQLPROD',
    @distribution_db = 'distribution',
    @working_directory = '\\CLT01\AG_Replication_Share',
       @security_mode =---This shows windows Authentication, you can use sql Auth using @login and @password Parameter

Step 4:


-- Add Remote distributor to the Publisher (primary Replica)

exec sys.sp_adddistributor
    @distributor = 'CLT01\SQLDistributor',
    @password = 'Pa$$w0rd'

Step 5:


-- Create Publication and subsciption

Step 6:


-- Make sure replication is enabled on all secondary Replicas

USE master;
GO
DECLARE @installed int;
EXEC @installed = sys.sp_MS_replication_installed;
SELECT @installed;

--Results=1

Step 7:


-- Add all secondary replica's as possible publisher incase of failover of AG, Run below
script on Distributor

EXEC sys.sp_adddistpublisher
    @publisher = 'NYSQLCLUSTER\NYSQLPROD',
    @distribution_db = 'distribution',
    @working_directory = '\\CLT01\AG_Replication_Share'

Step 8:


-- Add your Remote distributor to all Secondary Replica using below script.

EXEC sp_adddistributor
    @distributor = 'CLT01\SQLDistributor',
    @password = 'Pa$$w0rd'

Step 9:


-- Add Subscriber as a Link server on all Secondary servers, Make sure subscriber is in every publisher including in Distributor (Primary, All Secondary and Distributor)

Important:


--Use GUI with SQL Server Login to add Linked server, make sure connection succeeded

Step 10:


--Redirect Orignal Publisher to AG (Run below command on distributor)

USE distribution;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = 'TBSSQLCluster\SQLPROD',
    @publisher_db = 'SalesOrder_New',
    @redirected_publisher = 'SQLPROD_List';

Step 11:


--Run the Validation Store procedure at the Distributor and make sure you don't get any error

Validation Error:


--Msg 21892, Level 16, State 1, Procedure sp_hadr_validate_replica_hosts_as_publishers, Line 60
--Unable to query sys.availability_replicas at the availability group primary associated with virtual network name 'SQLPROD_List' for the server names of the member replicas: error = 18456, error message = Error 18456, Level 14, State 1, Message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'..',

Resolution:


--Easy fix is to run the validation directly using SSMS on your distributor - Remote to Distributor and run validation command, should run successfully.

USE distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
    @original_publisher = 'TBSSQLCLUSTER\SQLPROD',
    @publisher_db = 'SalesOrder_New',
    @redirected_publisher = @redirected_publisher output;












Setup Replication with AlwaysOn Availability Group in SQL Server - DBA Tutorial

5 comments:

  1. I am using remote distributor and have configured the replication by redirecting it to the listener name, now i have changed the listener name how should i change the redirected_publisher again ?

    ReplyDelete
  2. I can't thank you enough for this post. Just wanted to add one comment on Step 10. In my case I have encountered an error because I didn't provide the port number (I am not using the default port) in the redirect publisher script . Once I have provided the port number the error bypassed.
    I was scratching my head to configure this using Microsoft document where they haven't mentioned the linked servers, but you mentioned it and that helped me a lot. Thanks again :)

    ReplyDelete
  3. Hi. First of all thank you for this very useful guide that help me a lot. I will share a few hints from my experience. On distributor, if your SQL Server Service Account has only permissions on DATA folder you may need to give him permissions to ROOT level of that folder. This will help you if you receive an error when creating distributor complaining that it can't find/see path (your database path). Also, I needed to give ALTER ANY LOGIN and ALTER ANY LINKED SERVER to the user that I use for the replication. This fix error with creating snapshot (user don't have permission to create linked server..). Make sure that the user is db_owner on all the publish, subscribe and distributor DBs. If you are creating PULL replication instead of PUSH (like in this guide) make sure that you use share as your snapshot location and make sure that both SQL Server Service Account on the distributor and user that you use in the replication have at least CHANGE access to the share.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This is great article,Thanks for the procedure to setup replication with AGs! Replication worked for me as well after the primary replica fails over to secondary server. However, once the original primary server was taken offline(shutdown), replication stopped working completely. The log reader did not even pick up transactions from the secondary replica(which becomes primary when the original primary goes down) publisher.
    May i know changes required to be performed when primary server is taken offline.

    ReplyDelete