How to Upgrade SCCM SQL Server to 2019 | Primary Site Database

Let’s learn how to Upgrade SCCM SQL Server to 2019. In this post, learn the best method to upgrade SCCM Primary Server SQL Database to 2019. The SQL in-place upgrade process for Configuration Manager infra is always tricky because of many dependencies.

SCCM SQL Site Database Server Upgrade to 2019 is the last stage of the SQL in-place upgrade. The CAS server is the first one you must upgrade as part of the SQL server in-place upgrade process.

If you don’t CAS, you can refer to the following posts to help you complete the in-place upgrade of SQL Server 2019. The complete end-to-end SQL server upgrade of your Secondary Server, Reporting Services server, and Primary Server databases.

  1. SQL Server 2019 Upgrade For SCCM Secondary Server (Part 1)
  2. Upgrade SSRS SQL Server Reporting Services to 2019 (Part 2)
  3. Upgrade SCCM Primary Server SQL Database to 2019 (Part 3) – this post.

SCCM supports the in-place upgrade of SQL Server to the following versions. This support includes upgrading SQL Server Express to a newer version of SQL Server Express at secondary sites.

Patch My PC
  • SQL Server 2019
  • SQL Server 2017
  • SQL Server 2016
  • SQL Server 2014

You can upgrade from SQL Server 2014 (12.x), SQL Server 2016 (13.x), and SQL Server 2017 (14.x) to SQL Server 2019 (15.x). The SQL database keeps its existing cardinality estimation level if it’s at the minimum allowed for that instance of SQL Server.

Download SQL Server 2019 and Cumulative Update

Let’s download SQL Server 2019 enterprise or standard version and Cumulative Update before upgrading and copy it to the relevant SQL server (SCCM secondary server).

You can download the SQL Server 2019 Enterprise, Standard, or Express edition from VLSC (volume licensing). The product key is pre-populated when you install/upgrade the SQL server.

Download SQL Server 2019 and Cumilative Update
Download SQL Server 2019 and Cumulative Update

You can download the SQL Server 2019 Latest Cumulative Update from the following link. https://www.microsoft.com/en-us/download/details.aspx?id=100809

Download SQL Server 2019 and Cumulative Update
Download SQL Server 2019 and Cumulative Update

Prepare for SQL Server 2019 in-place Upgrade

The following are some of the preparation work you need to check and confirm before upgrading the primary server SQL database to 2019.

  1. Secondary server database is already upgraded to 2019.
  2. SQL Server Reporting Sevices (SSRS) 2014 or prior is removed or uninstalled.
  3. SSRS 2019 is installed, configured, and working.
  4. Take the snapshot of the SQL server that you are going to upgrade to 2019.
  5. Take the backup of all databases related to SCCM.
  6. The logged-in user must have local admin and SQL admin permission.
  7. Check and Confirm SCCM SQL based database replication (Link is Active) is working fine.
  8. Test the SQL Server upgrade 2019 scenarios in pre-production or staging environment before production.
  9. Confirm SQL Server 2019 support statement for ServiceNow and other 3rd party applications connected to SCCM DB for automation and asset inventory.

Stop and Disable SCCM Services on Primary Server

This is another pre-upgrade task that you need to complete before going into the next step. Ensure that the SCCM (ConfigMgr) services are disabled and stopped before upgrading. You can open services.msc and search for the services starting with the name SMS and stop + disable those services.

The following are the services that you are supposed to stop and disable during Upgrade SCCM SQL Server to 2019. It’s recommended to perform this action for primary and remote SQL (if you have one) servers.

  • SMS Agent Host
  • SMS_EXECUTIVE
  • SMS_NOTIFICATION_SERVER
  • SMS_SITE_BAKUP
  • SMS_SITE_COMPONENT_MANAGER
  • SMS_SITE_VSS_WRITER
Stop and Disable SCCM Services on Primary Server
Stop and Disable SCCM Services on Primary Server

Upgrade SCCM SQL Server to 2019 | Primary Site Database Upgrade

Go through the process of SQL Server upgrade to 2019 of an SCCM primary server site database. Before proceeding, ensure that all the pre-upgrade SQL tasks mentioned above are taken care of or completed.

Login to the SQL Server with the user account with local admin and SQL admin permission. Follow the steps below to start the upgrade SCCM SQL server to 2019.

  • Click on Setup.exe.
  • Click on Installation tab from SQL Server Installation Center.
  • Click on Upgrade from a previous version of SQL Server.
