Upgrading your SCCM site database


I wrote a blog recently about using ConfigMgr 1602+’s supported option of in-place upgrade to the site server operating system and successfully upgrade from Windows Server 2008R2 to 2012R2.

Whilst working on this piece I took a quick look at the supported configuration for CB and noticed that my SQL server was out of support. It’s currently running SQL Server 2008 R2 SP2. The supported configuration documents clearly state that a minimum of SP3 is needed.  I parked the upgrade of SQL, as I wanted to focus on getting the OS up and running and didn’t want to muddy the water too much by making too many changes at once. So it’s time now to make that change and get SQL up to something a little newer. You should always refer to ConfigMgr supported configuration documents and here is a link to the Current Branch articles. If you need to know the information for SQL versions then here is a direct link.

I’m planning to update SQL Server to 2014. Therefore I need to be running at least SQL Server 2014 SP1 – Standard, Enterprise with no minimum CU. So I will update to SQL Server 2014 SP2 – Standard.

You can quickly check the SQL version you are currently running by executing the query select @@version on the database.

sqlup-001

Before I start the upgrade process it is worth noting that you should backup your environment before you begin and make sure you have a working backup you can restore to if anything goes wrong along the way.

The first thing to do is to stop all the ConfigMgr processes so nothing will be writing to the database.

sqlup-002

Once disabled then it’s time to start up the SQL installation media and get SQL 2014 RTM installed.

In the Installation section you need to choose ‘Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008R2 or SQL Server 2012’.

sqlup-003

When prompted enter the licence key

sqlup-004

Accept the licence terms

sqlup-005

Click Next

sqlup-006

I’m not too fussed by this warning. Click Next.

sqlup-007

Click Next.

sqlup-008

I’m running on the default instance so I just need to click Next here.

sqlup-009

Click Next.

sqlup-010

Click Next

sqlup-011

Click Next

sqlup-012

Before you upgrade it is worth noting that on my particular upgrade I was greeted with the following. Ewww not nice.

sqlup-015

I checked the summary log via the link in the window and I had the following information:

Setup completed with required actions for features.
Troubleshooting information for those features:
 Next step for Adv_SSMS: Use the following information to resolve the error, and then try the setup process again.
 Next step for SSMS: Use the following information to resolve the error, and then try the setup process again.
 Next step for SQLEngine: Use the following information to resolve the error, and then try the setup process again.
 Next step for Replication: Use the following information to resolve the error, and then try the setup process again.
 Next step for AS: Use the following information to resolve the error, and then try the setup process again.
 Next step for SNAC_SDK: Use the following information to resolve the error, and then try the setup process again.

To fix the problem I had to uninstall the SQL Server 2012 Native Client.

sqlup-016

So if you have removed 2012 Native Client and are ready to go then click Upgrade when prompted.

sqlup-013

The upgrade process will begin.

sqlup-014

And as if by magic SQL will have upgraded.

sqlup-017

After firing up SQL Server 2014 Management Studio I ran another select @@version just to show the upgrade process had occurred.

sqlup-018

Now that I have SQL Server 2014 RTM installed, I need to install Service Pack 2 to get into a supported configuration. You can download this from here.

Run the SQLServer2014SP2-KB3171021-x64-ENU.exe file.

Accept the licence terms

sqlup-019

Click Next.

sqlup-020

Click Next

sqlup-021

Click Update to install the service pack.

sqlup-022

sqlup-023

All good. Close down the install.

sqlup-024

And all confirmed that the server is now patched.

sqlup-025

At this point it’s time to bring the ConfigMgr services back online.

sqlup-026

 

After upgrading on my site I had issues with the Reporting Services Point. I’m not sure if this was related to the upgrade as I am unsure as to the state of the role prior to upgrading. To remedy the problem I removed the role and deleted the databases from SQL. I then re-configured via the Reporting Services Configuration Manager and re-installed the Reporting Services Point role. Since I didn’t have any bespoke reports in the site then this was a relatively painless exercise.

A quick look at the Site Status showed that everything was looking good. My Service Connection Point is moaning as the site doesn’t have Internet access at present.

sqlup-028

