System Center Configuration Manager – “Configure SQL Server Service Broker Failed”

The Issue

Recently I installed the Latest Tech Preview in my lab environment and while running an In-Console update I received an error “Configure SQL Server Service Broker – Failed”.

The Investigation

If you watch a video Steve Rachui posted some time ago, there are some valuable tips for which logs to review and the whole process.

https://msit.microsoftstream.com/video/80b01841-8572-4185-a72f-1468098ba0c8

So for me the 4 most important log files were
– ConfigMgrSetup.log
– CMUpdate.log
– SMSProv.log
– DMPDownloader.log

After reviewing the CMUpdate.log file a found the below error

If you try to run this in SQL you get the below error which is more descriptive than the log. *** DO NOT JUST RUN ANY SQL COMMAND IF YOU DO NOT KNOW WHAT THE CONSEQUENCES ARE

Now as you can see the actual error is : “Route is not defined for target site with service name ConfigMgrRCM_SiteSS1”

I had recently installed a secondary site and deleted it from the console without cleaning up the role. If the Secondary site is still on the network always use the “Uninstall” option in the console to avoid issues.

So now that I deleted it I ran into an issue here the secondary site got stuck in the “deleting” state as described here: https://social.technet.microsoft.com/Forums/en-US/11151893-5833-40c7-a71c-134365043c27/secondary-site-stuck-quotdeletingquot?forum=configmanagergeneral

So how do we fix that? By using the Hierarchy Maintenance Tool!
https://docs.microsoft.com/en-us/configmgr/core/servers/manage/hierarchy-maintenance-tool-preinst.exe