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 = 1  ---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