Moving SCOM 2012 R2 Database to New SQL Server Cluster

When our System Center environment (VMM, Orchestrator, SCOM, DPM) was originally setup all the databases were installed on a single SQL Server VM (but in separate instances).  For a variety of reasons I made the decision that our System Center databases needed to at least be in a cluster configuration.  Luckily I stumbled across this excellent guide by MVP Paul Keely about configuring a SQL 2012 Cluster with Always On to be used for System Center databases.  I am not going to cover any of that here, this post is just going to be about how to move the Operations Manager database once the new cluster is built.

First, I need to go on a rant about just plain poor timing on my part.  If you don’t care to read it, skip to the next paragraph.  There is an article published by Microsoft that talks about how to move the Operational Database.  If you look at that article you will notice that it was last updated on January 19th.  That’s awesome.  You know what’s not awesome?  I first attempted to move the Operational Database on January 17th.  It failed miserably.  Why?  Because the article I was working off of didn’t include a second registry key that needed to be modified.  That’s 6 hours of my  life I will never get back.

In my environment I have two Operations Manager Management servers, in this article they are named OPSMGR01 and OPSMGR02.  When I wrote up my plan for this move I duplicated some sections that needed to be done on both servers and I kept that the same in this article.  For the SQL Server name, I used the name of the new SQL Cluster as SQLCLUSTER followed by the instance name.  If you are moving the databases to a single server you would just use the server name followed by the instance name.

  1. Stop the following services on OPSMGR01 and change their type to Manual.  Otherwise they will restart and cause you problems.
    1. System Center Data Access
    2. System Center Management Configuration
  2. Stop the following services on OPSMGR02 and change their type to Manual.  Otherwise they will restart and cause you problems.
    1. System Center Data Access
    2. System Center Management Configuration
  3. Create full backup of OperationsManager Database on OLDSQLServer using SQL
    1. Copy backup file to \\SQLCLUSTERNODE1\SHARE
  4. Open SQL Management Studio on SQLCLUSTERNODE1 and connect to SQLCLUSTER\SCOM Instance
    1. Restore OperationsManager Database using SQL
  5. Delete backup file from \\SOMEFILESHARE (Needed the space for the massive SCOM Data Warehouse Database backup that was coming next)
  6. Backup Registry on OPSMGR01
  7. Backup Registry on OPSMGR02
  8. Update Registry on OPSMGR01
    1. Regedit
    2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup
    3. For each of the following keys, double-click the name, change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your changes.
      1. DatabaseName: SQLCLUSTER\SCOM
      2. DatabaseServerName: OperationsManager
    4. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\2010\Common\Database and repeat step 3. (This was the aforementioned missing step)
  9. Update Registry on OPSMGR02
    1. Regedit
    2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup
    3. For each of the following keys, double-click the name, change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your changes.
      1. DatabaseName: SQLCLUSTER\SCOM
      2. DatabaseServerName: OperationsManager
    4. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\2010\Common\Database and repeat step 3.
  10. Edit the following file on OPSMGR01
    1. Browse to %ProgramFiles%\System Center 2012\Operations Manager\Server\ConfigService.config
    2. Backup existing file (copy and rename to .old and place somewhere safe)
    3. Open Notepad as Administrator and open the file in Step 1.
    4. In the <Category> tags named “Cmdb” and “ConfigStore”, change the value for ServerName to the name of the new SQL server.
    5. SQLCLUSTER\SCOM
    6. Save changes and close
  11. Edit the following file on OPSMGR02
    1. Browse to %ProgramFiles%\System Center 2012\Operations Manager\Server\ConfigService.config
    2. Backup existing file (copy and rename to .old and place somewhere safe)
    3. Open Notepad as Administrator and open the file in Step 1.
    4. In the <Category> tags named “Cmdb” and “ConfigStore”, change the value for ServerName to the name of the new SQL server.
    5. SQLCLUSTER\SCOM
    6. Save changes and close
  12. Update the operational database with the new database server name on SQLCLUSTERNODE1
    1. Open SQL Server Management Studio. Connect to SQLCLUSTER\SCOM instance.
    2. Expand Databases, OperationsManager, and Tables.
    3. Right-click dbo.MT_Microsoft$SystemCenter$ManagementGroup, and then click Edit Top 200 Rows.
    4. Change the value in the SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C5930B04 column to SQLCLUSTER\SCOM
      1. The GUID part in your database isn’t going to match the one above.  It’s ok!
    5. Save the change.
  13. On SQLCLUSTERNODE1, update the operational database with the new database server name to specify the location of the Application Performance Monitoring tables
    1. Open SQL Server Management Studio. Connect to SQLCLUSTER\SCOM instance
    2. Expand Databases, OperationsManager, and Tables.
    3. Right-click dbo.MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring, and then click Edit Top 200 Rows.
    4. Change the value in the MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A column to SC-SQLCLUS-SCOM\SCOM
      1. The GUID part in your database isn’t going to match the one above.  It’s ok!
    5. Save the change.
  14. Update security credentials on SQLCLUSTER\SCOM
    1. All of my security credentials carried over and I had no issues.  However, your environment may be completely different.  Definitely make sure to read this section in the TechNet documentation
  15. Execute these SQL commands on new OperationsManager database instance:
    1. sp_configure ‘show advanced options’,1
    2. reconfigure
    3. sp_configure ‘clr enabled’,1
    4. reconfigure
  16. Run the following SQL query (If you added the OperationsManager database to AlwaysOn before this step you can skip this step. I ran through all of this and added it to AlwaysOn once I knew for sure it was working on the new SQLCLUSTER):
    1. SELECT is_broker_enabled FROM sys.databases WHERE name=’OperationsManager’
      1. If the result of this query was an is_broker_enabled value of 1, skip this step. Otherwise, run the following SQL queries:
    2. ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    3. ALTER DATABASE OperationsManager SET ENABLE_BROKER
    4. ALTER DATABASE OperationsManager SET MULTI_USER
  17. Stop SQL Services relating to SCOM on OLDSQLServer
  18. Open PowerShell.  Type Write-Host “Bye Kitty” to sacrifice a kitten
  19. Restart OPSMGR01 and OSPMGR02 (this was to ensure registry setting changes were picked up)
  20. Start the following services on OPSMGR01 and change their startup type to Automatic
    1. System Center Data Access
    2. System Center Management Configuration
  21. Start the following services on OPSMGR02 and change their startup type to Automatic
    1. System Center Data Access
    2. System Center Management Configuration
  22. Spam refresh OperationsManager event log on OPSMGR01.  Hopefully you see lots of informational messages and no errors about connections to the SQL Server.
  23. Start OperationsManager console.  Check stuff

That’s it!