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.
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.
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)
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!'
PRINT N'PASS: CLR is enabled.'
DECLARE @repltable TABLE (
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!'
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!'
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!'
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!'
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!'
PRINT N'PASS: Service broker priority is set.'
And found that the Max Text repl size is incorrect…
I changed the size from the default to 2147483647
After running the query now you can see it passes and the upgrade also completes successfully.
Feel free to experiment with solution and add or correct me in any of the steps.