How to Upgrade SCCM SQL Server to 2019 | Primary Site Database Upgrade
How to Upgrade SCCM SQL Server to 2019 | Primary Site Database Upgrade

The SQL enterprise version product key is pre-populated, and this is the product key you need to use during the SQL Server Reporting Services (SSRS) 2019 install.

  • Click on the Next button.
How to Upgrade SCCM SQL Server to 2019 | Primary Site Database Upgrade
How to Upgrade SCCM SQL Server to 2019 | Primary Site Database Upgrade

Upgrade to SQL Server 2019 License Terms page to install SQL Server 2019, and you must accept the Microsoft Software License Terms.

  • Enable “I accept the license terms and privacy Statement” option.
  • Click on the Next button to continue.
How to Upgrade SCCM SQL Server to 2019 | Primary Site Database Upgrade
How to Upgrade SCCM SQL Server to 2019 | Primary Site Database Upgrade

SQL Server Upgrade to 2019 setup a set of Global rules to identify problems that might occur when you install SQL Server Setup support files. The features must be corrected before the Setup can continue.

NOTE! Normally, this global rules page will be skipped and automatically proceed to the next page if all the Global Rules are passed. The following is the list of global rules for SQL Server upgrade to 2019.

  • Setup Administrator.
  • Setup Account Privileges.
  • Restart Computer.
  • Windows Management Instrumentation (WMI) service.
  • Consistency validation for SQL Server Registry keys.
  • Log path names to files on SQL Server installation media.
  • SQL Server Setup product incompatibility.
  • Edition WOW64 platform.
How to Upgrade SCCM SQL Server to 2019 | Primary Site Database Upgrade
How to Upgrade SCCM SQL Server to 2019 | Primary Site Database Upgrade

Microsoft Update Option During SQL Server 2019 in-place Upgrade

Let’s look into the Microsoft Update Option During SQL Server 2019 in-place Upgrade. Most SQL servers might not have an internet connection, even via proxy.

Microsoft Update page gives you an option to use Microsoft Update to check for important updates. I don’t recommend using this option, even though Microsoft recommends it.

  • Don’t enable the following option – Use Microsoft Update to check for updates (recommended).
  • Click on the Next button to continue.
Microsoft Update Option During SQL Server 2019 in-place Upgrade
Microsoft Update Option During SQL Server 2019 in-place Upgrade

SQL Server 2019 in-place Upgrade – List of Tasks

If an update for SQL Server Setup is found and specified to be included, the update will be installed. The following is the list of tasks checked during the upgrade SCCM SQL Server to 2019 (SCCM Primary Site Database).

  • Scan for product updates – Completed.
  • Download Setup files – Skipped.
  • Extract Setup Files – Skipped.
  • Install Setup Files – Completed.
SQL Server 2019 in-place Upgrade - List of Tasks
SQL Server 2019 in-place Upgrade – List of Tasks

Specify Instance – How to Upgrade SCCM SQL Server to 2019 | Primary Site Database

Let’s check Specify Instance in the context of how to Upgrade SCCM SQL Server to 2019 | Primary Site Database. This is the page where you can specify the instance of SQL Server to modify.

Select the instance of SQL server to upgrade. If you want to upgrade only shared features, select the “Upgrade Shared Features” option. I don’t recommend changing anything from this page.

Instance to Upgrade – MSSQLServer.

  • Click on the Next button to continue.
Specify Instance - Upgrade SCCM SQL Server to 2019 | Primary Site Database
Specify Instance – Upgrade SCCM SQL Server to 2019 | Primary Site Database

Warning if you Miss SSRS 2014 Uninstallation

To avoid the SSRS uninstall warning, follow the steps mentioned to uninstall the SQL Server Reporting Services 2014 or prior if that is already installed.

Upgrading SQL Server will uninstall reporting Services.

Warning if you Miss SSRS 2014 Uninstallation
Warning if you Miss SSRS 2014 Uninstallation

Select Features before SCCM Primary Server SQL DB Upgrade to 2019

Let’s select Features before SCCM Primary Server SQL DB Upgrade to 2019. If you failed to remove the SSRS 2014 (or SQL SSRS 2012) version prior to upgrading SCCM primary server SQL DB to 2019, just before the Select feature page, you get the warning mentioned above page.

Since we have already completed the uninstallation of SSRS 2014 as per the Upgrade SSRS SQL Server Reporting Services To 2019 guide, there is no warning screen, as you see above.

  • Click on the Next button from Select Features page to continue.
Select Features before SCCM Primary Server SQL DB Upgrade to 2019
Select Features before SCCM Primary Server SQL DB Upgrade to 2019

