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