SQL Server 2022 RTM-CU13 KB5036432 sql replication do not work on availability database of always on availability group

Bao Viet NGUYEN 0 Reputation points
2024-10-27T17:12:54.4833333+00:00

Hi Sir/Madam,

My name is Bao Viet from Vietnam.

Current im involving in a project and in that project we decide to use SQL Replication to synchronize a table to a remote SQL Server.

On customer site, i have a cluster with 2 node, which were setup as always on availability group, a database (QLXM) was use for availability database (which currently have 'Synchronized' status). i configure node 1 as distributor. Then configure transaction publication on node 1 from the QLXM database (the owner node of cluster still node 1).

After that im checking the snapshot agent status and keep getting the status error 'Could not retrieve agent status. (Could not find stored procedure ''. (Framework Microsoft SqlClient Data Provider))'

i continue to add redirect_publisher for the node1 to AAGListener, and then execute sp_get_redirected_publisher i got weird following error message: The SQL Server instance 'node1' is not a replication publisher. Run sp_adddistributor on SQL Server instance 'node1' with distributor 'node1' in order to enable the instance to host the publishing database 'QLXM'. Make certain to specify the same login and password as that used for the original publisher.

Then i created another TestDB Database on node1 (which i do not add to availability database so that dont have 'synchronized' status). i created another publication on this TestDB, and then im checking the Snapshot Agent Status of newly created publication and got the success message : [100%] A snapshot of 1 article(s) was generated.

Next step I add TestDB to availability Database. check snapshot agent status of publication on TestDB and i got the error message just like QLXM Database. I remove TestDB out of Availability Database and error gone. So now can can completely sure the issues happen with availability database!

Please check and update me as soon as possible! i been stuck here for whole week and can not find solution to go on!

PS: i did created another environment on a virtual machine with SQL Server 2019 to verify whether i can do publication on a Aailability database and everything work well!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,060 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,661 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Bao Viet NGUYEN 0 Reputation points
    2024-10-30T02:11:19.35+00:00

    I just have discussion with customer and it turns out they setup Contained Always On Groups which according to below link currently not support replication:

    https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/contained-availability-groups-overview?view=sql-server-ver16

    So for now, i only have one concern left: do you have any plan to bring/support Replication on Contained Always On Groups?


  2. Ben Miller-(DBADuck) 190 Reputation points MVP
    2024-10-30T02:24:38.41+00:00

    Replication on an AG is a little tricky sometimes. There could be a few things wrong.

    Does the secondary have Replication feature installed? You can run into your problem if it is not.

    Also if publishing is not enabled on that secondary then that also can show that problem.

    This is your definitive guide for Replication on an AG. The key parts are the enabling of Replication on both nodes as well as the redirect. You referenced many of the items in your question or your follow-ups. But you will want to ensure that the steps are followed. So you may want to disable publishing and distribution on the primary and gut it all to start from scratch.

    https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-ver16

    We are here for follow-up.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.