Issue removing old SQL Server from ConfigMgr

Situation before migration:

1x Primary Site ConfigMgr 2012 R2 no CU (OS Win 2008 R2) – no OS upgrade Support (*)
2x Secondary Sites 2012 R2 no CU
1x SQL Server 2008 R2 SP2 no CU
2’500 Clients worldwide

Goal:

1 Primary Site Current Branch 1511
New OS Windows Server 2012 R2
1 local installed SQL Server
no more Secondary Sites
several DP’s around the world

Most of the things went smoothly but at one point for now we stuck. We’re not able to remove the old remote SQL Server. The Server still appears in the Site System Roles and if we try to remove the Site Database server role (remove is not greyed out) we recieve the error: “The Server cannot be deleted because it contains the following roles:”

We also tried to change the reg keys under SMS_Site_Componets_Manager\Multisite Componet Server\”Name of remote SQL”\Deinstallation Start Time\ set to 1.

 

Solution

Run the following Query in SQL Management Studio in order to find and replace the orphaned relationships.

Change with the corresponding names in < > and run against the SCCM DB :

 *************************************************************

use CM_<sitecode>
declare @ServerName varchar(15)
set @ServerName=’<orphanFQDN>‘

delete from statusmessages where machinename=@ServerName
delete from Summarizer_Components where MachineName like ‘%’+@ServerName+’%’
delete from summarizer_sitesystem where sitesystem like ‘%’+@ServerName+’%’
delete from statusmessageinsstrs where insstrvalue like ‘%’+@ServerName+’%’
delete from sysreslist where servername=@ServerName
delete from sc_sysresuse where nalpath like ‘%’+@ServerName+’%’

 *************************************************************

After that, reboot the Primary Site. This will remove the server from your Management Console and ConfigMgr Database.

(*)
ConfigMgr CB 1602 no supports OS Upgrade from 2008 R2 to 2012 R2. Make sure you uninstall WSUS first.

https://technet.microsoft.com/en-us/library/mt622084.aspx?f=255&MSPPError=-2147217396

Cheers, Al