I recently stumbled across a little gem of a utility called tablediff. I have been working a lot with replication in my current position and recently had to setup merge replication between 3 servers on our WAN. For those of you who have used replication in anger Replication Monitor doesn’t do a very good job of refreshing, especially for large publications or subscribers with quite a high latency, this is even more apparent during the delivery of a snapshot.
This particular publication was quite sizable and one particular subscriber was on a different continent so I was a little concerned about monitoring the delivery of the snapshot, now I could have used the below script to check the date and time the tables where created and also get the record count from each table on the subscriber;
-- Set database context
USESQLServer365;
GO
-- get created date of user tables
SELECT name, create_date
FROM sys.tables whereis_ms_shipped = 0
-- Get record count for all user tables
SELECT o.name 'TableName', p.[rows] 'Rows'
FROM sys.objects o
JOIN sys.partitions p ON o.object_id = p.object_id
WHERE (o.[type] = 'U')
AND o.is_ms_shipped = 0
AND(p.index_id IN (0,1))
ORDER BY p.[rows] DESC;
GO
I could have then compared this with the publisher to make sure they were in sync. I didn’t though, instead I decided to use tablediff, which is fantastic! BOL describes tablediff as;
The tablediffutility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:
· A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
· Perform a fast comparison by only comparing row counts and schema.
· Perform column-level comparisons.
· Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
· Log results to an output file or into a table in the destination database.
Let s setup a merge publication and subscribe to it so we can use tablediff. I have two databases ReplicationTest and SQLServer365 on my local instance of SQL Server 2008. ReplicationTest has a publication called MtoC_M (MastertoChild_Merge the naming convention adopted by my company for publications) which comprises of one article a table called MergeTest. SQLServer365 is the subscriber database, the definition of the table and replication topology is below;
The MergeTest table can be created using the below;
-- Set database context
USE [ReplicationTest]
GO
-- Drop table if it exixts
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[MergeTest]') AND [type] in (N'U'))
DROP TABLE [dbo].[MergeTest]
GO
-- Create table
CREATE TABLE [dbo].[MergeTest](
[MergeTestID] [int] IDENTITY(1,1),
[Tester] [varchar](255) NULL,
[ROWGUID] [uniqueidentifier] ROWGUIDCOL NULL,
CONSTRAINT[PK_MergeTest:MergeTestID] PRIMARY KEY CLUSTERED
(
[MergeTestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
ALTER TABLE [dbo].[MergeTest] ADD CONSTRAINT[MSmerge_default_constraint_for_rowguidcol_of_2137058649] DEFAULT (newsequentialid()) FOR [ROWGUID]
GO
The table contains 10,000 records which can be inserted with below;
-- Set database context
USE [ReplicationTest];
GO
-- Insert 10,000 records
INSERT INTO MergeTest (Tester)
VALUES ('Testing Merge Replication')
GO 10000
The publication can be created using the below;
-- Set database context
USE[ReplicationTest];
GO
-- Create publication
EXEC sp_replicationdboption @dbname = N'ReplicationTest', @optname = N'merge publish',@value = N'true'
GO
EXEC sp_addmergepublication @publication = N'MtoC_M', @description = N'Merge publication of database ''ReplicationTest'' from Publisher ''MANL003150''.', @sync_mode = N'native', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true',@enabled_for_internet = N'false',@snapshot_in_defaultfolder = N'true',@compress_snapshot = N'false', @ftp_port = 21, @ftp_subdirectory =N'ftp',@ftp_login = N'anonymous', @allow_subscription_copy =N'false',@add_to_active_directory = N'false',@dynamic_filters = N'false', @conflict_retention =14, @keep_partition_changes = N'false', @allow_synctoalternate =N'false',@max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0,@use_partition_groups = null, @publication_compatibility_level =N'100RTM',@replicate_ddl = 1,@allow_subscriber_initiated_snapshot = N'false',@allow_web_synchronization = N'false',@allow_partition_realignment = N'true',@retention_period_unit = N'days',@conflict_logging = N'both', @automatic_reinitialization_policy = 0
GO
EXEC sp_addpublication_snapshot@publication = N'MtoC_M', @frequency_type = 4, @frequency_interval =14, @frequency_relative_interval = 1,@frequency_recurrence_factor = 0, @frequency_subday =1, @frequency_subday_interval = 5,@active_start_time_of_day = 500, @active_end_time_of_day =235959, @active_start_date = 0, @active_end_date = 0, @job_login = null,@job_password = null,@publisher_security_mode = 1
GO
EXEC sp_addmergearticle @publication = N'MtoC_M', @article = N'MergeTest',@source_owner = N'dbo', @source_object = N'MergeTest', @type = N'table', @description = null, @creation_script =null, @pre_creation_cmd = N'drop',@schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range =10000, @identity_range =1000, @threshold =80, @destination_owner =N'dbo',@force_reinit_subscription = 1, @column_tracking = N'false',@subset_filterclause = null,@vertical_partition = N'false', @verify_resolver_signature =1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc =N'true',@check_permissions = 0,@subscriber_upload_options = 0, @delete_tracking = N'true',@compensate_for_errors = N'false',@stream_blob_columns = N'false', @partition_options =0
GO
The subscription can be created using the below;
-- Set database context
USE[ReplicationTest];
GO
-- Create subscription
EXEC sp_addmergesubscription @publication = N'MtoC_M', @subscriber = N'manl003150',@subscriber_db = N'SQLServer365', @subscription_type =N'Push',@sync_type = N'Automatic', @subscriber_type = N'Global',@subscription_priority = 75, @description = null, @use_interactive_resolver = N'False'
EXEC sp_addmergepushsubscription_agent @publication = N'MtoC_M', @subscriber = N'manl003150', @subscriber_db = N'SQLServer365',@job_login = null,@job_password = null,@subscriber_security_mode = 1, @publisher_security_mode =1, @frequency_type =64, @frequency_interval = 0, @frequency_relative_interval = 0,@frequency_recurrence_factor = 0, @frequency_subday =0, @frequency_subday_interval = 0,@active_start_time_of_day = 0, @active_end_time_of_day =235959, @active_start_date = 20120822,@active_end_date = 99991231, @enabled_for_syncmgr =N'False'
GO
Next we need to initialise the subscription from a new snapshot, again I’m using replication monitor for this because I’m lazy. Ok so now we have a synchronizing merge replication topology let’s take a look at tablediff.
Start with the below;
tablediff -sourceserver MANL003150 -sourcedatabase ReplicationTest -sourcetable MergeTest -sourceschema dbo -sourcelocked -destinationserver MANL003150 -destinationdatabase SQLServer365 -destinationtable MergeTest -destinationschema dbo -destinationlocked -o D:\ReplTableDiff\ReplicationTest-MtoC_M.txt
You will need to update the values for the below accordingly;
–sourceserver
-sourcedatabase
-destinationserver
-destinationdatabase
-o
There are also 4 more parameters you will need to add if you are using SQL authentication;
-sourceuser
-sourcepassword
-destinationuser
-destinationpassword
NOTE
Add -sourcepassword and -sourceuser before –sourcelocked
Add –destinationuser and -destinationpassword before -destinationlocked
Once you have updated the command open a command prompt window and browse to the COM directory of your SQL Server installation directory, mine is below;
C:\Program Files\Microsoft SQL Server\100\COM\
Paste the command and press enter, tablediff will analyse the schema and records of both tables and the output will be written to the output file you specified in the command mine is below;
Table [ReplicationTest].[dbo].[MergeTest] on MANL003150 and Table [SQLServer365].[dbo].[MergeTest] on MANL003150 are identical.
The requested operation took 0.2971372 seconds.
Result, but what about when the tables have different records, well let’s stop replication insert some records and run table diff again, I’ve just stopped replication from replication monitor as I’m running out of time, and below is the script to insert 10,000 more records;
-- Set database context
USE[ReplicationTest];
GO
-- Insert 10,000 records
INSERT INTO MergeTest (Tester)
VALUES ('Testing Merge Replication')
GO 10000
Right now run tablediff again, no need to change the output file name as it will just append, and voila, 10,000 differences followed by the ID’s of the differences, my output is below;
Table [ReplicationTest].[dbo].[MergeTest] on MANL003150 and Table [SQLServer365].[dbo].[MergeTest] on MANL003150 have 10000 differences.
Err MergeTestID
Src. Only 10001
Src. Only 10002
Src. Only 10003
Src. Only 10004
Src. Only 10005
Awesome or what?! What’s that, how about schema differences, well let’s take a look. I’ve just started replication synchronizing again so that we don’t get another 10,000 records to scroll through in the output file, make sure you stop synchronising once the 10,000 records have synched though.
-- Set database context
USE[ReplicationTest];
GO
-- Update schema
ALTER TABLE MergeTest
ADD SchemaDiff INT NULL
So if you run tablediff again, no need to change the output file name as it will just append. Well would you take a look at that the tables have different schemas and cannot be compared.
Table [ReplicationTest].[dbo].[MergeTest] on MANL003150 and Table [SQLServer365].[dbo].[MergeTest] on MANL003150 have different schemas and cannot be compared.
The requested operation took 0.1407231 seconds.
The next thing I want to show you is how to record the differences between the two tables. Start replication synchronising again so the schema change can replicate, once this has replicated stop replication again and insert some more records using the below;
-- Set database context
USE[ReplicationTest];
GO
-- Insert 10,000 records
INSERT INTO MergeTest (Tester)
VALUES ('Testing Merge Replication')
GO 10000
Then run the below to record the differences in a .sql file MergeTestDiff.sql
tablediff -sourceserver MANL003150 -sourcedatabase ReplicationTest -sourcetable MergeTest -sourceschema dbo -sourcelocked -destinationserver MANL003150 -destinationdatabase SQLServer365 -destinationtable MergeTest -destinationschema dbo -destinationlocked -dt -et MergeTestDiff -f D:\ReplTableDiff\MergeTestDiff.sql
And hey presto we have a .sql file with the insert statements to update the subscriber.
-- Host: MANL003150
-- Database: [SQLServer365]
-- Table: [dbo].[MergeTest]
SET IDENTITY_INSERT [dbo].[MergeTest] ON
INSERT INTO [dbo].[MergeTest] ([MergeTestID],[ROWGUID],[SchemaDiff],[Tester]) VALUES (42001,'11fcecd2-0503-e211-862c-54766c3ed109',Null,N'Testing Merge Replication')
INSERT INTO [dbo].[MergeTest] ([MergeTestID],[ROWGUID],[SchemaDiff],[Tester]) VALUES (42002,'12fcecd2-0503-e211-862c-54766c3ed109',Null,N'Testing Merge Replication')
INSERT INTO [dbo].[MergeTest] ([MergeTestID],[ROWGUID],[SchemaDiff],[Tester]) VALUES (42003,'13fcecd2-0503-e211-862c-54766c3ed109',Null,N'Testing Merge Replication')
INSERT INTO [dbo].[MergeTest] ([MergeTestID],[ROWGUID],[SchemaDiff],[Tester]) VALUES (42004,'14fcecd2-0503-e211-862c-54766c3ed109',Null,N'Testing Merge Replication')
The penultimate thing I am going to cover in this post is to log to a table, for this we need two parameters;
-dt this tells tablediff to drop the table if it already exists
-et creates the table to output the results too
So my command becomes;
tablediff -sourceserver MANL003150 -sourcedatabase ReplicationTest -sourcetable MergeTest -sourceschema dbo -sourcelocked -destinationserver MANL003150 -destinationdatabase SQLServer365 -destinationtable MergeTest -destinationschema dbo -destinationlocked -dt -et MergeTestDiff
With replication still NOT synchronising run the above command, this will create a table called MergeTestDiff with the same output we saw earlier in our output file, to take a look run the below;
-- Set database context
USE[SQLServer365];
GO
-- Take a look at MergeTestDiff
SELECT TOP 100 *
FROMSQLServer365.dbo.MergeTestDiff
GO
MergeTestID MSdifftool_ErrorCode MSdifftool_ErrorDescription
52001 2 Src. Only
52002 2 Src. Only
52003 2 Src. Only
52004 2 Src. Only
52005 2 Src. Only
I must say in case you haven’t already guessed but at this point I am in complete awe of this utility.
Finally below is a script to generate the command line commands to compare all merge articles;
-- Set database context
USEReplicationTest
GO
-- Generate command line commands for all merge articles
SELECT '"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver [MANL003150] -sourcedatabase [ReplicationTest] -sourcetable ['+ name + '] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [MANL003150] -destinationdatabase [SQLServer365] -destinationtable [' + name + '] -destinationschema [dbo] -destinationlocked [TABLOCK] -f D:\ReplTableDiff\' + CAST(name AS VARCHAR(100))+ '.sql'
FROMsysmergearticles
GO
The results can be copied and executed or used in a batch file there are a lot of uses for this utility and I hope this post has given you an insight into it.
Enjoy!
Chris
