FIX SCCM SQL Replication Issues using Replication Link Analyzer

Let’s see how to FIX SCCM SQL Replication Issues using Replication Link Analyzer. SCCM Database replication issues are common when you have an SCCM hierarchy with CAS, Primary, or Secondary servers.

Let’s check what the necessary troubleshooting steps an SCCM admin can perform are. SCCM replication issue is not very easy to troubleshoot via forums or offline. 

The above SCCM Replication Issue troubleshooting video will help you fix some common SCCM replication issues. More SQL-related backlog checking and troubleshooting are covered in another blog post.

The SCCM Replication Link Analyzer tool has been the savior of many replication issues within the recent builds of Configuration Manager.

• Troubleshoot and FIX SCCM SQL Backlog Issues using SSMS
• FIX SCCM SQL Based Database Replication Failure Between CAS Primary

SCCM SQL Based Replication?

SCCM SQL Based replication? Or is it SCCM SQL-based replication? Yes, Umair mentioned in his post that it’s NOT SQL replication. Instead, it’s SQL Based on reproduction. Yes, I agree it’s not SQL replication; it’s Data Replication Service (DRS) introduced in SCCM 2012.

But why am I using SQL replication again? Because most of the SCCM admins don’t care about this is SQL replication or SQL-based replication. But it’s just SQL replication for them (including me) 🙂

I can’t write or talk about SQL Service Broker (SSB) and Change Tracking along with Bulk copy program (BCP) because I don’t know anything about those SQL technologies.

Let’s dive into some scenarios of SCCM Database Replication issues. I recommend reading the Umair blog (mentioned above) to get more details about the SCCM SQL replication or SCCM replication.

SCCM Database Replication Issue?

SCCM Database replication issue is critical most of the time because that can put SCCM infrastructure in read-only mode. Think about a scenario you can view the objects in the console, but you can’t take any action. What will you do?

When you hit the SCCM replication issue, you will have similar scenarios in the primary child server. SCCM CAS server will be in maintenance mode, and it will be waiting for the primary server to send data.

This type of SCCM SQL replication issue can cause during SCCM in-place upgrade scenarios. When you have an SCCM hierarchy as shown in the below diagram with CAS and many primary and secondary sites, the replication link analyzer is going to be one of your frequent tools to troubleshoot SCCM replication issues.

SCCM Replication Groups

You can get more details about SCCM replication groups from my previous post, “List of all Replication Groups and Article Names.” I have another post that talks about SCCM SQL-based replication in detail.

I recommend reading that to know more about replication groups and article names with examples – SCCM SQL Based Replication Guide.

What is SCCM Replication Link Analyzer (RLA)?

Replication Link Analyzer (RLA) is the tool that is integrated with the SCCM admin console to help the admin to troubleshoot SCCM Database Replication issues. The replication link analyzer produces a log file and a report to make troubleshooting easy.

The replication link analyzer tool is the first line of troubleshooting for me to fix the replication issues between the SCCM site servers. You need to have permissions on the source and destination site and SQL to perform the analysis through RLA.

The following are some of the tasks integrated with the SCCM Replication Link Analyzer tool. I have attached the full list in the Github Repository.

• The SMS service is running.
• SMS Replication Configuration Monitor component is running.
• Ports required for SQL Server replication are enabled.
• SQL Server version is supported.
• The network is available between the two sites.
• There’s enough space for the SQL Server database.
• SQL Server Broker service configuration exists.
• SQL Server Broker service certificate exists.
• Known errors in SQL Server log files.
• Whether the replication queues are disabled.
• Time is in sync.
• Is the transmission of data stuck?
• Does a key conflict exist?

FIX SCCM SQL Replication Issues using Replication Link Analyzer

The SCCM Replication Link Analyzer (RLA) is the first tool you should try to resolve or fix the SCCM replication issue. You can launch the SCCM replication link analyzer from:

• SCCM console Monitoring workspace
• Click the Database Replication Node

I have only one connection between the DB replication I have is between SCCM primary and secondary sites.

• Select the replication link that is having a problem (Parent Site MEM to Child Site HS0)
• Click on Replication Link Analyzer from the Ribbon button to launch the Replication Analyser tool.

Remember: It may prompt for a username and password to connect to the destination server if your user doesn’t have access to the destination server database.

The replication Link analyzer will check all the pre-configured checks and confirm whether everything is ok or not. SCCM replication link analyzer will resolve the SCCM replication issue by itself and provide you handful of reports.

I have received a verification message as given in the below screenshot. Verifying database CM_MEM has valid security scope for Local System Account.

Replication Link Analyser detected Database CM_MEM does not have a valid security scope for Local System Account. Please make sure the Login for Login system account exists and it had a sysadmin role assigned.

Troubleshooting – SCCM Replication Link Analyzer

Manier time SCCM SQL-based troubleshooting is made easy with the SCCM replication link analyzer.