Upgrade to SQL Server Instance Configuration

The Instance Configuration page allows you to specify the name and instance ID for the instance of the SQL server. Normally, instance ID becomes part of the installation path.

I keep the default value because that is configured for the SCCM primary server. Click on the Next button to continue.

You can confirm the SQL Server Directory and Analysis Services Directory details from the SQL instance configuration page in the wizard.

Upgrade to SQL Server Instance Configuration for SCCM Primary server SQL DB
Upgrade to SQL Server Instance Configuration for SCCM Primary server SQL DB

SQL Server Configuration for In-place upgrade to 2019

SQL Server Configuration page in the wizard gives you an opportunity to check and confirm the service accounts and collation configuration.

For the SCCM primary server SQL DB upgrade scenario, you don’t have to change anything here on the page, and there is no option to change the SCCM collation settings.

  • Click on the Next button from Server Configuration page to continue.
SQL Server Configuration for In-place upgrade to 2019
SQL Server Configuration for In-place upgrade to 2019

Full-text Upgrade for SCCM SQL Database in-place Upgrade to 2019

The full-text upgrade page gives you an option to choose Full-Text upgrade options. You perform an in-place upgrade of SQL Server DB engine to 2019, and the user interface allows you to choose one of the following full-text upgrade options.

Import – Full-text catalogs are imported. Typically, import is significantly faster than rebuild. For example, import runs about 10 times faster than rebuild when using only one CPU.

  • I have selected the Import option because that seems to be the best option as per my reading especially for SCCM primary server scenario.
Full-text Upgrade for SCCM SQL Database in-place Upgrade to 2019
Full-text Upgrade for SCCM SQL Database in-place Upgrade to 2019

Feature Rules of SQL Server 2019 Upgrade Process

The Feature Rules page evaluates by running the rules to determine if the process will be blocked. It’s recommended that you run the Upgrade Advisor to analyze your prior installations completely.

All the 27 feature rules are passed and completed successfully. The more detailed sample report is uploaded to AnoopCNair/SQL-Server-2019-Upgrade-Checklistlist: Gives the details of SQL Server 2019 Upgrade Config Check (github.com).

Feature Rules of SQL Server 2019 Upgrade Process
Feature Rules of SQL Server 2019 Upgrade Process

UpgradeVerify SQL Server 2019 Features to be Upgraded

You can verify SQL Server 2019 features to be upgraded from the ready to upgrade page. You can check the full list of features from C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\ Data and Time -ConfigurationFile.ini.

The sample ConfigurationFile.ini is already uploaded to the Github repository.

NOTE! – Before starting the SQL Server 2019 upgrade process for SCCM primary server DB, ensure you have the snapshot of the server and backup of SCCM and SQL DBs.

  • Click on the Upgrade button to start the upgrade process.
Upgrade - Verify SQL Server 2019 Features to be Upgraded
Upgrade – Verify SQL Server 2019 Features to be Upgraded

Monitor SQL Server 2019 in-place Upgrade of SCCM Primary Server

Let’s see how to monitor SQL Server 2019 in-place Upgrade of SCCM Primary Server. One option is to look at the GUI “Upgrade to SQL Server 2019” wizard during the SQL upgrade to 2019.

Monitor SQL Server 2019 in-place Upgrade of SCCM Primary Server
Monitor SQL Server 2019 in-place Upgrade of SCCM Primary Server

The other option is to check the SQL upgrade log files located in the following folder in your C drive. There are a lot of log files, as you can see in the below screenshot.

  • C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20220315_162159 (the folder name at the last of the log file location indicates Date and TIme of the upgrade)
Monitor SQL Server 2019 in-place Upgrade of SCCM Primary Server
Monitor SQL Server 2019 in-place Upgrade of SCCM Primary Server

Successfully Upgraded the SCCM Primary SQL Server to 2019

The SCCM Primary SQL Server to 2019 was successfully upgraded, as you can see below. You can check the detailed status of the upgrade to 2019 from the following file. Your SQL Server 2019 upgrade was completed successfully with product updates.

  • C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20220315_162159\SystemConfigurationCheck_Report.htm
Successfully Upgraded the SCCM Primary SQL Server to 2019
Successfully Upgraded the SCCM Primary SQL Server to 2019

SQL Server 2019 Update Failed

The SQL Server 2019 Update Failed because of a pending reboot. I got the error when I tried to install SQL Server 2019 Cumulative Update.

  • Restart the SQL server before you can install CU 14 for SQL Server 2019 CU 15.
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB SQL Server 2019 Update Failed
SQL Server 2019 Update Failed

Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB

