MECM SQL Server Upgrade from 2012 to 2016
Let's see how we can upgrade SQL version from 2012 to 2016 on the SCCM server.
To find out SQL server version you can run query - select @@ version. This should show you the SQL server version and the latest CU Installed.
Requirement to upgrade SCCM SQL version.
1. SQL server supported
SQL Server 2019 : Standard, Enterprise - [CAS, Primary, Secondary]
SQL Server 2017: Standard, Enterprise - [CAS, Primary, Secondary]
SQL Server 2016: Standard, Enterprise - [CAS, Primary, Secondary]
SQL Server 2014: Standard, Enterprise - [CAS, Primary, Secondary]
SQL Server 2012: Standard, Enterprise - [CAS, Primary, Secondary]
SQL Server 2017 Express - [Secondary]
SQL Server 2016 Express - [Secondary]
SQL Server 2014 Express - [Secondary]
SQL Server 2012 Express - [Secondary]
2. Download SQL Reporting Services
3. Download SQL Management studio
Step 1 - Pre-Upgrade Task
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.
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
Confirm all components are stopped.
Step 2 - Upgrade SQL Reporting Services 2017
Backup any custom report from report server.
In Reporting services configuration manager back SQL Encryption Keys
Before upgrading to SQL Server uninstall reporting service point role and check srsrpsetup.log and wait until role is uninstalled.
Uninstall SQL Reporting services from Add/Remove Program features
Step 3 - Upgrade MECM SQL version
Mount ISO file and select upgrade from a previous version of SQL server
Select product key or continue as an evaluation and click on Next
Accept all License terms and click on Next
Select the instance to upgrade and click on Next
Select the features to upgrade and click on Next
Select the default/custom instance and click on Next
Click on Upgrade to begin installation and click on Next
Upgradation completed.
Step 4 - Re-install the SQL Reporting services
Download the latest version of Microsoft SQL server 2017 reporting service from here.
Run the reporting service and Install Reporting Services
Accept the license and click Next
Click Next
Select option Install Reporting Service only
Click Next
Select Configure Report Server
Connect to the report Server instance by clicking connect
Click on Database and select option Change Database
We have removed the reporting service and we will create a new report server DB. Select option create a new report server DB and click on next
Give the credentials to connect to the database server and click Next
Leave the setting as default and click Next
Specify credentials that report server and will use to connect report server DB and click Next
All steps should show as success and click Finish.
Specify the Web URL and click Apply
Select Web Portal URL and click Apply
Step 5 - Re-install Reporting Service point role
Let's see how to re-install Reporting service point role, open MECM console. Go to Administration --> overview --> Site configuration -->Servers and site system roles
Choose the server on which you want to install reporting service point role and Right click and choose Add site System roles
On the system Role selection page, choose Reporting Service point. Click Next
Provide the credentials using SQL Reporting Services will connect to configuration manager site DB. click verify and make sure see successfully verified message and click Next
Click close
Step 6 - Re- Start the service or restart the server once
We have stopped the Configuration manager services. Now that we have completed the SQL Upgrade and we can start the service or reboot the server.