The SCCM Replication Link Analyzer Troubleshooting Report is given below. You can see an error and the name of the rule that failed is Database CM_MEM has a valid security scope for Local System Account.

You have two options to check more details about the issue as explained below.

• View Log
• View Report

You can check the reports and especially the failed rules as you can see in the below screenshot. The report file is in the name of ReplicationAnalysis.htm. I see three rules are failed for the CM_MEM database as per the Replication Link Analyzer.

Rule Name	Parameters	HasRun	Has Passed	Details	Error Message
DoesValidLocalSystemSqlLoginExist	databaseName CM_MEM	TRUE	FALSE	sysadmin role is not granted for login: NT AUTHORITY\SYSTEM.

FIX!- I’m not worried about fixing this issue because this is not causing any issue with SCCM Database replication and I think it’s secure without having login access to the SysAdmin Role.

Replication Link Analyzer to FIX SCCM Secondary Server Replication Issue

The following is another example of a Replication Link Analyzer helping the admin to FIX the SCCM Secondary Server Replication Issue.

Restart the MSSQLServer service on MEMCMSecondary.memcm.com is the message that the replication link analyzer gave to solve the database replication issues.

• Click on the Restart MSSQLServer service link from the replication link analyzer window as shown below.

I have received the error as shown below. It seems the replication link analyzer couldn’t fix the issue. So you will need to go to a secondary server and check and confirm whether the SQL service is running or not.

The object reference is not set to an instance of an object. System.NullReferenceException: Object reference not set to an instance of an object.

If the replication link analyzer didn’t help you to resolve, proceed with the next step in troubleshooting.

Parent Site – Replication Status

Parent Site Replication Status gives us more information about the SCCM database replication within the admin console. You can check the summary of the replication status from the Summary tab.

• Navigate to \Monitoring\Overview\Database Replication
• Click on the Parent Site tab at the bottom of the screen

SQL Server service broker port4022Service Broker SQLMEMCM.memcm.com Port 4022 is still valid.
SQL Server secondary replica_56
SQL Server rolesmsdbrole_MP,smsdbrole_MCS,smsdbrole_DMP,smsdbrole_siteprovider,smsdbrole_siteserver,smsdbrole_AMTSP,smsdbrole_AIUS,smsdbrole_AITool,smsdbrole_extract,smsdbrole_WebPortal,smsdbrole_MPUserSvc,smsdbrole_MPMBAM,smsdbrole_AUDITMBAM,smsdbrole_EnrollSvr,smsdbrole_DViewAccess,smsdbrole_SUP,smsdbrole_CRP,smsdbrole_DWSS,smsdbrole_CRPPfx,smsschm_users,smsdbrole_DmpConnector,smsdbrole_HMSUserAll Configuration Manager SQL Roles still valid.
SQL Server port1433Port 1433 is still valid for SQLMEMCM.memcm.com.
SQL Server certificate expiry date: 2040-02-28, cn=SSB Transport Security CertificateService Broker certificate is still valid for SQLMEMCM.memcm.com.
SQL Server availability group failover_97
Machine certificateExpiry date: 2120-02-04, cn=SQLMEMCM.memcm.comCertificate is still valid for SQLMEMCM.memcm.com.
Firewall ports1433,4022Configuration Manager SQL Server ports 1433,4022 are still active on the Firewall exception.
Database file locationF:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CM_MEM.mdf F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CM_MEM_log.ldfConfiguration Manager Database file location is still valid.
Database file disk free space F:\ 51GBConfiguration Manager Database File-Disk still has enough free space.
Computer accountMEMCM\CMMEMCM$Configuration Manager Site Server Account CMMEMCM.memcm.com is still valid.

Child Site Replication Status

This is a secondary site replication status from the SCCM admin console. You can check the secondary server replication status with the primary server.

• Navigate to \Monitoring\Overview\Database Replication
• Click on the Child Site tab at the bottom of the screen

Computer accountmemcm.com\MEMCMSecondary$Configuration Manager Site Server Account MEMCMSecondary.memcm.com still valid.
Database file disk free spaceC:\ 81GBNo alerts configured. Use Site System properties to configure alerts.
Database file locationC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CM_HS0.mdf C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CM_HS0_log.ldfConfiguration Manager Database file location is still valid.
Firewall ports1433,4022Configuration Manager SQL Server ports 1433,4022 still active on Firewall exception.
Machine certificateExpiry date: 2120-07-09, cn=MEMCMSecondary.memcm.comCertificate is still valid for MEMCMSecondary.memcm.com.
SQL Server availability group failover_0
SQL Server certificateExpiry date: 2040-08-02, cn=SSB Transport Security CertificateService Broker certificate is still valid for MEMCMSecondary.memcm.com.
SQL Server port1433Port 1433 still valid for MEMCMSecondary.memcm.com.
SQL Server rolesmsdbrole_MP,smsdbrole_MCS,smsdbrole_DMP,smsdbrole_siteprovider,smsdbrole_siteserver,smsdbrole_AMTSP,smsdbrole_AIUS,smsdbrole_AITool,smsdbrole_extract,smsdbrole_WebPortal,smsdbrole_MPUserSvc,smsdbrole_MPMBAM,smsdbrole_AUDITMBAM,smsdbrole_EnrollSvr,smsdbrole_DViewAccess,smsdbrole_SUP,smsdbrole_CRP,smsdbrole_DWSS,smsdbrole_CRPPfx,smsschm_users,smsdbrole_DmpConnector,smsdbrole_HMSUserAll Configuration Manager SQL Roles still valid.
SQL Server secondary replica_0
SQL Server service broker port4022Service Broker MEMCMSecondary.memcm.com Port 4022 still valid.

