Latest Posts

Troubleshooting Database replication


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
Analyzer.

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:
  ReplicationLinkAnalysis.log
          ReplicationLinkAnalysis.xml

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:
rcmctrl.log
    replmgr.log

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:
HKEY_LOCAL_MACHINE\Software\Microsoft\SMS\Component\SMS_REPLICATION_CONFIGURATION_
MONITOR\Verbose logging

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:-

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’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s