Configuration Manager SQL Upgrade

With how easy it is to upgrade Configuration Manager now, it can be easy to forget about the most important component of your CM infrastructure, SQL.  I'm guessing that a number of us are still running SQL 2012.  Why?  Because when we built our CM 2012 environment, that's what was the latest for SQL at the time.  So, a number of CM environments are still running 2012.

So, why am I looking at this now?  Well, CM 1810 introduced a pre-req check for a never version of the SQL native client.  And I thought to upgrade SQL.  Now the SQL upgrade isn't a requirement (yet), just the native client update.  So, as I began the upgrade process, I found all the resources I found out there to be incomplete.  Once I figured it out for me, I thought I would share.

In the blog post, I will take you through the process of upgrading from SQL 2012 SP3 up to SQL 2016.  Yah, I know I could do 2017, but my organization is standardizing on 2016 for now.  This will be an in-place upgrade, so, lets get started.

My environment is running a stand-alone SQL Server installed on my primary site server.  SQL Server Reporting Services (SSRS) is also installed on this server and will be upgraded as part of the process as well.

Backups!!

Make sure you have a good backup of your CM database and your SSRS database before you begin this process.  Also, make backups of your custom reports into RDL report files just in case as well.  Maybe overkill, but better safe than sorry.

Remove Reporting Services Point Role From CM

Next, open your CM console and remove the Reporting Services Point Role from your server.  This is required at some point in the setup as the SSRS 2016 needs some CM specific files copied into its bin directory.  My experience, the removal of the role does not remove any default or custom reports from the reporting server.  You can monitor the removal in the log file (<Configuration Manager>\Logs\srsrpsetup.log).  This should only take a minute.

Pre-Req Checks for Reporting Services

• Back up your symmentric key for the reporting services database.
• Remove SSL Certificate bindings.  If all certificates are valid, probably will not have an issue but, if there is a issue with the certs, SSRS will fail the upgrade and leave it unusable.
  • Error during upgrade if you don't clean up certs: A HTTPS certificate is not configured on the Web site.
  • If you complete the upgrade and get this error.  SSRS will be unusable.  When I saw this, I had the option to roll back the server to before the upgrade.  The other option will be removing and reinstalling SSRS and reconnecting the existing DB with the encryption key.

1. Open Reporting Services Configuration Manager.
2. Connect to your Server.
3. Click on Encryption Keys on the left.
   1. Choose Backup and follow the prompt to back up the key to a secure location with a password.
4. Click Web Service URL on the left.
   1. For HTTPS Certificate, set to (Not Selected).
   2. Click Advanced... 
   3. Remove any HTTPS Identities.
   4. Click OK.
5. Click Report Manager URL on the left.
   1. Click Advanced...
   2. Change IP Address to All Assigned for HTTP, if not already set.
   3. Remove any HTTPS Indentities.
   4. Click OK.
6. Click Apply.
7. Restart Reporting Services service.
8. Verify accessible from HTTP://Server/Reports

Start the SQL Upgrade

By now, you should have you SQL media downloaded (2016 SP2, as of this posting).  Mount your ISO and run Setup.exe as an admin.  From the splash screen, go ahead and click on Installation on the left.  And then, Upgrade from a previous version of SQL Server on the right.

Click next on the licensing dialog.

Accept licensing terms, click Next.

On Microsoft Updates dialog, click Next.

On the Upgrade Rules, click Next.

• I haven't seen any not pass here yet.

Select your instance to upgrade.  Should be the only and default instance in most cases, MSSQLSERVER. Click Next.

On the Select Features dialog, it will be all greyed out, just click Next.

On the Instance Configuration dialog, just leave the defaults and click Next.

On Server Configuration, click Next.

On the Full-Text Upgrade dialog, leave default of Import, and click Next.

We are now ready for the actual upgrade to start!  On the Ready to Upgrade dialog, make note of your configuration file.  This can be used in your documentation or change control.  When you are ready to start the actual upgrade, click Upgrade.

