FIX SCCM SQL Based Database Replication Failure Between CAS Primary

A quick post about FIX SCCM SQL Based Database Replication. The SQL-based replicated issues are common when you have a Configuration Manager hierarchy with CAS, Primary, or Secondary servers. Let’s check the necessary troubleshooting steps an SCCM admin can perform.

This is another SCCM Database SQL replication error troubleshooting post from the HTMD community. There are several other posts that also help to understand the end-to-end process of SCCM Database replication troubleshooting.

Latest Post – FIX SCCM SQL Replication Issues Using Replication Link Analyzer

Introduction

The replication was failed for Multiple groups and tried to run Replication Link Analyzer; the replication Link Analyzer suggested reinit and fixing the issue. Proceeded with the suggested step, and it started reinit for replication Group “Configuration Data.”

The “Configuration Data” re-initialization was stuck for more than 4-5 hours with status 2 on Primary and status 4 on CAS for the replication group “Configuration Data,” and it was not moving.

We have tried to restart SMS_executive on CAS and Primary sites, but it did not help. We also tried failover on the primary site SQL Server.

Microsoft Documentation-ConfigMgr DRS Troubleshooting https://support.microsoft

Verify SCCM Replication issue Groups from SCCM SQL DB Queries

If DRS data replication fails from Primary to CAS and vice versa, follow the below steps to fix the issue.

Run “spdiagdrs” and verify if any messages are pending in the outgoing messages queue and see which DRS replications are failed for global and site.

Below is the query to track the re-initialization status of ‘Configuration Data’ on the CAS and PR1 site CM database.

SELECT * FROM RCM_DrsInitializationTracking WHERE ReplicationGroup = 'Configuration Data' and SiteRequesting = 'PR1' order by CreatedTime desc

Found both the site was trying to finish the re-initialization for replication group ‘Configuration Data’ with different replication Request Tracking GUID

On primary Site (PRI): The request tracking GUID ending with E4F with status 2 and the same id was Aborted on the CAS site.

On Central Site (CAS): The request tracking GUID ending with 73C with status 4 and the same id was Aborted on PR1 primary site.

Hear one site was sending the process, and the other was not accepted due to the aborted ( the request tracking status 7).

NOTE: It’s not recommended to update/modify SQL Database directly. If you see any error on SQL, I recommend raising a ticket with MS and getting it fixed. Or You can try backing up SQL DB before running the following modification queries.

Solution 1 – FIX SCCM SQL Based Database Replication

Aborted the stuck replication Request Tracking GUID on Central Site CAS and Primary Site PR1 CM database using the below query. ( Change the request tracking GUID below.

UPDATE CM_DrsInitializationTracking SET InitializationStatus =7 WHERE RequestTrackingGUID = '<<Request Tracking GUID>>'

After running the Above query on both the sites new process for reinit RG “Configuration Data” started automatically, and reinit was completed, and the site came to active.
If initialization did not start automatically, we can use the below SQL command to reinit it manually.
To initialize the replication group, you can run the below SQL query on the Primary site or CAS site CM database.

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

Example:

The replication is failed for Configuration data from PRI to CAS.

EXEC spDrsSendSubscriptionInvalid 'PRI', 'CAS', 'Configuration Data'

The replication is failed for “Configuration data” from CAS to PRI.

EXEC spDrsSendSubscriptionInvalid 'CAS', 'PRI', 'Configuration Data'

Query to check Initialization status.

select InitializationPercent, InitializationStatus, TryCount,* from RCM_DrsInitializationTracking where InitializationStatus not in (6,7)order by CreatedTime desc;

Initialization Status

Let’s understand the SCCM Database Replication initialization status.

1 is Making Request
2 is Sent BCP file
3 is Acknowledgement from CAS Server
4 is BCP Finished
5 is CAS Prepare CAB file, after a copy of CAS to Primary state changes to
6 is Good
7 is the Previous attempt is aborted

Solution 2: FIX SCCM SQL Based Database Replication

If DRS data replication fails from Primary to CAS and vice versa, follow the below steps to fix the issue.

Run spdiagdrs and verify if any messages are pending in the outgoing messages queue and see which DRS replications are failed for global and site.

Run below queries on CAS and Primary DB to see any backlogs.

Select * from DrssendHistory where ProcessedTime is NULL
Select * from RCM_ReplicationLinkStatus where snapshotapplied <>1
select * from sys.transmission_queue order by enqueue_time desc

If you see any transmission backlogs run the below query to clean up backlogs.

SQL Query – SCCM-query-to-clean-up-backlogs/SCCMBacklogClean.sql at main · AnoopCNair/SCCM-query-to-clean-up-backlogs (github.com)

FIX SCCM SQL Based Database Replication Failure Between CAS Primary| ConfigMgr

Global data replication Re-initiation:

Create a .PUB file in rcm inbox folder on the primary site for failed global data group

Verify rcmctrl.log for data processing in the primary server

You can check the percentage initialization with the below query.

Select * from RCM_DrsInitializationTracking where InitializationStatus not in (6, 7) order by createdtime desc

Once the existing data group replication has been completed you can re-initiate other global data groups one by one

NOTE! – Do not re-initiate two data groups at a time; we’ll need to do it one at a time and wait for the first one to be completed

The next step is to re-initiate failed site DRS data from the Primary server one by one.

Site data replication Re-initiation:

Create a .PUB file in the CAS server rcm inbox folder for the failed site data group

Verify rcmctrl.log for data processing in the primary server

You can check the percentage initialization with the below query.

Select * from RCM_DrsInitializationTracking where InitializationStatus not in (6, 7) order by createdtime desc

At 41 percent, you see below folders created on the rcm box

Once data group replication is completed, you can see the below message in rcmctrl.log once data group replication is completed.

Once the existing data group replication has been completed, you can re-initiate other site data groups one by one.

NOTE! – Do not re-initiate two data groups at a time. We’ll need to do it one at a time and wait for the first one to be completed.

Resources

Author

Mohan Kumar is a Technical Architect with more than 12 years of experience as an Endpoint Manager Configuration Manager (MEMCM) and has hands-on experience in SCCM, SCOM, SCORCH, SCVMM, SCEP, SQL, Azure, Intune, Update Management, etc. The main area of interest is designing and implementing ConfigMgr, OpsManager, Orchestrator, and Azure Infrastructure. He is having a vast knowledge of On-perm to Azure migration, SCOM to Azure Monitor, Migration On-Perm SQL to Azure SQL Always on setup, Configure serverless Database in Azure, Configure and Fix the ConfigMgr infrastructure related issue & troubleshooting.

FIX SCCM SQL Based Database Replication Failure Between CAS Primary | ConfigMgr