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

31 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
    Replies
    1. Hi Rajesh, If you were able to find solution in case of primary node is down, could you please share it?

      Delete
  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. Our nursing assignment help services have got everything covered. Name your assignment topic and we have the right sources to craft your paper.

    ReplyDelete
  10. 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
  11. 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
  12. This comment has been removed by the author.

    ReplyDelete

  13. 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
  14. 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
  15. 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
  16. 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
  17. Looking for first-class taxation law assignment help? Hire PhD Qualified Writers for guaranteed distinction grade

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

    ReplyDelete
  19. 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
  20. I really enjoy this, as a matter of fact; this blog offers one of the best information, Just keep it on. noun cut off mark for accounting

    ReplyDelete
  21. Acadecraft is the trendsetter for worldwide business leaders in the learning domain. The no.1 educational content developer upbeat the course curriculum with advanced learning solutions. Here, clients receive optimal online tutoring, video solutions, flashcards, and other customized eLearning solutions. Acadecraft delivers multilingual educational solutions 24/7
    copy editing services
    type setting services

    ReplyDelete
  22. This a very informative post thanks for sharing the useful information. coeikwo cut off mark for french

    ReplyDelete
  23. I am pleased to read your post on high-availability and disaster-recovery solutions. One thing that is bothering me is that can we change its state from a read-only workload to another one? Vital Technologies

    ReplyDelete
  24. At Acadecraft, clients can avail the best mobile learning services. Adhering to the requirements, the company develops customized mobile learning solutions that engage the learners.
    For free quotes and samples, reach out to the company at info@acadecraft.com.
    Also Read: Online Subject Matter Experts

    ReplyDelete
  25. According to Section 508, all federal agencies across the US need to make their digital products and services accessible to people with disabilities. Organizations may face legal issues when they fail to meet 508 remediation criteria. We have a team of experienced accessibility experts providing specialized services, including compliance audits, automated testing, remediation, and issue redressals.
    Best Language learning Solutions

    ReplyDelete
  26. Thank you so much for writing this detailed post. Your blog helped me develop a query that I used to find information about scholarships that cover full tuition fees. Most of them cover flight, accommodation, and other fees, including out-of-pocket student maintenance expenses. Buy Essay Online

    ReplyDelete
  27. While setting up Replication with AlwaysOn Availability Group in SQL Server may seem complex, this DBA tutorial, "How to setup Replication with AlwaysOn Availability Group in SQL Server Part 1," breaks it down step by step. If you're struggling with SQL tasks, consider seeking assistance, perhaps by searching for a do my excel assignment service to ease your workload and ensure a successful implementation of this critical database feature.

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

    ReplyDelete
  29. I've been exploring setting up Replication with Always On Availability Group in SQL Server for my projects, and this tutorial was a game-changer. It's a thorough guide that simplifies the process step by step, making it accessible for anyone diving into this complex area. The clarity in explanation and detailed walkthroughs are invaluable for someone like me handling databases. Looking forward to Part 2! Kudos to the team behind it for providing such essential insights for Trademark Registration in Pakistan.

    ReplyDelete