SCCMSQL Server

SCCM SQL Server Upgrade from 2014 to 2017

This post covers the steps to upgrade your SQL server installed for SCCM. I will show you how to upgrade the SQL server 2014 to 2017 on the SCCM server.

This will be an in-place upgrade of SQL server 2014 to the version 2017 on SCCM server. The same steps can help you with upgrading SQL server to any other supported version as well.

Lab Setup – In my test setup, I am running Configuration Manager version 1910. The site database is hosted on SQL serverĀ 2014 enterprise edition. I know I am running an older version of SQL server on my SCCM server and therefore I am upgrading it.

To find out the SQL Server version you can run the query – Select @@Version. This should show you the SQL Server version and the latest CU installed. To know different ways to find the SQL Server version, read this post.

Upgrade SQL Server 2014 to 2017

SCCM SQL Server Upgrade from 2014 to 2017

The high level steps for upgrading the SQL Server include

  • Before you Upgrade
  • Uninstall Reporting Services Point role
  • Uninstall SQL Reporting services
  • Delete Report server Database and Log files
  • Stop Configuration Manager Services
  • Upgrade SQL 2014 to 2017
  • Re-Install the SQL Reporting Services
  • Configure the SQL Reporting Services
  • Re-install Reporting Services Point role
  • Start SCCM services.

Step 1 – Before you upgrade your SQL Server

  1. First of all before upgrading the SQL server in your production environment, it is advised that you test the upgrade on a test environment.
  2. Make sure you backup your CM database and your SSRS database before you begin this process. Ensure you also backup your custom reports.
  3. Before upgrading from one edition of SQL Server to another, verify that the functionality you are currently using is supported in the edition to which you are moving. For example, I am upgrading SQL server 2014 to 2017 which is supported by Configuration Manager 1910. Where as SQL Server 2019 is not supported by ConfigMgr 1910 yet.
  4. The operating system should be compatible on which you are performing an SQL upgrade. Verify the hardware and software requirements before installing SQL Server 2017.
  5. Note that SQL Server 2017 is only available for 64-bit platforms. Hence cross-version instances of SQL Server 2017 are not supported.
  6. Before you Upgrade SQL Server 2014 to 2017, enable Windows Authentication for SQL Server Agent.
  7. If you are running evaluation edition of SQL server then upgrade to SQL 2017 is not possible.

Step 2 – Uninstall Reporting Services Point role

To uninstall Reporting Service Point role from Configuration Manager

  • Open the Configuration Manager console.
  • Go to Administration > Site Configuration > Servers and Site System Roles.
  • Select the server that has the Reporting services point role. Right click and choose Remove Role.

Uninstall Reporting Services Point role

Open the srsrpsetup.log file and wait until the role is uninstalled.

Uninstall Reporting Services Point roleHere is what your log file should look like post removing reporting services point role.

SMSSRSRP Setup Started....
Parameters: C:\Program Files\Microsoft Configuration Manager\bin\x64\rolesetup.exe /deinstall /siteserver:CORPSCCM SMSSRSRP 0
Deinstalling the SMSSRSRP
CTool::RegisterManagedBinary: run command line: "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe" "C:\Program Files\SMS_SRSRP\srsserver.dll" /unregister
CTool::RegisterManagedBinary: Registered C:\Program Files\SMS_SRSRP\srsserver.dll successfully
Unregistered DLL C:\Program Files\SMS_SRSRP\srsserver.dll
No versions of SMSSRSRP are installed.  Returning Success.
Removing SRS Reporting Point registry branch.
Deinstallation was successful.
~RoleSetup().

Step 3 – Uninstall SQL Reporting services

In this step, we will uninstall the SQL Reporting services. This procedure is also covered in one of my post here.

  • On Server with the SQL Reporting Services installed, open the Control Panel.
  • Navigate to Programs and Features. Select Microsoft SQL Server 2014 (64-bit).
  • Click the Uninstall/Change option.

Uninstall SQL Reporting services

From the list of options, click Remove.

Uninstall SQL Reporting services

Click Next.

Uninstall SQL Reporting services

From the list of features, select Reporting Services – Native. Click Next.

Uninstall SQL Reporting services

On the Ready to Remove page, click Remove.

Uninstall SQL Reporting services

The SQL Server 2014 reporting services uninstallation is complete. Click Close.

Uninstall SQL Reporting services

Step 4 – Delete Report Server Database and Log files

Connect to the SQL server using SQL Server Management Studio. You will find two databases that you need to delete – ReportServer and ReportServerTempDB.

Right click ReportServer and click Tasks > Detach.

Delete Report Server Database and Log files

On the Detach Database window, check the box for Drop Connections and Update Statistics. Click OK.

Delete Report Server Database and Log files

Next, detach the ReportServerTempDB using the same steps as above.

Delete Report Server Database and Log files

Select Drop Connections and Update Statistics. Click OK. Close the SQL management studio.

Delete Report Server Database and Log filesAfter performing the above steps, you must delete ReportServer and ReportServerTempDB DB files and log files.

To do that go to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA. Select ReportServer or ReportServerTempDB files and log files, delete them.

Step 5 – Stop Configuration Manager Services

Before you upgrade the SQL server, you must stop the Configuration Manager services. Go to Services console and stop the below services.

Stop Configuration Manager Services

Step 6 – Upgrade SQL Server 2014 to 2017

