Need to setup PULL subscription in a 2-node SQL 2016 Enterprise AlwaysOn Environment

  • These instructions did not work for me: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replication-subscribers-and-always-on-availability-groups-sql-server

    Deployment included:  Using an existing AG w/ 12 existing DB's (well tested; and fails back and forth w/ NO issues) I:

    1) Added new 13th "MyDatabase" to the AG

    2) Add the subscriber's AG Listener as a linked server to both nodes og the AG; Linked server defs working properly on both nodes

    3) SKIPPED this step in instructions as this is for PUSH subs; I am defining a PULL sub

    4) Using SSMS, I created the SUB; it dynamically created the SUB on both nodes of the AG

    - then In SSMS (2017 17.4) on the primary subscriber node, I opened the SQL Server Agent tree

    - Identified the Pull Distribution Agent job and edited the job

    - On the Run Agent job step, checked the -Publisher and -Distributor parameters. Made sure that these parameters contained the correct direct server and instance names of the publisher and distributor server.

    - then I changed the -Subscriber parameter to the subscriber's AG listener name

    - As mentioned the SUB was successfully created on BOTH nodes of the AG (Primary and seconday replica)
    - Only 1 REPL job was created - on the Primary AG node (I edited and chg'd -Subscriber parameter to the subscriber's AG listener name)
    - No REPL job was created on the Secondary AG node
    - When I failed the AG over to NODE 2, Replication for this PUBLICATION failed
    - I manually scripted the REPL job on NODE 1 and created on NODE 2; no luck

    Where did I go wrong here?

    BT
  • Thank you for the link.  Unfortunately, that link contains all the info I used during my setup.  All works fine and dandy UNTIL you failover the AoAG to the other node.. Then the PUB fails as there is no job defined on the secondary replica.  And, when I attempt to create the job on the secondary, it fails.  Other thoughts?

    BT
  • Express12 - Wednesday, February 14, 2018 2:59 PM

    Thank you for the link.  Unfortunately, that link contains all the info I used during my setup.  All works fine and dandy UNTIL you failover the AoAG to the other node.. Then the PUB fails as there is no job defined on the secondary replica.  And, when I attempt to create the job on the secondary, it fails.  Other thoughts?

    Both articles say to create the subscriptions using tsql. In your first post, you indicated you did it through SSMS.

    Other than that, What are all the error messages? Not sure what you mean by PUB fails. What is the error when attempting to create the job on the secondary?
    And where is the distributor? In the distribution database, check the MSrepl_errors table as well as the MSsubscriptions table.

    Sue

  • re-created the error using these exact steps:

    - removed the subscription from both SQL nodes of the AoAG -- completely (fresh start)
    - confirmed my subscriber database is in my AG and functioning properly
    - confirmed my subscriber AG Listener name is defined on both nodes as a linked server and functioning properly (all tables are viewable via the LS on both nodes)

    - in SSMS, used GUI to prep new SUB on node 1 of AG but DID NOT execute creation of SUB via the GUI; I only GEN'd the script
    ---- what was interesting: though I did not gen the SUB, the SUB showed in Repl Monitor under the source SQL Server's PUB list of SUBS ???

    - To remove the SUB on the source SQL Server, I expanded the PUB and I then DELETED the SUB -- it prompted me to connect to the target LISTENER SQL server to DELETE the sub and of course failed attempting to do so because the SUB did not yet exist on the target LISTENER; it did however delete the SUB defined on the SOURCE SQL Server under the PUB list

    - then after modifying the create SUB script to reference the @subscriber = N'LISTENER name' (instead of physical node 1) I connected to my source SQL Server and successfully executed the create SUB script

    - I then connected to my target node 1 and executed the 2nd script to create the PULL SUB; this created the SUB on both NODE 1 and NODE 2 of the taget AG servers; create just 1 REPL-Distributionjob on NODE 1 but the REPL=Distribution job on NODE 1 is erroring w/ msg:

    2018-02-15 12:41:11.066 Agent message code 21056. The subscription to publication 'MyPub_name' has expired or does not exist.

    (remember, NO Repl-Dist job was created on NODE 2 of the target AG)

    ---- I resolved the above job error by editing the REPL-Distribution job on target node 1 and changed the -Subcriber name from the physical server name to the LISTENER name

    - I then connected to my target node 2 and attempted to run the 1st portion of my script to create the SUB (exec sp_addpullsubscription ...) and it failed w/ error:

    Msg 3906, Level 16, State 2, Procedure sp_addpullsubscription, Line 187 [Batch Start Line 8]
    Failed to update database "my_database" because the database is read-only.

    - I then successfully failed the AG over to target node 2

    - then and attempted to run the 1st portion of my script to create the SUB (exec sp_addpullsubscription ...) and it failed w/ error:

    Msg 14058, Level 16, State 1, Procedure sp_addpullsubscription, Line 220 [Batch Start Line 8]
    Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.

    - I then attempted to run the 2nd portion of the script on Node 2, exec sp_addpullsubscription_agent, and it failed w/ error msg:

    Msg 21002, Level 11, State 1, Procedure sp_addpullsubscription_agent, Line 250 [Batch Start Line 12]
    The Distribution Agent for this subscription already exists (my_sub_dist_agent_entry_name).

    - I then attempted to DELETE the sub on target node 2; I did not CONNECT to the PUBLISHER to delete the record there; this resulted in the SUB being deleted on both the target NODE 1 and target node 2 servers AND leaving the source PUB Repl-monitor showing the PUB in a failed state

    - I then connected to the source Server and deleted the SUB on there to clean up the error in REPL monitor

    BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply