Since I’ve recently upgraded the site server OS, see here, I was going to blog the upgrade of the SQL on the site server. Unfortunately I forgot to screenshot the process, as you do, but I thought it would be worth at least pointing out the steps I took to get there.
Now I did do this before and blogged it (SQL 2008 to 2014), so this can be used as a starting point.
Start off by stopping the ConfigMgr services. Stopping is fine but I disabled them for now.
Run the SQL install from the media and choose Installation and then the upgrade link.
At this point, you are pretty much clicking through the wizard and accepting the default actions to progress.
Once done, you’ll need to apply a minimum of CU5 to be an a supported state for SQL. The latest CU is 12 and it’s available from here. Run the setup file, click through the wizard. Again it’s default actions as you click through.
When this is completed, you have upgraded SQL but there are a couple of steps to do still.
First up, you’ll need to download and install SQL Management Studio, this is now a separate installation.
If you try and run SQL Management Studio with your existing 2014 version then you’ll get the following message:
Don’t worry, installing the new Management Studio will get you up and running. The link to get the latest version is available here.
You will also need to install SQL Reporting Services as well, this is available from here. When installing SSRS have your SQL product key to hand, you will need to enter this to licence the software.
One final check to make is the compatibility level on your SQL once upgraded. MS has a handy table to reference.
As Microsoft states, ‘When you upgrade a site database from an earlier version of SQL Server, the database keeps its existing cardinality estimation level, if it’s at the minimum allowed for that instance of SQL Server. When you upgrade SQL Server with a database at a compatibility level lower than the allowed level, it automatically sets the database to the lowest compatibility level allowed by SQL Server.’
If you run the command SELECT name, compatibility_level FROM sys.databases, you’ll get the details back. As you can see from my site server the ConfigMgr DB is running at 110 which is the lowest supported compatibility level.
So I decided to raise this to the recommended level of 150 by running the command:
ALTER DATABASE <ConfigMgr database name> SET COMPATIBILITY_LEVEL = 150
If you’ve disabled the ConfigMgr services then get them started again.
If you need to upgrade in a CAS, then run the SQL upgrade on the CAS first and then the primary server/s and if you have secondaries then upgrade there before the primary site.
Hope this information helps you with your upgrade scenario.
SCCMentor - Paul Winstanley 