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:

And found that the Max Text repl size is incorrect…

I changed the size from the default to 2147483647

Default
Adjusted

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.

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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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