FIX SCCM Upgrade Issue because of SQL Based Replication

Let me share my experience with the FIX to SCCM Upgrade Issue because of SQL-based replication. My name is Deb, and this is my first post on this blog.

I will try to share my experience with SCCM, OSD, and Task Sequence more frequently. Recently, I faced an issue during the SCCM CB in-place upgrade.

I will try to explain the issue and resolution in this post. SCCM CB upgrade from version 1802 to 1806. Normally, the SCCM upgrade process is straightforward, but this time I faced multiple issues in completing the upgrade.

This blog post will definitely help you to know more about the SCCM Upgrade Issue because of SQL-based replication. You can fix these issues with this blog post.

Patch My PC
Index
FIX SCCM Upgrade Issue because of SQL-Based Replication
Troubleshoot – SCCM Upgrade Issue
SCCM SQL Replication – SCCM Upgrade Issue
Resolution FIX – SCCM Upgrade Issue
Result – SCCM Upgrade Issue Fixed
What is SCCM? PUB File?
FIX SCCM Upgrade Issue because of SQL Based Replication – Table.1

FIX SCCM Upgrade Issue because of SQL Based Replication

In the SCCM environment, we have an SCCM CAS server and multiple primary servers. During the SCCM CB upgrade, we found that the monitoring status was not changing for a few Primary sites and it showed waiting.

I observed the SCCM CB upgrade status for a few hours, like 4-5 hours, but the status was unchanged on the CAS console—monitoring workspace—upgrade status. When we checked the SCCM primary site, I saw the prior site had been upgraded successfully.

Adaptiva
FIX SCCM Upgrade Issue because of SQL Based Replication - Fig.1
FIX SCCM Upgrade Issue because of SQL Based Replication – Fig.1

However, the status of the successful upgrade of the primary server was not reaching the SCCM CAS server. Because of this status issue, the SCCM upgrade was not proceeding further to the next stage.

Troubleshoot – SCCM Upgrade Issue

The first troubleshooting was to go through the console monitoring status. I checked the SCCM DB replication link status on the CAS site. SCCM SQL replication failed between CAS and those primary sites. 

I found that the SCCM DB replication link was in a failed state, which is why the primary server SCCM upgrade status could not reach the CAS server. 

I rebooted the CAS server and SQL server to check whether there was any help with that, but no luck. Still, the issue was unchanged.

FIX SCCM Upgrade Issue because of SQL Based Replication - Fig.2
FIX SCCM Upgrade Issue because of SQL Based Replication – Fig.2

I started troubleshooting the SCCM upgrade issue with SCCM logs. I couldn’t find any errors in the sender.log and other logs. 

I checked the logs on the CAS server, such as sender.log and rcmctrl.log. I found an interesting error message in rcmctrl.log for one of the primary servers. 

Error: Failed to create drs init stored proc SMS_REPLICATION_CONFIGURATION_MONITOR 12/17/2018 1:33:06 PM 4576 (0x11E0)Error: Exception message: [Cannot drop type ‘SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPETEMPbecause it is being referenced by object ‘spUpsertAlwaysOn_Server_MonitoringStatus’. There may be other objects that reference this type.] SMS_REPLICATION_CONFIGURATION_MONITOR 12/17/2018 1:33:06 PM 4576 (0x11E0)Error: Could not create Drs initialization stored procedures or functions for table AlwaysOn_Server_MonitoringStatus, will retry on next cycle. SMS_REPLICATION_CONFIGURATION_MONITOR 12/17/2018 1:33:06 PM 4576 (0x11E0)

SCCM SQL Replication – SCCM Upgrade Issue

Further troubleshooting of SCCM replication was done using the following post, “SQL Replication Troubleshooting Guide.” 

As a next step, I tried to drop. PUB files on the CAS server rcm. Box to forcefully initiate the replication of particular primary server site data. Eg: hardware_inventory_3-<primary site code>.pub. 

The above step didn’t help me resolve the SCCM upgrade issue, and it’s still stuck. I raised a support case with Microsoft CSS because I didn’t have enough time to troubleshoot, and we should complete the SCCM upgrade within the change window.

Moreover, this issue was not mentioned in any TechNet forums, so it seems to be an SCCM bug or a particular problem with our SCCM environment.

Resolution FIX – SCCM Upgrade Issue

