Moving the Data Warehouse database to another server

Background: we decided to take a "simpler approach" than we did with moving the Operations Database. Moving that database included registry hacks and security changes. For moving the the Data Warehouse database, we wanted to be more straightforward – uninstall the OpsMgr Data Warehouse component, install it on a different server, then copy over the original database. However, this actually required a bit more discovery and work that was anticipated, since some things were not intuitively obvious (see step 10). 

  1. On the RMS, stop the SDK and Config Services.
  2. On the RMS and all other management servers, stop the Health Service. Stopping the OpsMgr services prevents updates from being posted to the databases while you are moving the data warehouse.
  3. On the current Data Warehouse server, use SQL Management Studio to backup the Data Warehouse database (default name: OperationsManagerDW) to a shared folder on the server. You will want to back up the master database as well, as a precaution.
  4. On the current Data Warehouse server, uninstall the OpsMgr data warehouse component. Open Control Panel, Add/Remove Programs, select the System Center Operations Manager 2007 Reporting Server and choose Change, in the Reporting Setup select Modify, and select the Data Warehouse component to not be available. Note: that this does not physically remove the data warehouse database as a SQL Server database. After removing the Data Warehouse component from OpsMgr, delete it manually using SQL Management Studio (this assumes you backed it up in step 3!).
  5. On the new Data Warehouse server, install the OpsMgr data warehouse component by running OMSetup.exe. Select the option to Install Operations Manager 2007 Reporting, selecting ONLY the Data Warehouse component for installation (Mark the Reporting Services component to not be available on this server).
  6. On the new Data Warehouse server, copy the backup of the data warehouse database (step 3) to a local folder. (If the shared folder on the original server is accessible as a mapped drive from SQL Management Studio, you can skip this step.)
  7. On the new Data Warehouse server, use SQL Management Studio to restore the data warehouse database backup (delete the existing database first, be sure the default option to Delete backup and restore history information for databases is checked). Restoring the original data warehouse database is necessary to not lose the report data you have already collected for your management group.
  8. On the new Data Warehouse server, create a login for the SDK Account, Data Warehouse Action Account and the Data Reader Account in SQL Management Studio. Ensure the database permissions are correct for these accounts.
  9. On the RMS, start the SDK service.
  10. On the server running SQL Reporting Services, modify the data source. In Internet Explorer, open http://localhost/reports. On the Properties page, choose Show Details. The data source is named "Data Warehouse Main." Select that data source, and in the connection string, change the name of the database server from the old data warehouse server to the new data warehouse server.
  11. Change the name of the data warehouse server in the OpsMgr databases. Open SQL Server Management Studio to do your edits. For the OperationsManager database, go to the MT_Datawarehouse table and change the value of the MainDatabaseServerName_16781F33_F72D_033C_1DF4_65A2AFF32CA3 column (that really is the column name!) to the new data warehouse database server. For the OperationsManagerDW database, navigate to the MemberDatabase table and change the value of ServerName. Be sure to close the Management Studio when you are through, to save your changes.
  12. Restart the Config and SDK services on the RMS and the Health service on the RMS and all other management servers.
This entry was posted in Operations Manager 2007. Bookmark the permalink.

2 Responses to Moving the Data Warehouse database to another server

  1. Walter says:

    Does this work if you do not have access to the original OpsMgrDW and SRS, just the backed up atabse?

  2. Operations says:

    Walter’s scenario is asking what to do if the entire place burned down and all you had were database backups. In this case, we are assuming when he reinstalls OpsMgr, the Operations database is installed to the same server but he wants to change the name of the server with the data warehouse database. (And of course that he keeps the same management group name.)
    Reinstall the management group (same name), and reporting, identifying the new server for the data warehouse location. Immediately stop the 3 services on the RMS. Restore the Ops and DW databases, then make the database changes specified in step 11. Restart the Health, SDK, and Config services on the RMS.
    There would be some additional cleanup to do, as the OpsDB would think agents were installed on computers that presumably no longer existed. 

Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s