Upgrade the backend of your SCOM 2019 environment to SQL 2019 CU7

by

Important update 13/10/2020:

Recently, the SQL 2019 CU7 update was taken offline due to several important issues. Although CU8 was released pretty much at the same time, there is no information to be found anymore about the support of SCOM 2019 for SQL 2019.

After testing, it appeared that a clean installation of SCOM 2019 (even with downloading updates in the installer) cannot be installed on SQL 2019 CU8. Although upgrading might still succeed we strongly recommend not to use SQL 2019 as part of your SCOM 2019 backend until Microsoft announces SQL 2019 support again.

 

Good day, fellow SCOM enthusiasts!

It seems the day has finally come that SQL 2019 CU7 was released, making SQL 2019 compatible with SCOM 2019. Of course, this does mean that CU7 needs to be installed to successfully host SCOM’s databases. More about this can be found on SCOM 2019’s What’s New page

The requirement at the SCOM side is easy. Although there are good reasons to be at the latest Update Rollup, SCOM 2019 RTM would suffice for the SQL part.

Anyway, let’s see how this works out in my home lab. This is a simple SCOM installation, comprising of two SCOM management server and a single SQL server hosting both the OperationsManager and OperationsManagerDW databases. Operations Manager is running at 2019 Update Rollup 2. SQL Server is running on an older version of 2017.

Out of curiosity, I’ve installed the Data Migration Assistant to see if any issues were predicted. For the OperationsManager database, it came up with the following potential issue;

There also was one for the OperationsManagerDW database;

As my personal knowledge regarding SQL does not exactly come close to that of SCOM, both of them­­­­­­­­­­ are worth noticing for now, and in case of issues later I can review its content again. However, none of them seem extremely critical to me at this moment. For more information about the actual SQL upgrade: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-version-15?view=sql-server-ver15

Conveniently, this article also comes up with an upgrade checklist:

Pre upgrade Checklist

  • Before upgrading from one edition of SQL Server 2019 (15.x) to another, verify that the functionality you are currently using is supported in the edition to which you are moving.
  • Verify supported hardware and software.
  • Before upgrading SQL Server, enable Windows Authentication for SQL Server Agent and verify the default configuration: that the SQL Server Agent service account is a member of the SQL Server sysadmin group.
  • To upgrade to SQL Server 2019 (15.x), you must be running a supported operating system. For more information, see Hardware and Software Requirements for Installing SQL Server.
  • Upgrade will be blocked if there is a pending restart.
  • Upgrade will be blocked if the Windows Installer service is not running.

It looks like we are currently compliant with all of the above. Note the Windows Authentication part; this one is certainly worth checking upfront.

 

Preparations

  1. First, let’s make sure our SCOM management servers are not actively communicating with our SQL instance. For this, I’m stopping the following services on my SCOM management servers:
    • Microsoft Monitoring Agent
    • System Center Management Configuration
    • System Center Data Access Service
  1. Backup the SQL databases. Especially with the Data Migration Assistant’s findings, it would be comfortable to know we can undo the upgrade. I would recommend using the script available in the following article; https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

 

The actual SQL 2019 upgrade

  1. In order to upgrade, you need the SQL 2019 installation files. Download them from your My Visual Studio, use an evaluation version that can be found here, or obtain it elsewhere at a location of your choice.
  2. Start the setup­ and choose to Upgrade from a previous version of SQL Server.
  3. Choose to use the Evaluation edition or enter your product key. Click Next.
  4. Accept the license terms by clicking Next.
  5. Make sure to select the appropriate instance and click Next.Select instance
  6. Review the information presented and click Next.
  7. If applicable, change the service account details for the SQL Full-text Filter Deamon Launcher In this case I will use the default local account.
  8. For the sake of this example, we will choose to import the current Full-text catalogs. On a later time we can rebuild if desired.
  9. Review the installation steps and click Upgrade.
  10. After waiting patiently, your installation should have finished without errors. Restart your SQL server when the popup box appears.

    That pretty much concludes the SQL 2019 upgrade! As mentioned before, CU7 is a requirement if you want to use SQL 2019 for your SCOM 2019 environment.

 

SQL 2019 CU7 update

  1. If not done already, obtain the installation files at https://www.microsoft.com/en-us/download/details.aspx?id=100809
  2. Start the setup and choose to accept the license terms.
  3. Make sure the appropriate instance name, in my case MSSQLSERVER, is checked and hit Next. Note that only the SQL 2019 instances are shown, and my SQL 2017 instance hosting the SCSM databases is not visible.
  4. Notice that some processes are still running. If you want to prevent your system from rebooting, stop all of them manually. In the situation where my demo environment can be rebooted at all times, I choose the easy way and decide to have a reboot afterwards. Click Next.
  5. Review the installation summary and click Update.
  6. Celebrate the completion of the CU7 update while restarting your SQL server.

 

In addition to restarting your SQL server, now would be a good time to start your SCOM related services as well if these are located on a separate server. In a non-production environment you can reboot the entire system as this might save you a few clicks.

After the reboot, open SQL Server Management Studio and have a look at the current version of to verify the upgrade went as expected. Version 15.0.4063.15 matches with SQL Server 2019 CU7.

For the ultimate test, open your Operations Manager console and have a look around. All seems well! How about you?

Concluding

Compatibility between SCOM 2019 and SQL 2019 has been desired for quite some time by many companies. Finally the SQL 2019 CU7 update was able to make this happen. Although the above scenario was not performed in a very complex environment, the installation itself was not more difficult than expected upfront. The Data Migration Assistant did indicate some potential issues upfront, but fortunately my environment was not affected by them.

Good luck with the upgrade, and let us know how it went!