The upgrade is going to take a while to complete.  You will see it move along as to goes.  At one point, I saw it seem to hang for 10-15 minutes on the following screen, SqlRSConfigAction_upgrade_configrc_Cpu64.  It eventually continued on on its own.  While waiting, (as I thought it hung too) I found some people stopped the SQL Full-text Filter Daemon Launcher service to get it to continue.

With any luck, your upgrade will eventually complete.  A reboot may be required to complete it. Though, I reboot anyway to make sure everything is happy.

Verify everything is functioning as expected in CM (except reports, we have to reinstall that still) before you continue.

Reinstall Reporting Services Point Role to CM

We need to add our Reporting Service Point back.  open your CM console and install Reporting Services Point Role back onto your site server just as it was before.  This will install the required files for the CM reports back into the bin directory on the new report server root (ie. <Program Files>\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin).  You can monitor the role installation in the log file (<Configuration Manager>\Logs\srsrpsetup.log).  This should only take a minute.

After it's installed, you should be able to use all the canned and your custom reports again without issue.

Many that do not remove and re-add the Reporting Services Point will run into errors when running reports about srsresources and other missing items, like this:

Failed to load expression host assembly. Details: Could not load file or assembly 'SrsResources, Culture=neutral' or one of its dependencies. The system cannot find the file specified. (rsErrorLoadingExprHostAssembly)

Uninstall Residual SQL 2012 Components

After you are upgraded to 2016 and everything is running fine, you may peak at Add/Remove Programs and find that there is still a bunch of SQL 2012 components installed on the system.  You don't have to remove these, but I did just to keep my system clean.  Also note, that part of the 2012 SQL install may have been SSMS.  You don't want to manage a SQL 2016 server with an old version of Management Studio.  So, lets get these cleaned up.  When I first looked at Add/Remove Programs, I had quite a list.  So, lets take a look and get started.

This is the order I completed the uninstall in.

1. Microsoft SQL Server 2012 (64-bit)
   • During this removal step, select all the features that are displayed to be removed.
   • Once this was removed, a number of the other items listed in Add/Remove Programs went with it.
2. Microsoft SQL Server 2012 Data-Tier App Framework
3. Microsoft SQL Server 2012 Management Objects
4. Microsoft SQL Server 2012 Management Objects (x64)
5. Microsoft SQL Server 2012 T-SQL Language Service

Make sure you leave the Microsoft SQL Server 2012 Native Client installed.  CM needs this to function.  We will upgrade this to the latest later.

After everything is cleaned up, reboot and make sure CM is still functioning.

Install the latest SQL Server Management Studio

At this point, I install the latest SQL Server Management Studio as it is no longer part of the SQL install.  Many recommend not putting it on your SQL server any more but, I still like it there for ease of use.  If you don't want to install it, connect to the database server on the next step from a current version of SSMS.

Change Database Functionality Level

Next, we will upgrade the compatibility level on the database to 2016.

1. Open SQL Server Management Studio (SSMS).
   • Make sure you are launching with a recent build of SSMS.
2. Browse to databases and select your Site Database.
3. Right-click the database and choose Properties.
4. Select the Options page on the left.
5. Then on the right, change your Compatibility level to SQL Server 2016 (130).
6. Click Ok!

NOTE: You likely have your WSUS database on this server as well. DO NOT increase its compatibility level.

 

Upgrade SQL Native Client to Latest QFE

CM 1810 has a prereq check for a SQL Native Client that supports TLS 1.2.  There is no SQL 2016 Client.  So, the latest QFE for the 2012 client should be installed from here as it is used for SQL 2012+ TLS 1.2 support:

• Latest QFE for SQL 2012 Native Client - Download and install to meet the pre-req for CM 1810

Read more about the TLS 1.2 support in SQL:

• TLS 1.2 support for Microsoft SQL Server

Complete!!

Alright.  I would give the thing one more reboot at this point and make sure everything is functioning as it should in CM and SQL. 

Hope this helps you out!