Total records don't match after database failover

Question

We have database setup for asynchronized high availability group for disaster recovery. Some tables from the same database are replicated to multiple subscribers. The system databases are not setup for High Availability Group. so, when database is failover to secondary server, the replication is broken. Once the database is failed back to the Primary server. The replication restarts automatically. The replication monitor shows the data is synchronized. But comparing the total counts between publisher and subscribers, it doesn't much. The snapshot was taken 3 years ago. A couple of tables are huge. How to fix this issue without taking a new snapshot? How to do the failover without broken the replication or what kind of maintenance need be done to synchronize the data one Failover is completed.

Answer 1

For the data sync, you could use a data compare tool to see how exposed you are. Is there a reason why the distribution database isn't in the HA group? Anyway, once you get them synced, I would recommend that you stop the jobs for distribution before you fail over. Then start them after the failover is complete. Also did you add a redirect for the replication to the Listener? (Assuming you mean you are using Availability Groups) If not you really should, https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-ver16