This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(243.2, 42%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBV%3C/text%3E%3C/svg%3E)
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!
I don't know anything about the issue as such, but CU3 is quite old. I would recommend that you download and install CU 15, the most recent Cumulative Update It could be that you are hitting an issue that has been fixed.
If the issue persists after upgrading to CU15, I would recommend that you set up a separate environment with SQL 2022 CU15 like you did with SQL 2019. This helps to nail down whether this is a issue in SQL Server itself, or something specific in the environment of the troublesome AG.
HI Erland,
Thanks you for your response!
i created separate environment and setup AAG with SQL Server 2022 CU13 and interestingly, Replication still work well!
Now iam really clueless what to check on customer environment!
So please advice if you have any idea!
Hi Erland,
I created separate environment and setup SQL 2022 CU13 and interestingly replication still work well! That mean problem might come from some setup on customer!
But now iam really clueless on what to do next!
So please advice if you have any idea!
Many Thanks!
Just to clarify: You actually have CU13 in production and not CU3 as the title says? (CU13 matches the KB number, but I wanted to check.) If it is CU13, maybe you can edit the title?
I don't work much with replication, so I have further ideas for troubleshooting. But I will get in touch with a friend and see if can step it.
Sorry, version is actually CU13. I edited the title!
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:
So for now, i only have one concern left: do you have any plan to bring/support Replication on Contained Always On Groups?
I don't know if Microsoft is planning on supporting Contained AG Replication, but you found it, SQL 2022 Contained AGs do not support replication.
That explains the matter. One could just wish that the error message could be clearer.
Yes the error message really confused me. it took me days to find a way out but can not!
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.
We are here for follow-up.
Thank you for your response!
I did actually follow the link you sent to setup Replication on Always on Group.The problem turns out come from new feature of SQL 2022, Contained Always on Group, which currently not support Replication!
So what i concern now is whether Microsoft have any plan to bring Replication on Contained Always On or not?
Like Ben I have no information, but I think the only reason that Microsoft would implement replication for Contained AGs is that there is a huge demand for it. And I mean huge. Microsoft has a track record of where feature A does not work with feature B. At least in this case there is an alternative: Use a non-contained AG.
To sum up: I would hold my breath.
On this site, you can submit suggestions for improvements: https://feedback.azure.com/forums/908035-sql-server. It helps if you can give a business case for your request. I guess in this case, the fact that you want to add replication to an existing contained AG is not to be neglected.
Hi Erland,
Contained AG have some good features for IT management team. thats why they set it up. anyway,
Let me submit suggest for improvements following the link you sent!
Thank you!