System Center Configuration Manager – Upgrade to 1902 Error – “SQL Server Configuration for site upgrade”

The issue:

After trying to upgrade a client Configuration Manager Environment from 1810 to 1902 the upgrade failed with the error “SQL Server Configuration for site upgrade”. The first thing that crosses your mind is the SQL version is wrong or the memory is not configured correctly. But in my case SQL was SQL Server 2016 Standard which is well supported and memory was configured correctly.

The Investigation

Upon further investigation, you will look in the Pre Requisite Log file (C:\ConfigMgrPrereq.log) and see that not all Errors are highlighted. Sometimes an error will show up a few lines before the highlighted error.

The Solution

Following another technet blog (https://social.technet.microsoft.com/Forums/en-US/53196fc0-ea14-47c6-b1ab-80f21fc6e070/1810-hotfix-rollup-kb4486457-prerequisite-check-failed-failed-sql-server-configuration-for)

I ran the below SQL Server command to simulate the pre requisite check on the DB.

SET NOCOUNT ON

     DECLARE @dbname NVARCHAR(128)
  
     SELECT @dbname = sd.name FROM sys.sysdatabases sd WHERE sd.dbid = DB_ID()
  
     IF (@dbname = N'master' OR @dbname = N'model' OR @dbname = N'msdb' OR @dbname = N'tempdb' OR @dbname = N'distribution' ) BEGIN
     RAISERROR(N'ERROR: Script is targetting a system database.  It should be targeting the DB you created instead.', 0, 1)
     GOTO Branch_Exit;
     END ELSE
     PRINT N'INFO: Targeted database is ' + @dbname + N'.'
  
     PRINT N'INFO: Running verifications....'
  
     IF NOT EXISTS (SELECT * FROM sys.configurations c WHERE c.name = 'clr enabled' AND c.value_in_use = 1)
     PRINT N'ERROR: CLR is not enabled!'
     ELSE
     PRINT N'PASS: CLR is enabled.'
  
     DECLARE @repltable TABLE (
     name nvarchar(max),
     minimum int,
     maximum int,
     config_value int,
     run_value int )
  
     INSERT INTO @repltable
     EXEC sp_configure 'max text repl size (B)'
  
     IF NOT EXISTS(SELECT * from @repltable where config_value = 2147483647 and run_value = 2147483647 )
     PRINT N'ERROR: Max text repl size is not correct!'
     ELSE
     PRINT N'PASS: Max text repl size is correct.'
  
     IF NOT EXISTS (SELECT db.owner_sid FROM sys.databases db WHERE db.database_id = DB_ID() AND db.owner_sid = 0x01)
     PRINT N'ERROR: Database owner is not sa account!'
     ELSE
     PRINT N'PASS: Database owner is sa account.'
  
     IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_trustworthy_on = 1 )
     PRINT N'ERROR: Trustworthy bit is not on!'
     ELSE
     PRINT N'PASS: Trustworthy bit is on.'
  
     IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_broker_enabled = 1 )
     PRINT N'ERROR: Service broker is not enabled!'
     ELSE
     PRINT N'PASS: Service broker is enabled.'
  
     IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_honor_broker_priority_on = 1 )
     PRINT N'ERROR: Service broker priority is not set!'
     ELSE
     PRINT N'PASS: Service broker priority is set.'
  
     PRINT N'Done!'
     Branch_Exit: