Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 982900 - Last Review: October 5, 2011 - Revision: 5.0
SQL Merge Replication Non-Convergence
Microsoft Professional Advisory Services is a support option that provides short-term, proactive, consultative support beyond break-fix product maintenance needs. This includes working with the same technician for assistance with issues like product migration, code review, or new program development and is a remote, phone-based support option. This service is typically used for shorter engagements, and is designed for developers and IT professionals who do not require the traditional onsite consulting or sustained account management services that are available from other Microsoft support options.
For additional information on Microsoft Advisory Services, including on how to engage, refer to this Microsoft web page:
http://support.microsoft.com/gp/AdvisoryService
(http://support.microsoft.com/gp/AdvisoryService)
Customer running Merge Replication with filters observes Publisher data changes are not being replicated to subscriber(s).
Customer has configured Merge Replication using Horizontal “subset†and/or “join†filter logic. They are expecting data changes made at the Publisher to be migrated to 1 or many subscribers. However data is not appearing on either 1) the correct subscriber, or 2) any subscriber(s).
To analyze data non-convergence a detailed analysis of Publisher Trigger Logic and Publication Filtering needs to be performed. This includes obtaining the Replication scripts, metadata, and published database schema. A full backup of the all system database and user databases maybe required in order to reproduce problem in MS testing labs.
Customer should be able to provide example INS/UPD/DEL statements which demonstrate the problem.
Statement of Work for this Scenario
Analysis of customer’s Published database triggers, Replication filtering, and Replication metadata tracking tables is needed to determine root cause for the non-convergence. Included with Scope- Collecting Replication Publication Scripts
- Collecting Replication Publisher Metadata
- Collecting Replication Distributor Metadata
-
Collecting Replication Subscriber Metadata
- Restarting Merge Agent to collect verbose log
- Isolating problem to a single INS/UPD/DEL statement on the Publisher which demonstrates the problem.
- Collecting PSSDIAG/Perfmon data during observed problem to track workload Replication triggers and metadata tables.
- Collecting Replication metadata tables before and after the problem statements.
- Analyze the Replication filter logic to understand how Replication determines which subscriber should get the data.
-
Analyze the metadata tables before and after data change and to confirm which subscriber was to receive the data
Outside the Scope- Provide recommendation on best filter logic to meet customer’s business logic
-
Walkthrough of SQLIO to test disk subsystem throughput
- Recommendation on how to reconfigure IO subsystem to improve disk read/write performance
Below is a list of self-help resources for this scenario. Microsoft Support Engineers may also use these resources during an Advisory Services engagement.
First Steps in Troubleshooting ReplicationReplication Metadata Collection ScriptsKB article 312292 How to enable replication agents for logging to output files in SQL Server Merge Replication Best PracticesMerge Replication Centralized LoggingHow Merge Replication Tracks and Enumerates ChangesEnhancing Merge Replication PerformanceReplication team blog
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2008 Standard
| kbproadvisory kbinfo kbsurveynew kbtshoot kbnomt kbprosql kbprodeveloper KB982900 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate