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.