SCCM SQL Based Replication Guide ConfigMgr

SCCM SQL Based Replication Guide ConfigMgr. The New (SQL) based site-to-site replication model is the most challenging but very interesting part (at least for me) of System Center Configuration Manager.

So, I thought of sharing some points about the main pillars of the SQL replication model. Also, in the resources section of the post, you can find very useful blog posts about SQL-based replication from Microsoft. This SCCM SQL Based Replication Guide gives you end-to-end coverage of issues.

Before going into depth, I would like to thank Saud Al-Mishari, Microsoft PFE (when this post was published), who spoke at the MMS 2012 session “CD-B407.”

I will also share some tips, such as how to force or re-init site-to-site replication and verify it.

Sign up to get the best of How To Manage Devices straight to your inbox!

• Troubleshoot and FIX SCCM SQL Backlog Issues
• FIX SCCM SQL Replication Issues Using Replication Link Analyzer

SCCM SQL Based Replication Guide ConfigMgr

Let’s understand the key components of the new (SQL) based replication model.

1. DRS – Data Replication Service
2. SSB – SQL Service Broker
3. RCM – Replication Configuration Management/Monitoring
4. RG – Replication Group
5. Replication Pattern
6. Article Name/s

DRS – Data Replication Service / SSB – SQL Service Broker

Configuration Manager uses Database Replication Service (DRS) to replicate the data between ConfigMgr sites. The DRS intern uses SQL Server Service Broker (SSB) to replicate data between the sites.

RCM –  Replication Configuration Management / Monitoring

RCM is a thread of SMSEXEC. As the name suggests, it monitors Replication Configuration and Monitoring. The rcmctrl.log file provides more details about RCM-related activities.

RG – Replication Group

Replication Groups are a set of tables that are monitored and replicated together. Replication groups are segregated and grouped into THREE Replication Patterns.

To get the full list of RG along with the replication schedule – Run the SQL query – Select * from vReplicationData

Each RG (Replication Group) has a unique Replication ID. In the CM 2012 (and CB version) RTM release, all the transport is based on DRS.

Replication Pattern

Replication Patterns are group rules based on those, and the replication groups are segregated. Three replication patterns are available. More Details about Data Replication – TechNet Blog

a) Global – Global data is anything that the administrator creates. Two-way replication between the CAS and Primaries. e.g Package Metadata

b) Global_Proxy – This Replication data is based on secondary servers.

c) Site – One-way replication to the parent site / CAS. e.g Software Inventory/Hardware Inventory

List of SCCM Database Article Name/s

Replication Groups are further divided into Article Names based on ReplicationID. Each RG (Replication Group) has a unique Replication ID.

Run the SQL query to get the list of Article Names used for the SCCM database.

Select * from vArticleData

e.g  Add_Remove_Programs_64_DATA, Add_Remove_Programs_64_HIST, Add_Remove_Programs_DATA, BoundaryGroup, BoundaryGroupMembers etc…

Force SCCM Database Site To Site Replication / re-init process

You can use <site_Code>.SHA file or Preinst.exe /syncchild If you want to force the site-to-site replication in SCCM / ConfigMgr 2007. Fortunately/unfortunately, these methods will NOT work in CM 2012.

/SYNCCHILD option to sync child sites has been deprecated. This functionality is no longer required in System Center 2012 Configuration Manager.

If you need to perform a manual sync between the CAS and Primary server, same as dropping.SHA file in the inbox or sync child…..

You can use the stored procedure (sproc) spDrsSendSubscriptionInvalid with suitable parameters to force site-to-site replication.

Word of caution – This will start the re-replication between the sites and may cause of a lot of Network Traffic…..

EXEC spDrsSendSubscriptionInvalid,,
EXEC spDrsSendSubscriptionInvalid 'PR1', 'CAS', 'Configuration Data'

How to verify the site-to-site replication from SQL Server Management studio?

Run the SQL query to check out the Transmission Queue for a particular site (in my example, it’s site PR1). The SQL query (Transmission Queue) is available in Add files via upload · AnoopCNair/SCCM-SQL-backlog-Check-Query@010e8f9 (github.com)

You can verify the transmission logs through the vLogs view.

Word of Caution – Avoid using the “ select * from vLogs ” query in the production environment !!

select * from vLogs
Select top 1000 * from vLogs order by LogTime desc

You can verify site replication through the rcmctrl.log on the CAS and Primary servers.

rcmctrl.log  @ CAS server.

See the log file entry “Created miniJob to send a compressed copy of DRS INIT BCP Package to site PR1.”

rcmctrl.log @ Primary server (PR1)

SQL Queries to Troubleshoot SCCM Database Replication

Some Random SQL Queries will help troubleshoot this type of issue further. Make sure you have a proper SQL backup before updating the SQL DB.

select * from RCM_ReplicationLinkStatus where SnapshotApplied <>1

Select * from Sites where SiteCode in ('CAS','H00')

Update ServerData set SiteStatus = 125 where SiteCode = 'CAS'

Exec spFakeSiteOutOfMaintenance 'CAS'

Select * from rcm_drsinitializationtracking where initializationstatus not in (6,7) order by initializationstatus desc

Exec SpDiagDRS

Update RCM_DrsInitializationTracking set InitializationStatus = 6 where SiteRequesting='H00' and SiteFulfilling='CAS'

Update Sites set Status =1 and DetailedStatus =125 where sitecode = 'CTO'

EXEC spDrsSendSubscriptionInvalid 'H00', 'CAS', 'Replication Configuration'

Resources

• More Details about DRS – TechNet Article
• More Details about SSB – SQL Team Article
• Microsoft ConfigMgr Team released another extensive guide for SCCM ConfigMgr – SQL based related troubleshooting – here
• Sudheesh SQL Troubleshooting Guide – here

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Author

Anoop C Nair has been Microsoft MVP from 2015 onwards for 10 consecutive years! He is a Workplace Solution Architect with more than 22+ years of experience in Workplace technologies. He is also a Blogger, Speaker, and leader of the Local User Group Community. His main focus is on Device Management technologies like SCCM and Intune. He writes about technologies like Intune, SCCM, Windows, Cloud PC, Windows, Entra, Microsoft Security, Career, etc..