Microsoft analyzed the SQL database, logs, and monitoring workspace of our SCCM infrastructure.

Microsoft engineer found that the rcm. The box folder size is also larger because so many files are present in the rcm—box folder, which were not getting removed.

Microsoft also tried to drop. The PUB file got the same error as I mentioned above in the RCMCTRL.log file. Check out the section below to find out what it is. PUB file?

Post analyzing the rcmctrl.log. MS found that the. PUB file could not re-initiate the sync because there was a change in the SQL  database store procedure. It is changed to

SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPETEMP

FIX SCCM Upgrade Issue because of SQL Based Replication - Fig.3
FIX SCCM Upgrade Issue because of SQL Based Replication – Fig.3

How to change it to the original stored procedure name ‘SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPE‘?

  • Open the SQL Management Studio with SQL Admin access
  • Search for the stored procedure called “SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPETEMP”
  • Open the Stored Procedure and change
    • SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPETEMP‘ to ‘SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPE‘. (as you can see from the above screen capture)

Microsoft support engineer took the backup of SQL DB and changed the SCCM stored procedure name to fix the SCCM upgrade issue. 
SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPETEMP‘ to
SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPE‘. 


SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPETEMP‘ to
SCCM_DRS_AlwaysOn_Server_MonitoringStatus_TYPE‘. 

After modifying the SP, replication issues were fixed, the CAS monitoring status was updated to reflect the correct status of the primary server, and the SCCM upgrade was completed.

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.

Result – SCCM Upgrade Issue Fixed

The above change in stored procedure helped activate the replication links between the primary server and CAS. Once the replication link was active, the SCCM 1806 upgrade issue was fixed automatically

FIX SCCM Upgrade Issue because of SQL Based Replication - Fig.4
FIX SCCM Upgrade Issue because of SQL Based Replication – Fig.4

What is SCCM? PUB File?

If you have been an SCCM admin for several years, you might know that SHA files and PUB files are similar.

The SCCM/PUB files are helpful for manually initiating the SQL resource group replication sync without changing anything in SQL DB or running stored procedures like “spDrsSendSubscriptionInvalid.” 

I don’t recommend using “spDrsSendSubscriptionInvalid” because this SP initiates the re-replication between the sites, which may cause a lot of Network Traffic and other issues.

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

Author

Debabrata Pati has more than 7+ years of experience in IT. Skilled in MEMCM, Azure, and Powershell. More than five (5) years of experience in MEMCM (SCCM) administration, OSD, and Troubleshooting for the environment with more than 100K client devices.

5 thoughts on “FIX SCCM Upgrade Issue because of SQL Based Replication”

  1. Hi Deb – It’s very useful and helped me to resolve sccm secondary server upgrade issues … I feel if your secondary server upgrade is not completed as per the console but it got upgraded as per the logs ?? Yes then the way is to check sql replication issues between sccm primary and secondary servers .
    Thank you

    Reply
  2. Hey Deb thanks very much for sharing this. Was able to fix a replication issue caused by a database migration.
    For me the “TYPETEMP” was in 2 spots:
    -SCCM_DRS.spDeleteDeviceClientHealthState
    -SCCM_DRS.spUpsertDeviceClientHealthState

    I had to slightly modify what was provided in your screenshot.

    USE [CM_CAS]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [SCCM_DRS].[spDeleteDeviceClientHealthState](@ChangeTable as SCCM_DRS_DeviceClientHealthState_TYPE READONLY, @SourceSite nvarchar)
    AS
    BEGIN
    SET NOCOUNT ON
    DELETE T FROM DeviceClientHealthState AS T
    JOIN @ChangeTable C ON C.RecordID = C.RecordID
    END

    USE [CM_CAS]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [SCCM_DRS].[spUpsertDeviceClientHealthState](@ChangeTable as SCCM_DRS_DeviceClientHealthState_TYPE READONLY, @SourceSite nvarchar)
    AS
    BEGIN
    SET NOCOUNT ON
    DELETE T FROM DeviceClientHealthState AS T
    JOIN @ChangeTable C ON C.RecordID = C.RecordID
    END

    Reply
  3. Hi Deb,

    THANK YOU for this post. It has helped me resolve a problem I was having with my primary sites not replicating back to their CAS. It wasn’t that particular type that was causing my issue, but the resolution was the same. Thankyou again

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.