[Replication consistency] Compare all tables between two databases at once with Table Diff

Microsoft

Nov 24, 2022

When using a SQL Server Replication we need to make sure that our replication is healthy and also the data across replicated articles are consistent (some workloads can change the subscriber database)

The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology.

Although this utility is amazing, nowadays our database environments has hundreds of tables in one database, and so we can potentially have hundreds of articles being replicated across two databases. Comparing table by table could not be feasible and we might need a method to compare all tables between two databases at once.

Recently I came across with a case in which the customer needed an easy way to compare all the tables (thousands) from the publisher and the subscriber. To do achieve this, I’ve created a small T-SQL that generates all table diff commands for each existing table on publisher database.

Create the tablediff command to compare all tables between two databases at once

We can use T-SQL to generate the tablediff commands to compare all tables between two databases.

To generate and use the tablediff script, we can use the following steps.

NOTE: you need to run the commands on a VM, Server or machine with Microsoft SQL Server installed.

Open the SSMS, edit and run the following script on the source database (in the replication scenario the publisher).

This will generate the tablediff script for each table

Note: source and destination server name can be Microsoft SQL Server or Azure SQL Managed Instance

Declare @sourceserver nvarchar(100) = '<SourceServerName>,<port>'
Declare @sourceuser nvarchar(100) = '<sqluser>'
Declare @sourcepassword nvarchar(100) = '<password>'
Declare @sourcedatabase nvarchar(100) = '<sourcedatabase>'
Declare @destinationserver nvarchar(100) = '<servername>,<port>'
Declare @destinationuser nvarchar(100) = '<sqluser>'
Declare @destinationpassword nvarchar(100) = '<password>'
Declare @destinationdatabase nvarchar(100) = '<sourcedatabase>'
Declare @path_to_write_diferences nvarchar(100) = '<Existing Path to save the T-SQL script with differences>’ 
EX: ‘C:\Tables_diferences\'





SELECT 'tablediff -sourceserver ' + @sourceserver + ' -sourceuser ' + @sourceuser + 
' -sourcepassword ' + @sourcepassword + ' -sourcedatabase ' + @sourcedatabase + ' -sourcetable ' + TABLE_NAME + 
' -destinationserver ' + @destinationserver + ' -destinationuser ' + @destinationuser + 
' -destinationpassword ' + @destinationpassword + ' -destinationdatabase ' + @destinationdatabase + 
' -destinationtable ' + TABLE_NAME + ' -f ' + @path_to_write_diferences + TABLE_NAME + ' -t 36000'
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG= @sourcedatabase

Copy the generated tablediff results to a notepad (no need to add any concatenation operator like ‘& or ||’)

Select all scripts and copy them

Open a CMD window in Administrative mode

Go to the directory were tablediff.exe is placed something like: cd C:\Program Files\Microsoft SQL Server\150\COM

Paste all command at once and click enter

This will run each tablediff command and save the T-SQL with all differences to the path given on step 1.

The generated T-SQL script will contain the updates, deletes or inserts needed to run on the secondary database (subscriber) to converge all tables on both databases again.

Note: For all tables existing in the source and not existing in the destination, the process will return a fail and jump to the next script until the end.