http://www.sqlservercentral.com/blogs/sqlserver365/2012/09/20/tablediff-utility/ Printed 2014/10/25 10:26PM
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; -- get created date of user tables FROM sys.tables whereis_ms_shipped = 0 -- Get record count for all user tables SELECT o.name 'TableName', p.[rows] 'Rows' JOIN sys.partitions p ON o.object_id = p.object_id 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; -- 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] CREATE TABLE [dbo].[MergeTest]( [MergeTestID] [int] IDENTITY(1,1), [Tester] [varchar](255) NULL, [ROWGUID] [uniqueidentifier] ROWGUIDCOL NULL, CONSTRAINT[PK_MergeTest:MergeTestID] PRIMARY KEY CLUSTERED )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ALTER TABLE [dbo].[MergeTest] ADD CONSTRAINT[MSmerge_default_constraint_for_rowguidcol_of_2137058649] DEFAULT (newsequentialid()) FOR [ROWGUID] The table contains 10,000 records which can be inserted with below; INSERT INTO MergeTest (Tester) VALUES ('Testing Merge Replication') The publication can be created using the below; EXEC sp_replicationdboption @dbname = N'ReplicationTest', @optname = N'merge publish',@value = N'true' 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 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 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 The subscription can be created using the below; 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' 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. 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; There are also 4 more parameters you will need to add if you are using SQL authentication; 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; INSERT INTO MergeTest (Tester) VALUES ('Testing Merge Replication') 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. 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. 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; INSERT INTO MergeTest (Tester) VALUES ('Testing Merge Replication') 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. -- 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 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; -- Take a look at MergeTestDiff FROMSQLServer365.dbo.MergeTestDiff MergeTestID MSdifftool_ErrorCode MSdifftool_ErrorDescription 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; -- 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' 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.