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.
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.
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
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.
This script can be executed on the secondary database (subscriber)
Script to run on the subscriber tables
Enjoy it!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.