To upgrade or perform in-place upgrade of SQL Server 2014 to SQL Server 2017.

  • Copy over the SQL Server 2017 media to the server.
  • You can either mount the media or extract the SQL 2017 setup files to a folder.
  • Right click Setup.exe and Run as administrator.

Upgrade SQL Server 2014 to 2017

On the SQL Server Installation Center, click Installation. From the right pane, select Upgrade from a previous version of SQL Server.

Upgrade SQL Server 2014 to 2017

Accept the license terms and click Next.

Upgrade SQL Server 2014 to 2017

One of the Rule failed here because a Server restarted is required. If you see this, restart your Server.

Upgrade SQL Server 2014 to 2017

After the server restart, stop all the Configuration Manager Services because these services would be running post a server restart.

Back to SQL server 2017 upgrade, the global rules check completed successfully. On the Select Instance page, click Next.

Upgrade SQL Server 2014 to 2017

Select the SQL features to upgrade and click Next.

Upgrade SQL Server 2014 to 2017

Click Next.

Upgrade SQL Server 2014 to 2017

On the Ready to Upgrade page, click Upgrade.

Upgrade SQL Server 2014 to 2017

The SQL server upgrade is complete. We have successfully upgraded SQL Server 2014 to version 2017. Click Close.

Upgrade SQL Server 2014 to 2017

Step 7- Install SQL Server 2017 Cumulative Update

If you read the supported SQL server versions for ConfigMgr 1910, it clearly states that you can use SQL Server 2017 version with cumulative update version 2 or higher. So do not forget to install the cumulative update when you upgrade to SQL 2017.

You can find the latest cumulative updates for SQL Server 2017 here. While writing this post, the cumulative update 18 KB4527377 for SQL Server 2017 is the latest one.

Download the cumulative update on the SQL server. Right click the update and click Run as administrator.

Accept the license terms and click Next.

Install SQL Server 2017 Cumulative Update

On the Select Features page, Click Next.

Install SQL Server 2017 Cumulative Update

Click Update.

Install SQL Server 2017 Cumulative Update

The SQL Server 2017 cumulative update 18 KB4527377 installation is complete. Click Close.

Install SQL Server 2017 Cumulative Update

Step 8 – Re-Install the SQL Reporting Services

Download the latest version of Microsoft SQL Server 2017 Reporting Services from here. Run the Reporting Services and on the welcome screen click Install Reporting Services.

Re-Install the SQL Reporting Services

Accept the license terms and click Next.

Re-Install the SQL Reporting Services

Click Next.

Re-Install the SQL Reporting Services

Don’t change anything here, click Install.

Re-Install the SQL Reporting Services

Step 9 – Configure the SQL Reporting Services

The Reporting Services installation is complete. However it is not configured yet, so click Configure Report Server.

Re-Install the SQL Reporting Services

Connect to the Report server instance by clicking Connect.

Configure the SQL Reporting Services

Click Database and under Current Report Server Database, click Change Database.

Configure the SQL Reporting Services

Since we have removed the reporting services database earlier, let’s create a new report server database. Select Create a new report server database and click Next.

Configure the SQL Reporting Services

Specify the credentials to connect to the database server. Click Next.

Configure the SQL Reporting Services

Leave the settings to default here and click Next.

Configure the SQL Reporting Services

Specify the credentials that report server will use it to connect to report server database. Click Next.

Configure the SQL Reporting Services

Ensure all the steps show as Success. Click Finish.

Configure the SQL Reporting Services

Select Web Service URL and click Apply.

Configure the SQL Reporting Services

Click Web Portal URL and click Apply.

Configure the SQL Reporting Services

Step 10 – Re-install Reporting Services Point role

To re-install the Reporting Service Point role, open the SCCM console. Go to Administration > Overview > Site Configuration > Servers and Site System Roles.

Select the server on which you want to install Reporting services point role. Right click and choose Add Site System Roles.

Re-install Reporting Services Point role

On the System Role Selection page, select Reporting Services Point. Click Next.

Re-install Reporting Services Point role

Specify the credentials using which SQL Reporting Services will connect to Configuration Manager site database. Click Verify and ensure you see Successfully verified message. Click Next.

Re-install Reporting Services Point role

On the Completion page, click Close.

Re-install Reporting Services Point role

Step 11 – Start Configuration Manager Services

In the Step 5 we had stopped the Configuration Manager services. Now that we have completed the SQL server upgrade, you can start the services. Alternatively, you can restart the server once.

Open the browser and make sure you can access the reports.

Upgrade SQL Server 2014 to 2017

Prajwal Desai

Hi, I am Prajwal Desai. For last few years I have been working on multiple technologies such as SCCM / Configuration Manager, Intune, Azure, Security etc. I created this site so that I can share valuable information with everyone.

Related Articles

8 Comments

  1. Prajwal,

    I just wanted to say “Thank you,” for all the help you’ve given me over the years. Your site is always the first site I go to, when I have SCCM questions…

  2. Hi Prajwal,

    Please confirm if old reportserver and reportservertempdb, also need to restore on upgraded database server to sql 2017.

    Also confirm if encryption key backup also need to restore otherwise how reports will available without Db and encryption backup restoration

  3. Hi,

    I have upgraded my SCCM to 1910 today but my sql is still on 2012 version.
    Above procedure applicable for upgrading sql 2012 to 2017 as well?

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button