Using the Tablediff utility to troubleshoot problems in transactional replication

Transactional replication is a commonly used means in data synchronization, replication process will inevitably encounter a lot of problems, in the author's problems, there are generally two categories: one is to solve the problem by restarting the distributor agent, the other is because subscriber modified the data, Causes data to be published to conflict, this kind of problem generally needs to be repaired manually. Tablediff is a command-line utility for SQL Server 2005 that compares two tables and generates scripts for data synchronization. With this tool, you can easily fix problems with two table data inconsistencies. However, if you apply the tool to resolve data conflicts in transactional replication, in addition to synchronizing data, you must also resolve the issue of skipping the unpublished error transaction sequence after manually synchronizing data. The 1th part of this article introduces the usage of Tablediff tools and some of the author's usage tests, and part 2nd describes how to use this tool to fix data conflicts in transactional replication. It is hoped that the two-part introduction will help to solve the problem of transactional replication. I. The role of the tool compares data in two non convergent tables, you can use the utility from a command prompt or in a batch file to perform the following tasks: Ø the source table in the SQL Server instance that acts as the replication Publisher and one or more SQL that acts as a replication subscriber Row-by-line comparison between the target tables in the server instance. Ø A quick comparison can be performed by comparing only the number of rows and schemas. Ø perform column-level comparisons. Ø T-SQL script is generated to fix the differences in the target server so that the source and target tables converge. Ø record the results to the output file or to the table in the target database ii. use of the tool requires the use of this tool, the following conditions are required: Ø only for SQL Server. Ø columns that do not contain sql_variant data types Øsource table and destination table need to meet the following consistency: N number consistent n name consistent n if you use the-STRICT option to require a consistent type of column, only the type of the column is required to be compatible 。 The following data types are compatible

source data type	Target data type	source data Type	Target data type
tinyint	smallint, int, bigint	nvarchar (max)	ntext
smallint	Int, Bi Gint	varbinary (max)	image
int	bigint	text	varchar (max)
timestamp	varbinary	ntext	nvarchar (max)
varchar (max)	text	image	varbinary (max)

Øsource table must contain at least one: N primary key n identity n ROWGUID column n unique column n use-strict option, destination table must contain at least one of the above columns o if you generate T-SQL scripts, The script does not contain columns of the following data types: N varchar (max) n nvarchar (max) n varbinary (max) n text n ntext n image n timestamp n xml third, the use of instructions 1. Syntax and parameter description: The following table describes the usage syntax of TABLEDIFF and related parameter descriptions

TableDiff Grammar	parameter Description
[ -? ] | { -sourceserver Source_server_name[/instance_name] -sourcedatabase source_database -sourcetable Source_table_name [-sourceschema Source_schema_name] [-sourcepassword Source_password] [-sourceuser Source_login] [-sourcelocked]   -destinationserver Destination_server_name[/instance_name] -destinationdatabase subscription_database -destinationtable destination_table [-destinationschema Destination_schema_name] [-destinationpassword Destination_password] [-destinationuser Destination_login] [-destinationlocked]     [Q] [-c] [-strict]           [b large_object_bytes]     [-BF number_of_statements] [F [file_name]]         [-O output_file_name] [-et table_name] [-DT]       [-RC number_of_retries] [-ri Retry_interval] [-t connection_timeouts]   }	Returns a list of supported parameters
Sets the source information. If Sourceuser is not specified, Windows authentication is used. sourcelocked specifies how the source table is locked during comparison, either TABLOCK or Holdlock, not specified, and the source table is not locked (NOLOCK)
Set destination information. If DestinationUser is not specified, Windows authentication is used. destinationlocked specifies the way in which the destination table is locked during comparison, either Tablock or Holdlock, unspecified, and the destination table is not locked (NOLOCK)
Comparison mode:-Q Only compares the row number and schema-c comparison column level differences, if the T-SQL script file is generated, the column-level diff comparison is performed regardless of whether this option is specified-strict the source and target schemas are strictly compared