SCCM SQL Based Replication Guide ConfigMgr Endpoint Manager. 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 which are the main pillars of the SQL replication model. Also, find very useful blog posts about SQL-based replication from Umair Khan here. This SCCM SQL Based Replication Guide gives you end-to-end coverage of issues.
Latest Post – Enable Windows 10 Servicing Using SCCM | ConfigMgr | WSUS HTMD Blog (anoopcnair.com)
SCCM SQL Based Replication Guide ConfigMgr Endpoint Manager
Microsoft ConfigMgr Team released another extensive guide for SCCM ConfigMgr – SQL based related troubleshooting – here
Sudheesh SQL Troubleshooting Guide – here
Key components of 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
Also some tips:: how to :: force or re-init site to site replication, verify the site to site replication……
Before going into deep dive, I would like to thank Saud Al-Mishari, Microsoft PFE who was the speaker of MMS 2012 session “CD-B407”. This post is inspired from his session.
DRS – Data Replication Service / SSB – SQL Service Broker
To replicate the data between configmgr sites, Configuration Manager uses Database Replication Service (DRS). The DRS intern uses SQL Server Service Broker (SSB) to replicate data between the sites.
More Details about DRS – TechNet Article
More Details about SSB – SQL Team Article
RCM – Replication Configuration Management/Monitoring
RCM is a thread of SMSEXEC. As the name suggests, this thread keeps an eye on Replication Configuration and Monitoring. You can refer to rcmctrl.log file to get 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 in to THREE Replication Patterns.
To get the full list of RG along with replication schedule – Run the SQL query – Select * from vReplicationData
Each RG (Replication Group) has a unique Replication ID. In CM 2012 RTM release, all the transport is based on DRS.
Replication Pattern
Replication Patterns are group rules based on those, the replication groups are segregated. Three replication patterns are available. More Details about Data Replication – TechNet Blog
a) Global – Global data is anything that is created by administrator. Two way replication between the CAS and Primaries. e.g Package Metadata
b) Global_Proxy – This Replication data is based out secondary servers.
c) Site – One way replication to the parent site / CAS. e.g Software Inventory/Hardware Inventory
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 “ Select * from vArticleData “
e.g Add_Remove_Programs_64_DATA, Add_Remove_Programs_64_HIST, Add_Remove_Programs_DATA, BoundaryGroup, BoundaryGroupMembers etc…
Force Site To Site Replication / re-init process
You can use <site_Code>.SHA file or Preinst.exe /syncchild, If you want to force site to site replication in SCCM / ConfigMgr 2007. Fortunately/Unfortunately, these methods are NOT going to work in CM 2012 
/SYNCCHILD option to sync child sites has been deprecated. This functionality is
not longer required in System Center 2012 Configuration Manager.
If you need to perform a manual sync between the CAS and Primary server, same like dropping .SHA file in the inbox or syncchild…..
You can use the stored procedure (sproc) spDrsSendSubscriptionInvalid with suitable parameter to force the site to site replication.
Word of caution – This will start the re replication between the sites and may cause of lot of Network Traffic…..
EXEC spDrsSendSubscriptionInvalid,,
e.g 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 Transmission Queue for a particular site (in my example it’s site PR1)
SELECT TOP 1000 *, casted_message_body = CASE message_type_name WHEN 'X' THEN CAST(message_body AS NVARCHAR(MAX)) ELSE message_body END FROM [CM_CAS].[sys].[transmission_queue] where to_service_name = 'ConfigMgrDRS_SitePR1'
You can verify the transmission logs through vLogs view.
Word of Caution – Avoid using “ select * from vLogs ” query in production environment !!
Select top 1000 * from vLogs order by LogTime desc
You can verify site replication through the rcmctrl.log at CAS and Primary servers.
rcmctrl.log @ CAS server.
See the log file entry “Created miniJob to send compressed copy of DRS INIT BCP Package to site PR1”
rcmctrl.log @ Primary server (PR1)
Some Random SQL Queries which will help to troubleshoot further on this type of issues. Make sure you have 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'
