Brad McGehee over at SQL Aloha posted his July Question: What Do You Think are the Most Common DBA Pain Points and it got me thinking about my DBA career and some of the pain points I’ve experienced with SQL Server Replication.
One of the biggest problems I face is ensuring the convergence of data
in all nodes in my Merge topologies. There are a variety of reasons for
non-convergence to occur such as network related issues, conflicts,
improper filters, and/or some general catastrophic error. Sometimes
disasters happen and they need to be cleaned up.
If and when non-convergence occurs, it’s important to identify the root cause and correct it. After doing so, you'll likely need to get everything back in sync. If you're lucky and the volume of non-converged data is minimal, a complete tear down might not be required. Sometimes the problem can be corrected by identifying the non-converged data and performing dummy updates on these rows, or with reinitialization.
However, if the non-convergence is widespread you may want to consider tearing everything down (publications and subscriptions), manually synchronizing the non-converged databases, and setting everything back up again. The detection of non-convergence and the manual synchronization of databases
can be done using the tablediff utility but an even better tool is Red Gate’s SQL Data Compare.
SQL Data Compare is by far superior to the tablediff utility and offers a powerful user interface. This tool has saved my bacon on numerous occasions and has got me up and running quickly after replication disasters. It's a fine tool to have in your DBA toolbox if you're replicating databases.
