How to move SCOM 2012 OperationsManagerDW Database

In a previous post we have moved the OperationsManager Database to a new SQL server.

In this post I will cover the steps required to move the OperationsManagerDW Database.

I’m assuming that you have the new SQL server prepared with the exact same installation as the currently used server.

This process includes a downtime for the SCOM server so be prepared.

  1. Start by stopping the System Center Data Access service

Stop-Service OMSDK

  1. On the original SQL server backup the Database (OperationsManagerDW) using the SQL Management Studio.
  2. Once the backup is completed copy the BAK file to the new SQL server to a local drive.
  3. Restore the OperationsManagerDW Database using SQL Management Studio.

That is the procedure to move the SCOM 2012 Database to a new server.

Now we need to reconfigure the SCOM 2012 server to connect to the new SQL server.

  1. Open the registry on the SCOM 2012 and change the following key appropriately:

HKLM\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Reporting Double click DatabaseServerName and change the value to point to the new server.

 

** Please note, if you have installed the new SQL with the default instance name then you need to enter only the servername. If you have installed the SQL with a named instance, you will have to enter the servername\instancename.

Now the SCOM 2012 server knows about the new SQL server that holds its DB.

 

The next step is to modify some properties on the new SQL server:

  1. Open SQL Server Management Studio
  2. Expand Databases, OperationsManager, and Tables
  3. Right-click dbo. MT_Microsoft$SystemCenter$DataWarehouse, and then click Edit Top 200 Rows
  4. Change the value in the MainDatabaseServerName_<GUID> column to reflect the name of the new SQL Server-based computer.
  5. Click on update to reflect the changes
Change OperationsManager DataWarehouse server name

Change OperationsManager DataWarehouse server name

 

  1. Right-click dbo. MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring, and then click Edit Top 200 Rows
  2. Change the value in the MainDatabaseServerName_<GUID> column to reflect the name of the new SQL Server-based computer.
  3. Click on update to reflect the changes
Change OperationsManager AppMonitoring server name

Change OperationsManager AppMonitoring server name

 

  1. Expand Databases, OperationsManagerDW, and Tables
  2. Right-click dbo. MemberDatabase, and then click Edit Top 200 Rows
  3. Change the value in the ServerName column to reflect the name of the new SQL Server-based computer.
  4. Click on update to reflect the changes
Change OperationsManagerDW ServerName server name

Change OperationsManagerDW ServerName server name

The next step is to make sure that all the SQL Logins are configured correctly.

The Logins should have been maintained during the restoration of the Database on the new SQL server, but I suggest that you review the permissions on the old SQL server and match them to the new SQL server.

That’s it!

You can now start the SCOM 2012 services but my suggestion is to reboot the SCOM 2012 server. The services should start automatically and all should be now working!

 

[important]

Update March 19th, 2013

There are 2 more tables you need to alter after the move:

dbo. MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring_Log

dbo. MT_Microsoft$SystemCenter$DataWarehouse_Log

[/important]

 

[important]

Update March 31st, 2013

Please note Walid’s comment bellow about the location of certain registry keys when you have a multi server environment  and some additional keys needed to be changed.

Thank’s Walid!

[/important]

Incoming search terms:

  • Movescom
  • Move Data Warehouse SCOM 2012
  • change scom datawarehouse to another sql server
  • scom move database
  • scom 2012 move database
  • scom 2012 db dwh move
  • operational data reports registry key location
  • moving operations manager dw 2012 database
  • move the operations manager dw to another instance
  • move scom database 2012

4 Comments

  1. The key “HKLM\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Reporting” is only found on the server holding reporting services (typically the current SQL server) not the SCOM management server

    also there’s a key at on the management server at
    “HKLM\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup”
    and there are 2 keys
    -DatabaseServerName
    -DataWarehouseDBServerName

    do we need to edit these as well?

    • Hi Walid!
      Thank you very much for your comment, I have only one server in my setup…

      Regarding the additional keys, I will have to verify this.
      I will post another comment here with once I do.

      • in my case I had the SQL remote from the SCOM server. and the reporting installed on the SQL, I edited the registry in both locations (reporting and setup) and it appears to be working fine

        may I ask you which image plugin are you using for the images and thos cool flashy announcements 🙂

        • Thank you very much for sharing!
          I will add this to the post.

          About the image plugin – its called Lightbox Plus Colorbox.
          By flashy announcements – you mean the one on top? its part of the Theme.

Leave a Reply to admin Cancel reply