Initialization Details Replication Status

The SCCM Initialization Details Replication Status is also available within the admin console.

• Navigate to \Monitoring\Overview\Database Replication
• Click on the Initialization Details tab at the bottom of the screen

Secondary Site Data100SucceededSecondary
Secondary Site Updates100SucceededSecondary
Secondary_Site_Replication_Configuration100SucceededSecondary

Replication Details Replication Status

You can get Replication Details from the following tab to get the details of secondary and primary sites.

• Navigate to \Monitoring\Overview\Database Replication
• Click on the Replication Details tab at the bottom of the screen

Secondary Site DataSecondary 54/7/2022 9:12:00 AM 4/7/2022 9:08:00 AM4/7/2022 9:08:00 AM4/7/2022 9:12:00 AM
Secondary Site UpdatesSecondary 24/7/2022 9:01:00 AM 4/7/2022 9:00:00 AM4/7/2022 9:00:00 AM4/7/2022 9:01:00 AM
Secondary_Site_Replication_ConfigurationSecondary 24/7/2022 9:07:00 AM 4/7/2022 9:03:00 AM 4/7/2022 9:03:00 AM 4/7/2022 9:07:00 AM

SQL Management Studio – SCCM Replication Troubleshooting

You can use SQL management studio to perform the next level of troubleshooting. SQL management studio will help you to understand the SQL backlog issues.

It also helps you to understand the SCCM replication status of the server in your environment.

1. CAS is in maintenance mode
2. CAS is an Active mode
3. Primary is in maintenance mode
4. Primary is in Active mode

You can run “EXEC SPDiagDRS” from the SQL management studio to get more details about the SCCM replication issue. I have posted about SCCM SQL backlog issues “Troubleshoot SCCM SQL Backlog Issue.”

EXEC SPDiagDRS

I would recommend reading the previously mentioned post to get more helpful details. This post applies to SCCM CB versions (1802, 1806, or 1810).

Monitoring Workspace – Database Replication Node

You can get more details about the replication groups or data which are failing to replication from the SCCM console database replication node.

The following views in the console give more information about the flow between each replication group.

1. Detailed view of the “Initialization Detail” in the console
2. A clear view of “Replication Detail” in the console

SCCM Replication Issue Troubleshooting with Logs

The following logs are used in the SCCM replication issue.

1. Rcmctrl.log – Records the activities of SQL database replication between SCCM sites in the hierarchy.
2. Sender.log – Records the activities in case of manual sync of replication groups. This manual replication can be done as part of troubleshooting. PUB files.

At the time of SCCM 2007, If you needed to perform manual sync between the CAS and Primary server, SCCM admin used.SHA files.

I would recommend reading Sudheesh’s blog about SCCM. PUB file-based manual SQL-based replication. More details –   https://blogs.technet.microsoft.com/sudheesn/2012/10/20/drs-initialization-in-configuration-manager-2012/

SQL Query to Check Replication Link Status

Let’s check the following query to understand the Check Replication Link Status using SQL query using SQL Server Management Studio.

select * from RCM_ReplicationLinkStatus

Site Owner	Site Sending	Site Receiving	Replication ID	Transport Type	Update Time	Status	Status Name	Last Sync Finish Time	Snapshot Applied	Snapshot Applied Time	Link Down Time
MEM	HS0	MEM	47	3	12:29.0	2	Active	05:26.3	1	02:53.7	NULL
MEM	HS0	MEM	48	3	12:29.1	2	Active	11:26.4	1	54:57.0	NULL
MEM	HS0	MEM	49	3	12:29.2	2	Active	07:26.4	1	04:38.1	NULL
MEM	MEM	HS0	47	3	12:29.0	2	Active	09:15.6	NULL	NULL	NULL
MEM	MEM	HS0	48	3	12:29.1	2	Active	59:14.9	NULL	NULL	NULL
MEM	MEM	HS0	49	3	12:29.2	2	Active	11:15.8	NULL	NULL	NULL

The following is the same screenshot of the SQL query and the results to check the SCCM DB Replication between primary and secondary servers.

Author

Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc……………