How to: Compare Replicated Tables for Differences (Replication Programming)

  • 아티클

Article validation is used to determine if published data for table articles at the Publisher and Subscriber are not identical, which can indicate non-convergence. For more information, see 복제된 데이터의 유효성 검사. However, validation only returns pass or fail information and does not provide any information about what is different between the source and destination tables. The tablediff command prompt utility returns detailed difference information between two tables and can even generate a Transact-SQL script to bring a subscription into convergence with data at the Publisher.

[!참고] The tablediff utility is only supported for SQL Server servers.

To compare replicated tables for differences using tablediff

  1. From the command prompt at any server in a replication topology, run the tablediff 유틸리티. Specify the following parameters:

    • -sourceserver - name of the server on which the data is known to be correct, usually the Publisher.
    • -sourcedatabase - name of the database containing the correct data.
    • -sourcetable - name of the source table for the article being compared.
    • (Optional) -sourceschema - schema owner of the source table, if not the default schema.
    • (Optional) -sourceuser and -sourcepassword when using SQL Server Authentication to connect to the Publisher.
      ms147919.security(ko-kr,SQL.90).gif보안 정보:
      When possible, use Windows Authentication. If you must use SQL Server Authentication, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.
    • -destinationserver - name of the server on which the data is being compared, usually a Subscriber.
    • -destinationdatabase - name of a the database being compared.
    • -destinationtable - name of the table being compared.
    • (Optional) -destinationschema - schema owner of the destination table, if not the default schema.
    • (Optional) -destinationuser and -destinationpassword when using SQL Server Authentication to connect to the Subscriber.
      ms147919.security(ko-kr,SQL.90).gif보안 정보:
      When possible, use Windows Authentication. If you must use SQL Server Authentication, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.
    • (Optional) Use -c to do a column-level comparison.
    • (Optional) Use -q to do a fast, row count- and schema-only comparison.
    • (Optional) Specify a file name and path for -o to output the results to a file.
    • (Optional) Specify a table in the subscription database into which to insert results for -et. If the table already exists, specify -dt to first drop the table.
    • (Optional) Use -f to generate a Transact-SQL file to fix data at the Subscriber so that it matches data at the Publisher. Use -df to specify the number of Transact-SQL statements in each file.
    • (Optional) Use -rc and -ri to specify the number of times to retry an operation and the retry interval.
    • (Optional) Use -strict to enforce strict schema comparison between source and destination tables.

참고 항목

작업

How to: Validate Data at the Subscriber (Replication Transact-SQL Programming)
How to: Validate Data at the Subscriber (RMO Programming)

관련 자료

복제 문제 해결 도구
게시자와 구독자의 데이터가 일치하지 않음

도움말 및 정보

SQL Server 2005 지원 받기