The CU 15 is the latest CU for SQL Server 2019, and I have installed that on the secondary SQL server. You can follow the same method to install SQL server 2019 cumulative update 15 on SCCM primary server SQL DB.

NOTE! – SQL Server 2019 CU5 is the minimum requirement for SQL Server 2019 as it resolves an issue with scalar UDF inlining.

Download – CU Cumulative Update Package 15 for SQL Server 2019 – KB5008996 from https://www.microsoft.com/en-us/download/details.aspx?id=100809

  • Double click on SQLServer2019-KB5008996-x64.exe hotfix pack for SQL Server 2019 using the user account that has local admin and SQL admin permissions.

The SQL Server 2019 update to CU 15 failed initially, as explained in the above section of the post. After the restart, the SQL Server 2019 Update page automatically skipped to the License Terms page because all the rules had been passed.

Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB

To install the CU for SQL Server 2019, you must accept the Microsoft Software License Terms. Enable the checkmark on I accept the license terms and privacy statement.

  • Click on the Next button to continue.
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB

You can click on the Next button to continue.

Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB

Check files in Use page helps to check services and applications are using files that setup needs to complete the installation. The files in use check completed without any error or warning.

  • Click on the Next button to continue.
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB

Ready to update SQL Server 2019 to CU 15. You can verify the features to be updated if that is something important for you.

  • Click on the Update button to start the SCCM Primary server SQL DB 2019 Update to CU 15 process.
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB

Update progress screen gives you the status bar progress of SQL Server 2019 update to CU15. You can also refer to the log files to get more details on the background process.

  • C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20220315_162159\
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB

Your SQL Server 2019 update operation is completed as per the Complete page. There is an interesting Computer restart required message on the complete page of the CU 15 update.

NOTE! – Message: One or more affected files have operations pending. You must restart your computer after the setup process is completed.

Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB
Install SQL Server 2019 CU15 on SCCM Primary Server SQL DB

Restarted the server and the full update summary is available at C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20220315_181548\SystemConfigurationCheck_Report.htm.

Upgrade SQL Server Management Studio to 2019

I recommend upgrading the SQL Server Management Studio (SSMS) to 2019. I have a comprehensive guide about SQL Server Management Studio (SSMS) with Azure Data Studio Installation Upgrade Process.

SQL Server Version Verification

Let’s check the SQL Server version using the following SQL query from SSMS. It’s Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) – 15.0.4198.2 (X64).

SELECT @@VERSION AS 'SQL Server Version'
SQL Server Version Verification
SQL Server Version Verification

Post SQL Server 2019 in-place Upgrade Tasks

One of the post SQL Server 2019 in-place Upgrade Tasks for the SCCM primary server is to enable and start all the SCCM related services.

  • SMS Agent Host
  • SMS_EXECUTIVE
  • SMS_NOTIFICATION_SERVER
  • SMS_SITE_BAKUP
  • SMS_SITE_COMPONENT_MANAGER
  • SMS_SITE_VSS_WRITER
Post SQL Server 2019 in-place Upgrade Tasks
Post SQL Server 2019 in-place Upgrade Tasks

Database Replication Issue after SQL Server 2019 Upgrade

I have seen the database Replication Issue after the SQL Server 2019 Upgrade of the primary server database. The site hierarchy node shows the replication is failed between the primary server and secondary server.

Database Replication Issue after SQL Server 2019 Upgrade
Database Replication Issue after SQL Server 2019 Upgrade

The database replication analyzer from \Monitoring\Overview\Database Replication indicated a problem with the SQL services on the Secondary server. A quick restart of the secondary server helped to resolve the issue.

Read MoreFIX SCCM SQL Based Database Replication Failure Between CAS Primary

Database Replication Issue after SQL Server 2019 Upgrade
Database Replication Issue after SQL Server 2019 Upgrade

Validation on SCCM Primary Site Side after SQL Server 2019 Upgrade

Let’s check how to validate SQL-based replication between SCCM primary and secondary servers after the SQL Server upgrade to 2019. After the restart of the secondary server, the database replication between secondary and primary is back online (Link is Active).

  • You can navigate to \Monitoring\Overview\Site Hierarchy in the SCCM admin console to confirm whether the SQL-based replication is working fine.
Validation on SCCM Primary Site Side after SQL Server 2019 Upgrade
Validation on SCCM Primary Site Side after SQL Server 2019 Upgrade

Author

Leave a Comment

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