SQL Server is obviously a key component to take care of, for a healthy SCCM infrastructure. While SCCM supports a wide variety of SQL Server versions, keeping it up to date may be a good idea to avoid getting out of support.

In this post, we will detail how to upgrade SCCM SQL 2014 to SQL 2017. SQL 2019 is available for a couple of weeks, but it’s still not listed as supported in the SCCM documentation.

If you’re looking to install SQL 2017 from scratch, jump to our post on this topic.

Important Info

At the time of writing, the latest supported SQL Server version is still 2017 with at least Cumulative Update 2

Upgrade SCCM SQL version Requirements

  • Current SQL versions must be one of the following :
    • SQL Server 2008 SP4 or later
    • SQL Server 2008 R2 SP3 or later
    • SQL Server 2012 SP2 or later
    • SQL Server 2014 or later
    • SQL Server 2016 or later
  • Download SQL reporting services
  • Download SQL Management studio
  • The operating system is still under support, which means Windows server 2012 or higher
  • Destination edition of SQL must match or higher than current installation
    • Meaning Standard can be upgraded to Standard but as well to Enterprise.
    • See below link for compatibility table

For more details about requirements, see this Microsoft docs page.

Pre-Upgrade tasks

  • Validate SQL functionality used are supported by the destination edition, which should be for SCCM
  • Make sure Windows authentication is enabled, which should be for SCCM
  • Uninstall SQL Management Studio, as this is no longer included in the SQL installation.
  • Make sure there’s no pending restart
  • Stop SMS_EXECUTIVE to stop all SCCM components
    • Go to Monitoring/System Status/Component Status and select Start/Configuration Manager Service manager
Upgrade SCCM SQL version
  • Select the SMS_Executive component and stop it
Upgrade SCCM SQL version
  • Make sure all components are stopped
Upgrade SCCM SQL version

Upgrade SQL Reporting services 2017

Before upgrading the SQL server, Reporting services must be updated. This component is no longer part of the main installation and comes as a standalone download. Upgrade will be prevented until the current version of SQL Reporting services is still installed on the server.

  • Backup any custom reports from the Report Server.
Upgrade SCCM SQL version
  • In Reporting services configuration manager, backup SQL reporting services Encryption keys
Upgrade SCCM SQL version
  • Install SQL server 2017 reporting services
Upgrade SCCM SQL version
  • Provide the cd-key. It can be found in the install wizard from the SQL 2017 ISO as well as your licensing website.
Upgrade SCCM SQL version
  • Accept license terms
Upgrade SCCM SQL version
  • This will only install the reporting services. It will not connect to current reporting database
Upgrade SCCM SQL version
  • Select the install path and click Install
Upgrade SCCM SQL version
  • Start configuring SQL reporting services
Upgrade SCCM SQL version
  • Configure Reporting services as it was with the previous version.
Important Info

  • Use the same ReportServer database that was used before.
  • Restore Encryption keys
  • Remember links and ports to use the same

Upgrade SCCM SQL version

  • Mount the ISO and select Upgrade
Upgrade SCCM SQL version
  • Provide Product Key or continue as an evaluation. Make sure current and future edition support this upgrade path
Upgrade SCCM SQL version
  • Accept license terms
Upgrade SCCM SQL version
  • Select the desired instance to upgrade
Upgrade SCCM SQL version
  • Check the box to uninstall Reporting services if it was not previously done.
Upgrade SCCM SQL version
  • Confirm features that will be upgraded
Upgrade SCCM SQL version
  • Confirm the instance to be upgraded
Upgrade SCCM SQL version
  • Click Upgrade to begin
Upgrade SCCM SQL version
  • Upgrade completed!
Upgrade SCCM SQL version

Post upgrade tasks

  • SCCM requires a minimum CU2 to be installed on SQL 2017. At the time of writing this article, CU18 is the latest available. Make sure to install the latest cumulative update for SQL server 2017
  • Uninstall unnecessary SQL server components from the previous version, if any are still there like the Management Studio
  • Review SCCM status
  • Review SCCM reports on the web and in console

Hope this helps!

[ratings]

Comments (6)

matt.hall@op.ac.nz

09.03.2021 AT 07:40 PM
I just upgraded our Prod Server from SQL 2012 to SQL 2017, here's some extra notes. As SSRS is now separate and has a separate installer when you install it the instance ID is now called SSRS and not MSSQLSERVER as it was before you started the upgrade. This caused me a couple of issues. The Reporting Services Component was giving a Critical error & after fixing that I got this error as well "Failed to load expression host assembly. Details: Could not load file or assembly 'SrsResources'..." To expand on the 'Important Info' part above when you are setting up SSRS again, you need to hit Apply on each page of the setup tasks, if the links on Web Service URL and Report Manager URL aren't blue you haven't clicked Apply. It's really not obvious as it looks like it's configured but it's not until you hit Apply. After you restore the Encryption key you may have issues, like I did and what Jack says above. To fix it I also had to remove the old entry from the dbo.Keys table in the ReportServer Database. You'll see 2 or maybe 3 entries if you tried to redo it a few times like I did. You can DELETE [ReportServer].[dbo].[Keys] WHERE InstanceName = 'MSSQLSERVER ' (pic here https://www.reddit.com/r/SCCM/comments/kia9ji/upgrade_of_configmgr_sql_from_2016_to_2019/) If you'd done it a few times like me delete the oldest SSRS row as well (row 3,4,5 etc..) You only want one Row in this Table. You know its sorted if the Encryption key pane, changes from Restore to allowing you to click Backup the key again... Secondly to fix the error message, go into ConfigMgr and remove your Reporting Services role and then re-add it straight away. it will look something like this - https://imgur.com/a/9TurZ1I and then when you re-add it, it will say SSRS instead of MSSQLSERVER, this fixes things up and updates the path to the missing files. Give SCCM a while to catch-up, I deleted all the red error messages, rebooted and left it for 30mins and everything came right. Hope this helps someone else...

Joe

02.12.2020 AT 06:34 AM
Hi, Would this scenario work when upgrading from SQL2012R2 to 2017?

Joe

02.12.2020 AT 06:36 AM
heh... sql version requirements were mentioned in the beginning of the article, so I guess yes.

openfile

01.25.2020 AT 05:45 AM
XLSX files are the type of Microsoft Excel spreadsheets from where we can create, edit, view or share files. open xlsx file using the OpenOffice calc. program. For more information, visit open xlsx_ file.

Jack

01.14.2020 AT 07:11 AM
I followed this guide but was forced to delete an old entry from the keys table in the ReportServer database after I had run the upgrade.

Jonathan Lefebvre

01.14.2020 AT 08:54 AM
Hi Jack, My understanding is this can happen with the upgrade of SSRS.Unfortunate, but this fix isn't too bad. thanks for your input Jonathan