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:
Step2:
Step3:
Step 4:
Step 5:
Step 6:
Step 7:
Step 8:
Step 9:
Important:
Step 10:
Step 11:
Validation Error:
Resolution:
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
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 ?
ReplyDeleteI 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.
ReplyDeleteI 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 :)
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.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis 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.
ReplyDeleteMay i know changes required to be performed when primary server is taken offline.
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.
ReplyDeletehow changes are handled
There are certain guarantees that make us the pioneer in custom dissertation writing services the UK. When you decide to buy custom dissertation online from us, you are eligible to get the following guarantees:
ReplyDeleteNow in case you are stuck with such complicacies and looking for the best administrative law essay help online, then look nowhere else and choose MyAssignmenthelp for some brilliant outcomes and assignment solutions par excellence.
ReplyDeleteama citation
oxford referencing tool
mla referencing generator
I am really impressed with the judgment you presented above. The astounding assignment help service by the IdealAssignmentHelp platform allows students to stay at the top of their class and secure a better future for themselves in today's competitive era. This is an ideal platform to go to if you are in urgent need of a professional academic writer. Also Visit: My Assignment Help
ReplyDeleteOur nursing assignment help services have got everything covered. Name your assignment topic and we have the right sources to craft your paper.
ReplyDeleteThis is whereby the evaluator is reluctant to give a realistic appraisal because he knows it may hurt the future of the employee concerning matters such as promotion. content writing services
ReplyDelete