October 29, 2023 at 11:11 pm
Hi there, I have this situation that I have been trying to get around for some time but I haven't been able to despite some considerable investigation. I have transactional replication between 2 servers, the standard run of the mill set up. I noticed after some time, the replication goes out of sync (or breaks) and I have to update the publication with the tables that are required at the subscription. So I go and include the missing articles in the publication and the subscriber gets the update through a new log snapshot and everything is in order again and working smoothly. In the last 2 weeks I've had to do this about 4 times and I realized the publisher server through an external process (from the front end application), creates 'new incremental tables'. I have done some checking and haven't been able to find a solution that will enable replication to automatically take in the new tables that get created to the publication and progressively update the subscriber with the same.
Please if anyone is able to provide some advise around this - let me know.
Thanks.
October 31, 2023 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 31, 2023 at 5:50 pm
Hi there, I have this situation that I have been trying to get around for some time but I haven't been able to despite some considerable investigation. I have transactional replication between 2 servers, the standard run of the mill set up. I noticed after some time, the replication goes out of sync (or breaks) and I have to update the publication with the tables that are required at the subscription. So I go and include the missing articles in the publication and the subscriber gets the update through a new log snapshot and everything is in order again and working smoothly. In the last 2 weeks I've had to do this about 4 times and I realized the publisher server through an external process (from the front end application), creates 'new incremental tables'. I have done some checking and haven't been able to find a solution that will enable replication to automatically take in the new tables that get created to the publication and progressively update the subscriber with the same.
Please if anyone is able to provide some advise around this - let me know.
Thanks.
Are these incremental tables permanent? Do you get any kind of notification when they get created? If not, I think I would set up a process that checks for the existence of new objects.
You could do something like test for the existence of a new table, and script the creation of a new publication / subscription / initialization for the new objects. Depending upon how often this occurs, the number of publications and subscriptions may be significant in short period. Once there are X number of new publications and subscriptions, you could combine these into a single publications/subscription to keep things cleaner.
What is the reason you have replication in place? If it's something like reporting, then an Availability Group may be a better choice, although more expensive.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 28, 2023 at 2:53 am
Thank you Michael - sorry for not responding sooner.
Yes it will be really helpful if I could have a trigger or a stored procedure to check every time a new table gets created to act as a trigger to get another job to the meat and potatoes of adding article to publication, and then refreshing subscription.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply