Configuration Manager site to site communication uses SSB(SQL Server Service Broker) feature to replicate data between the site databases instead of the file based replication used in previous version of Configuration Manager.
We can troubleshoot SQL replication issue by following below approach.
1. Replication Link Analyzer.
2. Investigating Log files.
3. Performing SQL queries
4. Re-initiating replication.
Replication Link Analyzer :-
Your first step for troubleshooting replication should be to use the Replication Link Analyzer.
In the Configuration Manager console, start by viewing the current status of the replication
links. When you have problems, the first place you should check is the Replication Link
1. Click Monitoring | Overview | Site Hierarchy.
If the link icon is green, everything is fine. If it is not green, continue with this procedure to use the Replication Link Analyzer to troubleshoot.
2. Click Monitoring | Overview | Database Replication.
3. Select the link.
4. In the lower portion of the window, you can see the detailed status of this link. This
information includes whether the replication is active, and the status of the global data
replication link and the site data replication link.
5. Right-click the link name to open the Replication Link Analyzer Wizard.
6. Follow the wizard to remediate if necessary, and then review the result files:
The Replication Link Analyzer works by examining both sites and checking whether:
The SMS service is running
The SMS Replication Configuration Monitor component is running
The ports required for SQL replication are enabled
The SQL version is supported
The network is available between the two sites
There is enough space for the SQL database
The SSB service configuration exists
The SSB service certificate exists
There are any known errors in SQL log files
There are any replication queues disabled
Time is in sync
The transmission of data is stuck
A key conflict exists
The Replication Link Analyzer can find and fix most but not all database replication
problems. If Replication Link Analyzer has not helped you resolve your problem, you should
proceed with step 2.
Investigating log files :-
If you are still having difficulties after using the Replication Link Analyzer, your next step
should be to check the following two log files for all involved sites:
During the troubleshooting process, you might not get extra details with default logging.You need to turn on verbose logging using the following registry key:
Set the Value 0 for Errors and key messages (the default value)
Set the Value 1 for Errors, key messages, warnings and more general information
Set the Value 2, which is Verbose, to see everything
Performing SQL queries:-
If you are still unable to find the root cause of the issue, you need to run SQL queries using
Microsoft SQL Server Management Studio on the central administration site or primary site to
get more information. Specifically, you should:
1. Run the spDiagDRS script. The resulting output contains useful information about the general status of the database replication, the current replication link status, and the last sync time for each replication group.
2. Examine the vLogs view. These logs show more detailed information about the process.For example, when the database replication checks for changes, when it receives the BCP (bulk copy data) from the publisher, when it ProcessSyncDataXml, and when a specific table is updated.
3. Check the SSB log found at: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\ErrorLog.
For more information on using profiler and SSB-related tables to troubleshoot service
broker problems, see http://www.sqlteam.com/article/how-to-troubleshoot-service-broker problems.
Re-initiating replication by sending a subscription invalid message should be the last step you
try because it causes all the data to be re-replicated between the sites, which will generate a
lot of network traffic.
To re-initiate the global data, run the following SQL command:
EXEC spDrsSendSubscriptionInvalid ‘SiteCode’, ‘SiteCode’, ‘Configuration Data’