Tips for troubleshooting SC 2012 Configuration Manager Data Replication Service (DRS)

  • Article

In SC 2012 Configuration Manager, Site to Site communication will use SQL Service Broker feature to replicate data between site databases instead of file based replication in previous Configuration Manager versions.

With SQL replication, the performance and reliability will be improved. However, it might be a little more difficult for users to trouble shoot replication issue. In this blog, I will list some tips for troubleshooting the SQL replication in SC 2012 Configuration Manager.

In Configuration Manager Console, you can view the current status of the replication links. Go to Monitoring Tab -> Overview -> Site Hierarchy. If it shows green, then everything is fine.

If it’s not green, below is some steps you can use to troubleshoot:

1. Use Replication Link Analyzer.

The first place you can check is the Replication Link Analyzer. Go to Monitoring Tab -> Overview -> Database Replication. Select the link. In the lower side of the window, you can see the details status of this link: whether the replication is active; what’s the status of the global data replication link and the site data replication link.

Right click the link name will bring out the Replication Link Analyzer Wizard. Follow the wizard to remediate if necessary and review the result file and log on desktop: ReplicationLinkAnalysis.log and ReplicationLinkAnalysis.xml.

Most problems can be found and fixed by RLA. RLA will check the following rules on both sites

  • SMS service is running
  • SMS Replication Configuration Monitor component is running
  • Ports required for SQL replication is enabled
  • SQL version is supported
  • Network is available between the two sites
  • There’s enough space for SQL Database
  • SQL Server Broker service configuration exist
  • SQL Server Broker service certificate exist
  • Any know errors in SQL log files
  • Are the replication queues disabled
  • Time is in sync
  • Is the transmission of data stuck
  • Does Key Conflict exist

 More about RLA: https://technet.microsoft.com/en-us/library/gg712676.aspx#BKMK_RLA

2. Log file – rcmctrl.log, replmgr.log.

This is the second place you should look after RLA. You can update the registry key to set the logging level. The value can be found at: "HKEY_LOCAL_MACHINE\Software\Microsoft\SMS\Components\SMS_REPLICATION_CONFIGURATION_MONITOR\Verbose logging". Default is value 0.
Value 0: Errors and key messages;
Value 1: Everything in value 0 and Warnings and more general information;
Value 2: Verbose (everything)

3. Now if you still didn’t find out what happens, you will need to go into the SQL for more information.

Run script spDiagDRS: it will output useful information about the DRS General Status, Current replication link status, Last sync time for each replication group, etc.

Look the view vLogs: it will show more details info for the whole process like when DRS is wake up to check for the changes, when it receive the BCP from the publisher, when it ProcessSyncDataXml, when the specific table is updated, etc.

4. The left methods will be troubleshoot the SQL Service Broker itself.

You can check the SQL Service Broker log: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\ErrorLog

Or follow the link here to use Profiler and SSB related table to troubleshoot service broker problems: https://www.sqlteam.com/article/how-to-troubleshoot-service-broker-problems

5. Reinitiate by sending subscription invalid message.

This would be the last step you can take. This will cause all the data be re-replicated between the sites and will cause a lot of network traffic.

To reinit site data, run on CAS

SELECT ID, ReplicationGroup, ReplicationPattern, TransportType, 0 from ReplicationData where ReplicationPattern='site'

EXEC spDrsSendSubscriptionInvalid <ReceivingSiteCode/Subscriber>, <SendingSiteCode/Publisher>, <ReplicationGroupName>

Eg: EXEC spDrsSendSubscriptionInvalid 'PE1', 'CAR', ‘CI_Compliance_Rule_Details'

To reinit Global data, run on Primary

SELECT ID, ReplicationGroup, ReplicationPattern, TransportType, 0 from ReplicationData where ReplicationPattern='global'

EXEC spDrsSendSubscriptionInvalid <ReceivingSiteCode/Subscriber>, <SendingSiteCode/Publisher>, <ReplicationGroupName>

Eg: EXEC spDrsSendSubscriptionInvalid 'CAR', 'PE1', ‘Configuration Data’