none
How to Configure Replication for Always On Availability Groups under SQL Server 2016 Standard Edition. RRS feed

  • Question

  • I would like to setting up Transactional replication on a database which is part of basic AlwaysOn Availability Group with No readable secondary

    Server 1 : AlwaysOn Availability Group Primary replica and transactional replication publisher

    Server 2 : AlwaysOn Availability Group Primary replica and transactional replication publisher

    Server 3 : replication subscriber and distributor

    I received the error message  (as below) when I EXEC sys.sp_validate_replica_hosts_as_publishers at distribution database:
    Msg 21899, Level 11, State 1, Procedure sp_hadr_verify_subscribers_at_publisher, Line 109 [Batch Start Line 2]

    The query at the redirected publisher 'Server 1' to determine whether there were sysserver entries for the subscribers of the original publisher 'Server 1' failed with error '976', error message 'Error 976, Level 14, State 1, Message: The target database, 'xxx', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.
    One or more publisher validation errors were encountered for replica host 'Server 1'.

    Due to SQL Server edition limitation, readable secondary is not able to set to read-only or intended read.

    Is it possible to Configure Replication for Always On Availability Groups in this scenario and how to setup.
    Thursday, November 23, 2017 3:18 AM

All replies

  • Hi winter007,

    Based on the error message, it looks like that you are running sys.sp_validate_replica_hosts_as_publishers on the secondary node. Please refer to this blog to configure replication on Always-on: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/30/setting-up-replication-on-a-database-that-is-part-of-an-alwayson-availability-group/

    Here is an article which described creating transactional replication on Always-on with Standard edition: https://blog.sqlauthority.com/2015/12/17/sql-server-how-to-create-a-readable-secondary-server-in-sql-server-standard-notes-from-the-field-107/

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 24, 2017 8:43 AM
    Moderator
  • Hi Teige, Thanks for your advice. We found in order to configure replication on availability group, 1 of the requirement is the secondary node must configure to readable secondary which this does not available on Basic availability group.
    We are looking replication solution for Basic availability group.
    Do you have other recommendation?
    Thursday, November 30, 2017 4:12 AM
  • Hi winter007,

    There is not a limitation that the secondary node must configure to readable secondary when the node is set as publisher. In your scenario, you only set the publisher on the Basic availability group.

    I think you may get the limitation from the description "Basic availability groups can not be part of a distributed availability group" in the document Basic Availability Groups (Always On Availability Groups), this is not related to replication.

    Besides, you can refer to the blog I shared above, https://blog.sqlauthority.com/2015/12/17/sql-server-how-to-create-a-readable-secondary-server-in-sql-server-standard-notes-from-the-field-107/, they can configure it normally.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 1, 2017 9:16 AM
    Moderator
  • <g class="gr_ gr_22 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="22" id="22">Hi</g> Teige the blog you provide a link to does not answer the question.   In the blog article, which is very short on details to my mind.  Seems to be adding a connection a database participating in a Basic Availability Group (BAG) and have the replication process use DB in the current primary node in the BAG.

    WHY IS NOT EXPLAIN OR NOTED is what happens when the primary node fails over the secondary node?

    When looking at the documentation, and in particular the documentation provided by Microsoft appears to be incorrect at least in the context of <g class="gr_ gr_23 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="23" id="23">BAG's</g>.   https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/30/setting-up-replication-on-a-database-that-is-part-of-an-alwayson-availability-group/  Requires setting up of linked servers, which are set up as part of the distribution database set.  WHICH DO NOT WORK.  Yet I am able to set up replication!

    <g class="gr_ gr_19 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="19" id="19">Next</g> the GUI for setting up the distribution database works as expected.  The script generated by the GUI, DOES NOT WORK!!!! 

    Hopefully, you get my less than subtle hints.  Getting help any help is largely dependent on the community, which Microsoft does not appear to work to support or get involved with.    That as they say is another story.

    Wednesday, August 22, 2018 12:48 PM
  • Hi Bob-

    Did you find a solution to incorporate BAG and transnational replication?  We are in similar solution we would like to setup BAG on the publisher and distributor however we like secondary readable.  We are running into the same issue.  

    Wednesday, January 9, 2019 2:49 PM
  • This error 21899 and 976 when running sp_hadr_verify_subscribers_at_publisher on a Standard Edition AG (BAG) distributor is expected behavior, and is described in the doc: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server

    You need to run this query on every AG node of the publisher, and make sure subscriber machine name is present in the result:

    SELECT * FROM sys.sysservers

    Test all linked servers on all involved servers and fix them if needed (often test fails because of missing "Data Access=True" and they have no valid sql login entered to connect with).

    Btw, here is a walkthrough video: https://www.youtube.com/watch?v=ujUuH-0fLWY
    The script used in demo is here: http://www.techbrothersit.com/2015/07/how-to-setup-replication-with-alwayson.html

    Friday, May 17, 2019 3:32 PM