Why does using repair invalidate replication subscriptions?

Question: Several times in the last year I’ve been forced to run the repair option of CHECKDB because of corruption affecting a database and backups being unavailable. In one case the database was a replication publication database and I followed the Books Online guidelines to reinitialize it’s subscribers after running repair. Can you explain why this is necessary?

Answer: Any time that the REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB (hereafter just called ‘repair’) is used, you need to consider the effect on replication if the database being repaired is a publication database.

There are two cases to consider – if the repairs affected the replication metadata tables or not. The simplest is when they did not affect the replication metadata tables.

When using merge replication, changes to the publication database are captured using DML triggers. When using transactional replication, changes to the publication database are captured by analyzing the database’s transaction log and converting the logged operations into logical operations on the database. In both cases the logical operations are then applied to the replication subscribers.

Neither of these mechanisms allows the capture of operations performed by repair. Repair operations are always direct physical changes to the database structures to fix inconsistencies in the structures (e.g. a database page, table record, or a linkage between two pages), as opposed to physical changes because of queries performing inserts, updates, or deletes on tables.

These repair operations cannot translate into logical operations that can be applied to replication subscribers because there are no logical operations than can be expressed using Transact-SQL for the equivalent of the direct structural changes that repair is performing. This means that if any table that is part of a replication publication is changed by a repair operation, the replication subscription is no longer valid and must be reinitialized.

As an example, imagine a repair operation is forced to remove a data page from a unique clustered index (essentially deleting some table records), and the subscription is NOT reinitialized. Those records would still exist on the replicated copy of the table. If a subsequent insert operation inserted records with cluster key values corresponding to the records deleted by the repair operation, the Distribution Agent would fail when trying to apply the inserts to the replicated copy – because the repair operation was not applied to the subscription database and a duplicate key violation error will occur when attempting to apply the insert to the replicated table.

A replication subscription must always be reinitialized if any table in the publication is affected by a repair operation.

If the repair operation affects any of the replication metadata tables, the entire replication publication is in an inconsistent state and replication must be removed completely and reconfigured. This is a much more invasive process than just reinitializing a subscription, obviously.

You can read a little more about when you should do this in the SQL Server Books Online for DBCC CHECKDB.