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

30 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
  6. 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.
    how changes are handled

    ReplyDelete
  7. 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:

    ReplyDelete
  8. Now 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.

    ama citation
    oxford referencing tool
    mla referencing generator

    ReplyDelete
  9. 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

    ReplyDelete
  10. Our nursing assignment help services have got everything covered. Name your assignment topic and we have the right sources to craft your paper.

    ReplyDelete
  11. This 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
  12. HIRE essay typer TO WRITE AN EXCELLENT ESSAY FOR YOU? How good it will be if someone will complete your challenging Economics essay on Market Structures?

    ReplyDelete
  13. The CBD Oil Boxes are an important thing to trade cannabis oil in the market by performing it in the Cardboard or Kraft materials most maximum of the time. We are developing exceptional artwork for these CBD Packaging boxes. We at PACKAGINGPAPA.COM, recognize the significance of press examples to explain our packaging abilities. therefore free individual kits are offered to our customers which include random box samples of our past done work.

    ReplyDelete
  14. This post is exceptionally written. I can imagine how much hard work you have put into this so, congratulations. If you are looking for an exceptionally developed Marketing Dissertation Help, you should certainly get in touch with the experienced and skilled experts of the LiveWebTutors platform right away.

    ReplyDelete
  15. Are you searching for Assignment Help online in Canada? Then you are in the right spot. Our team of professional writers is highly efficient in helping the students with their assignments. Visit our website to know more about our services.

    ReplyDelete
  16. Great post! I have definitely noted some points from it. It feels crazy how a well-composed assignment can have a strong impact on the grades. To help you achieve the academic goals, the Livewebtutors platform has curated an exclusive Dissertation Help Australia facility to help students enjoy their academic journey.

    ReplyDelete
  17. GetMyob perddisco help from myperdiscohelp.com at a very low, cheap and reasonable price.
    We are a team of PHD professionals and experts. We offer solutions and answer sheets to any assignments or question papers in any subject.

    ReplyDelete

  18. Online Assignment Helper

    Best assignment expert is the foremost Online Assignment Helper that makes assignments super easy for students. Get your assignment done at a very low and affordable price.

    ReplyDelete
  19. Each time students are given new question sets to practice and clear their concept. Our, devoted authority is functioning 24x7 to yield the greatest assistance to you.Perdisco worksheet We also contribute framed solutions with step-by-step explanations to the students at minimum rates.

    ReplyDelete
  20. Dark Dale is a professional blogger, full-time mother of two, and a part-time baker. He has years of experience in blogging and loves to blog about a wide range of topics, from educational to travel and lifestyle. Dale is also associated with onlineassignmenthelpaustralia.com, where he helps students with assignments on request.
    Assignment Help Melbourne
    Malaysia Assignment Helper

    ReplyDelete
  21. Help With an Assignment

    Do you need help with an assignment? It’s all good, the best assignment expert is now serving the school and college students Help With an Assignment. Our PhD. experts promise plagiarism-free, high-quality assignment help on +100 subjects.

    ReplyDelete
  22. Get Affordable academic writing services australia . Our Professional writers provide 24/7 Help with all your academic needs.

    ReplyDelete
  23. Assignments are the tasks provided to Assignment Help Surrey students to check the student’s ability to write on a particular subject. It keeps the student updated to the modern issues faced by the country as well as issues faced in the past. It acts as a homework to make the students gain some knowledge on the following subject. Assignment Help Vancouver But students hardly get time to do such long assignments as they are involved in many activities nowadays. Some are playing, some are participating in sports, some are spending little time with their family or some are doing part time jobs. With all these activities, students hardly finds time to make research on the subject or write so long Assignment Help Toronto pages.

    ReplyDelete
  24. Looking for first-class taxation law assignment help? Hire PhD Qualified Writers for guaranteed distinction grade

    ReplyDelete
  25. Assignment Studio offers best higher business management assignment help online with free revisions and plagiarism free content.

    ReplyDelete
  26. Fantasy Power 11! Thank you for sharing valuable information. Nice post. I enjoyed visiting this post… Play Carrom Game Online provides the best opportunity for earning money.

    ReplyDelete
  27. what will happen if one of my possible publisher(one of replica in Publisher AG) got corrupted and added back to Publisher AG after re-building the corrupted node ?

    If we failover the Publisher AG to the rebuilt node/replica, will replication work?

    ReplyDelete
  28. Writing academic documents is a task most students prefer to leave up to the subject matter experts. For these are time-consuming and requires a lot of research. Students are also given a lot of time to find Online Essay Help malaysia We can help them with paper help too. This is the main reason why most students search for my assignment help over the internet and choose only the most proficient and trusted academic writing experts.

    ReplyDelete
  29. Gotoassignmenthelp is team of leading professional writers for Assignment help Services Writing Services to students all around the world. Contact us today for Best Management assignment help at very affordable price. Under GotoAssignmentHelp we provide many types of help to the students. We are the most reliable assignment helpers. We have gained our specialization after spending most of our times in making ourselves perfect. We providing 100% plagiarism free Assignment Help. At the point when you pick our Java assignment help assistance, our faculty will consider the quality and your evaluation criteria.

    ReplyDelete