26 comments

  1. Great article. My upgrade scenario for SQL 2014 to 2016:

    Windows 2012R2 hosted on ESXi , SCCM CB 1606, and SQL 2014 SP2 with CU1. The upgrade went pretty smooth however the reporting service (SRSS) broke, tried several fixes and reverted the image a second time and same problem. Staying at SQL 2014 for now.

    1. That’s good to know, thanks for the info. I had issues with the Reporting Point and I wasn’t sure as to the state of that role prior to upgrade, hence I didn’t want to attribute it to the process. It would be good to know if others experience the same.

  2. I had the same problem with reporting. I uninstalled the reporting point then reinstalled the reporting point. Reporting work fine now.

    The problem I have is that the Devices collection and the Drivers collection is very slow when they are expanded past the initial 1000.

  3. I am about to follow the same, what the hierarchy you followed, First Cas, then Primary and then Secondary ?

    1. This was standalone primary. I don’t think there is an order when running SQL upgrade, I could be incorrect however, so long as you are running supported releases. It might be prudent to start at the CAS and upgrade down the hierarchy. Cheers Paul

  4. Quick question, while upgrading SQL on Secondary Server (Which is actually express edition), which binaries are to be selected , Here my SQL on secondary server is 2008 r2 SP3 Express edition and I am upgrading to SQL 2014 SP2
    Link : https://www.microsoft.com/en-in/download/details.aspx?id=42299
    binaries included in Link are listed below, Can you help me which will be the current one to be selected.

    Express 32BIT WoW64\SQLEXPR32_x86_ENU.exe
    Express 32BIT\SQLEXPR_x86_ENU.exe
    Express 64BIT\SQLEXPR_x64_ENU.exe
    ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe
    ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe
    ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe
    ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe
    LocalDB 32BIT\SqlLocalDB.msi
    LocalDB 64BIT\SqlLocalDB.msi
    MgmtStudio 32BIT\SQLManagementStudio_x86_ENU.exe
    MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe

  5. Upgrade Completed but Reporting services are broken. I reinstalled reporing services and role using https://blogs.technet.microsoft.com/charlesa_us/2015/03/07/re-installing-the-sql-reporting-services-for-configmgr-2012-wsql-2012/
    everything looks ok but srsrp.log says
    This is a SRS Reporting Point Role as SRSRP registry key exists.
    [DataSource]: Unable to read one of the keys associated with the Reporting Role. Please verify the reporting role is configured correctly
    [DataSource]: Unable to read one of the keys associated with the Reporting Role. Please verify the reporting role is configured correctly
    Waiting for changes for 1 minutes SMS_SRS_REPORTING_POINT 21/04/2017 07:45:53 4580 (0x11E4)
    nothing happens there after.

    PLease help

      1. Yes I have resolved now. what I did is : In SCCM console – Site Server – Reporting Server = Reporting service point – I removed the account and added new – then removed the account re added my original service account this time restry populated with right account and encrypted password.

        I Have 50 + Custom reports I need to manually aselect data source to them is there anything which I can automate it .

  6. Hi,
    I tried to upgrade the SQL Server from 2008r2sp3 Standard to 2014 Standard.
    The Problem is, that the License Key is not getting accepted during the Upgrade.
    Usually the Licensekey is in the Installationpackage from the SCCM, and during a Upgrade the Installation should find it by itself.
    I read also that this fails often. I found a way to read the 2008 key but 2014 just refuse to accept it.
    Any Suggestions for this Situation?

    Thanks and Regards
    Marcus

  7. Hi, I did the upgrade from SQL 2014 to SQL 2016 after a backup and restore to new hardware (Server 2016) and reporting got broken as well. Everything in the upgrade went well and no alerts are generated. Only when I try to run a report I get following error:

    Microsoft.Reporting.WinForms.ReportServerException
    Cannot create a connection to data source ‘AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_’. (rsErrorOpeningConnection)

    and in srsrp.log:
    (!) Error retrieving folders – [A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – The remote computer refused the network connection.)].

    Reporting is running on the primary site server.

    Anyone has experience with this problem? Re-installing Reporting didn’t work for me.

    Thanks in advance.

      1. Hi,

        yes it was working afterwards. I left 5 days between the backup and restore and the upgrade. Any way I can check the permissions?

        Thanks for the quick response.

  8. Hi – Will there be another document covering sql 2016 sp1 to sql2017 in place upgrade on cb1802. I would like to perform in place upgrade. Worried about SSRS issue.

    Have ran DMA assessment and everything seems fine for in place upgrade. Gong to backup all the db’s before in place upgrade.

    Thanks

    Ram

  9. Hi all I am using SCCM CB and unable to reinstall the Reporting service point. I don’t see any error message in srsrpMSI.log, srsrpsetup.log and srsrp.log . the only suspicious message I am having is “[DataSource]: Unable to read one of the keys associated with the Reporting Role. Please verify the reporting role is configured correctly” in srsrp.log. Also the Root report folder for configuration manager is not created in sql server reporting services.

      1. Yes I have configured SSRS in reporting configuration manager.
        due to this problem I have also tested AD credential, virtual service account and now Localsystem for the service account in reporting configuration manager but none of them were successful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s