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.
- Check the path given on step 1 to see the T-SQL scripts generated (only for tables with differences between source and destination).
This script can be executed on the secondary database (subscriber)
Enjoy it!