We are "republishing" 20 SQL Server 2012 source databases, using SQL 2016 as the republisher and subscribers, with SQL 2017 acting as the single distributor for both "hops" (Hop 1 being from the source to the republisher, and Hop 2 being from the republisher to the two subscribers). Both hops are using transactional replication. Everything described below is occurring in our test environment - we have not yet gone live in production.
We've noticed today that some of the tables are not propagating properly for Hop 2. On both subscribers they are essentially frozen - nothing that we do at the source or the republisher propagates to them - i.e. INSERTS, UPDATES, DELETES - none come across to either of the subscribers. However the publication is still working - all other tables are replicating fine - it's just a few of the tables in this situation.
Although we have set up fairly detailed replication monitoring, there are no alerts or errors being generated anywhere that we can see.
UPDATE #1: There seem to be two different kinds of freeze going on here. We have found we can identify [most of] the frozen tables by comparing the results of these two statements:
- SELECT publisher_db, source_owner, source_object FROM distribution.dbo.MSarticles (run on the distributor)
- SELECT 'My_Database_Name', dest_owner, dest_table FROM My_Database_Name..sysarticles (run on the republisher)
There are discrepancies in both directions - for example at this exact moment there are 30 articles that exist on the republisher without a match on the distributor, and there are 8 articles on the distributor that don't exist on the publisher. It's the first of those that is the indicator of a problem. Any table that is listed as an article on the republisher but not on the distributor is what we are calling a "Type 1" freeze.
UPDATE #2: We discovered today that there is also a "Type 2" freeze. These can be identified on the distributor by comparing the MSarticles table with MSsubscriptions. We found that across the ~1000 tables in 20 databases that we are replicating, we had 9 which were unexpectedly missing entries in the subscriptions list. Each one was from a different database, and (I am not making this up!) every one of them had an article_id = 16. No idea why 16, but that's what we're seeing. Hopefully MS Support can make something of that.
Investigations so far
We have not yet been able to determine what is special or different about these frozen tables - they certainly appear to be very simple and normal, and they were published in the same way as the working tables. Some are very small - for example just 5 fields and two rows.
There are no error messages anywhere - the LogReader Agent, the Distribution Agent and the Snapshot Agent are all reporting that everything is working fine. The table distribution.dbo.MSrepl_errors did not shed any light.
Marking the subscribers for reinitialisation and then pushing a new snapshot does not solve the problem. There are no error messages, it all looks like it's doing the right thing, but afterwards the frozen table is still showing the same data that it was beforehand, i.e. the changes have not propagated across. This tells us that the problem affects both snapshots and normal transaction delivery.
Stopping and restarting the various agents does not solve the problem - the changes already made do not propagate, and new changes made afterwards do not propagate either. Restarting all the SQL instances involved has no effect.
The Validation feature seems to skip the frozen tables, which is why it has taken us so long to notice this problem. Validation works fine on the non-frozen tables, but simply does not mention the frozen tables at all. Apart from the data differences between the republisher and the subscribers, this is our only other clue about which tables are frozen and which are working.
Tracer tokens are working fine, as are our heartbeat tables which we update once every minute (unless it is the Heartbeat table itself that is frozen). This is the most strange thing about all this - changes are coming across fine for the working tables, but never appear for the frozen tables.
We have enabled verbose logging on the Distribution Agent, but no new clues appear.
For each frozen table, we can issue a sp_dropsubscription and then a sp_addsubscription, followed by sp_startpublication_snapshot. This is the only method that we have found so far that resolves the problem - i.e. the subscriber table now contains the latest data and receives any further changes. However we have found that after doing this workaround on every table, and also after removing and re-deploying replication entirely, that it works fine for a while, but within 24 hours we start seeing new freezes.
We are keen to know the root cause of the issue, and ideally prevent it from occurring at all. Any troubleshooting guidance would be appreciated.
UPDATE #3: We have now built a SQL Agent job that automatically applies the workaround whenever it detects a Type 1 frozen table. Not ideal, given that some of our tables are massive and our subscribers are over a slow connection.