Tuesday, January 8, 2013

OM12 SP1: Upgrading SQL Server 2008 SP1 To SQL Server 2012 SP1

In a previous posting I wrote about the challenges when upgrading SQL Server 2008 R2 SP1 hosting the OM12 SP1 databases to SQL Server 2012 SP1. I checked with Microsoft (they’re very busy since I haven’t got an answer yet) but one of my fellow MVPs, Daniele Grandini told me he had successfully run this upgrade.

High level steps Daniele mailed me:

  1. Remove OM12 SP1 Reporting;
  2. Reset SSRS;
  3. Upgrade SQL Server 2008 R2 SP1 to SQL Server 2012 SP1;
  4. Reinstall OM12 SP Reporting.

So it was time to test this in one of my test labs. Let’s start!

Detailed steps in one of my test labs:

  1. Backup the OM12 SP1 databases (OperationsManager, OperationsManagerDW & ReportServer);
  2. Snapshot the VM(s) hosting the SQL Server instance(s). When the server(s) is(are) physical make a complete backup(s) of the server(s) so a rollback can be done when required;
  3. Only when required: Backup customized Reports using this tool;
  4. Remove OM12 SP1 Reporting from the SQL Server hosting the SSRS instance for OM12 SP1;
  5. Remove the OM12 SP1 Agent as well since it might block the installation of OM12 SP1 Reporting later on;
  6. Run the tool ResetSRS.exe found on the installation media of OM12 SP1 (~:\SupportTools\AMD64);
    1. Syntax: ResetSRS.exe MSSQLSERVER for the default SQL Server instance;
    2. Provide the account for setting up the database connection <ENTER>;
    3. Provide the password <ENTER>;
    4. Account and password will be validated. When OK the SSRS instance will be reset (the OM12 SP1 security extensions will be removed);
    5. After a while SSRS will be reset successfully. When failed retry and when it fails again, remove SSRS and reinstall it using this posting of mine;
    6. Browse to http://localhost/reports in order to check the health of the SSRS instance. Normally you should see the web interface of SSRS. When an error is shown fix it or remove and reinstall SSRS using this posting of mine.
  7. Now all is OK for the upgrade to SQL Server 2012 SP1. Insert the installation media of SQL Server 2012 SP1 and run it;
    1. SQL Server Installation Center screen will be shown after a while > Installation > Upgrade from SQL Server 2005, SSQL Server 2008 or SQL Server 2008 R2;
    2. Setup Support Rules will be loaded. When all is done > OK;
    3. Enter the product key > Next;
    4. Accept EULA and choose whether or not to send feature usage data to Microsoft > Next;
    5. SQL Server will check for product updates to SQL Server 2012 SP1. When there is no internet connectivity you’ll get an error message. Luckily you can still continue the upgrade when that happens > Next;
    6. SQL Server Setup Files will be installed now > Next;
    7. Select the SQL Instance to be upgraded > Next;
    8. The related Features to be upgraded will be shown as well > Next;
    9. Instance Configuration is shown now. Modify ONLY when required > Next;
    10. Disk space requirements will be checked > Next;
    11. Server Configuration will be shown with the related service accounts > Next;
    12. Full-Text Upgrade. I have chosen the default option, Import > Next;
    13. Error Reporting. Choose what’s default for the company you’re working for > Next;
    14. Upgrade Rules will be processed now. When all is done > Next;
      1. Now stop the OM12 SP1 Management Servers since SQL Server will upgraded in the next step. During that process no data will be written to the OM12 SP1 databases. Therefore it’s better to shutdown the OM12 SP1 Management Servers. Communicate this with the end-users of OM12 SP1.
    15. Ready to Upgrade screen will be shown. Check the summary. When all is OK > Upgrade;
    16. Upgrade Process screen is shown now. Be patient, it will take a while;
    17. When the upgrade is finished it will show whether all SQL instance features and components were upgraded successfully. There is also a summary log file. Check it and when all is OK > Close.
  8. Browse to http://localhost/reports in order to check the health of the SSRS instance. Normally you should see the web interface of SSRS. Please note, this web interface is based on SQL Server 2012;
  9. Restart the OM12 SP1 Management Servers and when they’re running start the OM12 SP1 Console in order to see all is really OK;
  10. Now it’s time to reinstall OM12 SP1 Reporting. Open the OM12 SP1 installation media on the SQL 2012 Server and run Setup.exe;
  11. Install > select Reporting Server > Next > select installation location > Next > Next > Accept EULA > Next;
  12. Specify an OM12 SP1 Management Server > Next > select the SQL Server DB instance which is the local SQL Server instance > Next > Enter the credentials for the Data Reader Account;
  13. Select whether or not to join ODR > > Select Next whether or not to use Windows Update for OM12 SP1 Reporting > Next;
  14. Installation Summary screen is shown. Check and when all is OK > Install;
  15. After a while OM12 SP1 Reporting is successfully installed. Within an hour the Reports, present in the imported MPs, will be uploaded to SSRS;
  16. Only when required: Restore customized Reports using this tool;
  17. Reinstall the OM12 SP1 Agent on the SQL server.

And now SQL Server 2008 R2 SP1 hosting the OM12 SP1 databases is successfully upgraded to SQL Server 2012 SP1. Nice!

Thanks Daniele for sharing your personal upgrade experiences. Much appreciated! I owe you a beer!


Unknown said...

daniele and marnix - you are right on and this is the recommended procedure you will find in OpsMgr 2012 Unleashed, Chapter 6 (Upgrade and Migration). - JJ

Marnix Wolf said...

Thanks John for your feedback. Much appreciated.
Cheers, Marnix

Dominique said...

Daniele, Marnix Happy New Year...
Thanks for this excellent post.. one hurdle less...
I am testing the upgrade SCOM 2012 UR11 to SCOM 2016 UR1 and apparently the same process about SQL 2012 to be upgraded needs to be dome. One question is it for both instance OperationsManager\RepoertServer & OperationsManagerDW\ReportsServer as well...

Marnix Wolf said...

Hi Dominique.

Thanks for you wishes. Same to you.

Yes, SQL server needs to be at least on SQL Server 2014 level, this goes for all SCOM components, Reporting included.

BUT... for quite a few environments I've already run the upgrade and those environments run SQL Server 2012 SP2 or later (SP3).

And that works just fine. Please know this is not OFFICIALLY supported though.

Just my two cents and own experiences out of the field.