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
- Select the SMS_Executive component and stop it
- Make sure all components are stopped
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.
- In Reporting services configuration manager, backup SQL reporting services Encryption keys
- Install SQL server 2017 reporting services
- Provide the cd-key. It can be found in the install wizard from the SQL 2017 ISO as well as your licensing website.
- Accept license terms
- This will only install the reporting services. It will not connect to current reporting database
- Select the install path and click Install
- Start configuring SQL reporting services
- 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
- Provide Product Key or continue as an evaluation. Make sure current and future edition support this upgrade path
- Accept license terms
- Select the desired instance to upgrade
- Check the box to uninstall Reporting services if it was not previously done.
- Confirm features that will be upgraded
- Confirm the instance to be upgraded
- Click Upgrade to begin
- Upgrade completed!
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]Share this Post
6 Comments on “How to upgrade SCCM SQL version”
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…
Hi, Would this scenario work when upgrading from SQL2012R2 to 2017?
heh… sql version requirements were mentioned in the beginning of the article, so I guess yes.
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.
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.
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