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!