How to Move the Data Warehouse Database

Applies To: Operations Manager for System Center 2012, System Center 2012 R2 Operations Manager, System Center 2012 SP1 - Operations Manager

After the initial deployment of System Center 2012 – Operations Manager, you might need to move the data warehouse database from one Microsoft SQL Server-based computer to another.

Caution
This procedure can result in data loss if it is not performed correctly and within a reasonable length of time of the failure. Ensure that you follow all steps precisely, without unnecessary delays between the steps.

This procedure requires Microsoft SQL Server configuration. You need to back up a database, restore a database, update a database table, add new Logins, and modify User Mapping settings for Logins. For more information, see SQL Server documentation.

Data Warehouse Database Relocation Procedure

Use the procedure below to move the data warehouse database to a different system.

To move the data warehouse database

1. Stop the Operations Manager services (System Center Data Access Service, System Center Management Service, System Center Management Configuration Service) on all management servers in the management group.

2. On the current Data Warehouse server, use SQL Server Management Studio to create a full backup of the data warehouse database. The default name is OperationsManagerDW. We recommend that you also back up the associated master database.

   For more information, see How to: Back Up a Database (SQL Server Management Studio).

3. On the new SQL server, copy the backup file to a local drive or map a local drive to the folder that contains the backup file.

4. Optionally, on the current Data Warehouse server, delete the data warehouse database.

5. On the new Data Warehouse server, use SQL Management Studio to restore the operational database that you previously backed up.

   For more information, see How to: Restore a Database Backup (SQL Server Management Studio).

6. On the server hosting the Operations Manager Reporting component, update the registry to refer to the new SQL Server-based computer.

   Note
   Before editing the registry, follow your organization’s backup policies with regard to the registry.

   1. Log on to the management server with Administrator permissions. 
      
      
   2. Click Start, select Run, type regedit in the Open box, and then click OK to start Registry Editor.
      
      
   3. Under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Reporting, double-click the nameDWDBInstance, and then change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your change.
      
      
   4. Close the Registry Editor.
      
      

7. Start the System Center Data Access Service on the management server associated with the reporting server.

8. On the management server associated with the reporting server, change the connection string.

   1. Open a browser and go to the reporting webpage, http://localhost/reports_instancename.
      
      
   2. Click Show Details and then click Data Warehouse Main. 
      
      
   3. Change the Connection String to contain the new data warehouse server name, and then click Apply. 
      
      
   4. Close the browser.
      
      

9. On the management server associated with the reporting server, change the connection string for AppMonitoringSource.

   1. Open a browser and go to the reporting webpage, http://localhost/reports_instancename.
      
      
   2. Click Application Monitoring, and then click .NET Monitoring.
      
      
   3. Click Show Details, and then click AppMonitoringSource.
      
      
   4. On the AppMonitoringSource page, click Properties and change Connection string to contain the new data warehouse main data source server name, and then click Apply.
      
      
   5. Close the browser.
      
      

10. On the server hosting the operational database, update the OperationsManager database table.

    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_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F column to reflect the name of the new SQL Server.
       
       
    5. Close SQL Server Management Studio.
       
       

11. On the server hosting the operational database, update the OperationsManager database for Application Performance Monitoring functionality.

    1. Open SQL Server Management Studio.
       
       
    2. Expand Databases, OperationsManager, and Tables.
       
       
    3. Right-click dbo. MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring, and then click Edit Top 200 Rows.
       
       
    4. Change the value in the MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A column to reflect the name of the new SQL Server.
       
       
    5. Close SQL Server Management Studio.
       
       

12. On the new data warehouse server, update the member database.

    1. Open SQL Server Management Studio.
       
       
    2. Expand Databases, OperationsManagerDW, and Tables.
       
       
    3. Right-click dbo. MemberDatabase, and then click Edit Top 200 Rows.
       
       
    4. Change the value in the ServerName column to reflect the name of the new SQL Server.
       
       
    5. Close SQL Server Management Studio.
       
       

13. On the new server hosting the operational database, expand Security, then expand Logins, and then add the data writer account.

    For more information, see How to: Create a SQL Server Login.

14. Also in Logins, add the data reader account.

15. Also in Logins, add the Data Access Service computer account, using the form “domain\computername$”.

16. For the Data Access Service (DAS) computer account, add the following user mappings:

    • db_datareader
      
      
    • OpsMgrReader
      
      
    • apm_datareader
      
      

    Note

    If an account has not existed before in the SQL instance in which you are adding it, the mapping will be picked up by SID automatically from the restored data warehouse database. If the account has existed in that SQL instance before, you receive an error indicating failure for that login, although the account appears in Logins. If you are creating a new login, ensure the User Mapping for that login and database are set to the same values as the previous login: DW Data Writer: db_owner, OpsMgrWriter, apm_datareader, apm_datawriter DW Data Reader: db_datareader, OpsMgrReader, apm_datareader DAS/Config account: db_datareader, OpsMgrReader, apm_datareader If DAS/Config uses the LocalSystem account, specify computer account in form “<domain>\<computername>$”.

17. Start the Operations Manager services (System Center Management, System Center Data Access, and System Center Management Configuration) on all the management servers in the management group.

To verify a successful move of the data warehouse database

1. Verify that you can successfully run a report from the console.

2. Ensure that the health state of all management servers in the management group are Healthy.

   If the health state of any management server is Critical, open Health Explorer, expand Availability - <server name>, and then continue to expand until you can navigate to Data Warehouse SQL RS Deployed Management Pack List Request State. Check the associated events to determine if there is an issue accessing the data warehouse database.

3. Check operating system events:

   1. Open the operating system's Event viewer. Navigate to Event Viewer, and then to Operations Manager.
      
      
   2. In the Operations Manager pane, search for events with a Source of Health Service Module and a Category of Data Warehouse.
      
      The move was successful if event number 31570, 31558, or 31554 exists.
      
      There is an issue accessing the data warehouse database if event numbers 31563, 31551, 31569, or 31552 exists.
      
      

4. Check events in Operations Manager:

   1. In the Operations console, select Monitoring.
      
      
   2. Navigate to Monitoring, Operations Manager, Health Service Module Events, and then to Performance Data Source Module Events.
      
      
   3. Search the Performance Data Source Module Events pane for events with a Date and Time that is later than the move.
      
      There is a problem with the data warehouse database if events have a Source of Health Service Module and an Event Number of 10103.
      
      

-----
For additional resources, see Information and Support for System Center 2012. 

Tip: Use this query to find online documentation in the TechNet Library for System Center 2012. For instructions and examples, see Search the System Center 2012 Documentation Library.
-----