Export (0) Print
Expand All

How to Move the OperationsManagerDW Database in Operations Manager 2007

Updated: May 22, 2009

Applies To: Operations Manager 2007 R2, Operations Manager 2007 SP1

For various reasons, it might be necessary to move the OperationsManagerDW database from its original server to another server.

CautionCaution
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.

The high-level steps to move the OperationsManagerDW database are as follows:

  1. Stop Microsoft System Center Operations Manager 2007 services to prevent updates to the OperationsManagerDW database during the move.

  2. Back up the OperationsManagerDW database to preserve the data that Operations Manager has already collected from the management group.

  3. Uninstall the current Data Warehouse component, and delete the OperationsManagerDW database.

  4. Install the Reporting Data Warehouse component on the new Data Warehouse server.

  5. Restore the original OperationsManagerDW database.

  6. Configure Operations Manager to use the OperationsManagerDW database on the new Data Warehouse server.

  7. Restart Operations Manager services.

Use the procedure below to move the OperationsManagerDW database to a new Data Warehouse server.

To move the OperationsManagerDW database

  1. Stop Operations Manager services as follows:

    1. On the root management server, stop the System Center Data Access, and the System Center Management Configuration Services.

    2. On the root management server and on all other management servers, stop the System Center Management Service.

  2. On the current Data Warehouse server, use SQL Server Management Studio to back up the OperationsManagerDW database (default name) to a shared folder on the server. It is recommended that you also back up the associated master database.

  3. On the current Data Warehouse server, uninstall the Reporting Data Warehouse component as follows:

    1. Click Start, click Control Panel, and then select Add or Remove Programs if you are using Windows Server 2003 or select Programs and Features if you are using Windows Server 2008.

    2. In the Add or Remove Programs for Windows Server 2003 or Programs and Features for Windows Server 2008, dialog box, select System Center Operations Manager 2007 R2 Reporting Server, and then select Change.

    3. In the System Center Operations Manager 2007 Reporting Setup Wizard, on the Operations Manager 2007 R2 Maintenance page, select Modify, and then click Next.

    4. On the Custom Setup page, configure the Data Warehouse component with the This component will not be available option.

    5. Complete the wizard.

      noteNote
      This does not physically remove the OperationsManagerDW database from Microsoft SQL Server.

  4. On the current Data Warehouse server, delete the OperationsManagerDW database as follows:

    1. In Microsoft SQL Server Management Studio, navigate to Databases.

    2. Right-click OperationsManagerDW, and then select Delete.

    3. In the Delete Object dialog box, ensure that the Delete backup and restore history information for databases and Close existing connections options are both selected.

  5. On the new Data Warehouse server, run SetupOM.exe to install the Reporting Data Warehouse component as follows:

    1. On the System Center Operations Manager 2007 R2 Setup page, select Install Operations Manager 2007 R2 Reporting.

    2. In the System Center Operations Manager 2007 R2 Reporting Setup Wizard, on the Custom Setup page, configure only the Data Warehouse component for installation.

      If you are moving the OperationsManagerDW database to a server that is different than the server on which the Operations Manager Reporting component is installed, Configure the Reporting Server component with the This component will not be available option.

  6. On the new Data Warehouse server, delete the OperationsManagerDW database as follows:

    1. In Microsoft SQL Server Management Studio, navigate to Databases.

    2. Right-click OperationsManagerDW, and then select Delete.

    3. In the Delete Object dialog box, ensure that the Delete backup and restore history information for databases and Close existing connections options are both selected.

  7. On the new Data Warehouse server, use SQL Management Studio to restore the OperationsManagerDW database backup (from step 2). Access the database backup by copying the backup to a local drive or by mapping a local drive to the folder that contains the backup.

  8. On the new Data Warehouse server, use SQL Management Studio to create a login for the System Center Data Access Service account, the Data Warehouse Action Account, and the Data Reader Account.

    noteNote
    If localsystem was used as the System Center Data Access account, enter <domain\computername$> in SQL Logins.

  9. On the new Data Warehouse server, add the correct login permission for the computer on which the SDK Service is running, as follows:

    1. In Microsoft SQL Server Management Studio, in the Object Explorer pane, navigate to Security and then expand Logins.

    2. Right-click the account that corresponds to the computer on which the SDK Service is running (for example, if localsystem is used, it will be <domain\computername$>). Select Properties.

    3. In the Login Properties dialog box, in the Select a page pane, select User Mapping.

    4. In the Users mapped to this login list, in the Map column, select the box that corresponds to the OperationsManagerDW database.

    5. In the Database role membership for: OperationsManagerDW list, select OpsMgrReader and db_datareader.

    6. Click OK to save your changes and to close the Login Properties dialog box.

  10. On the new Data Warehouse server, add the correct login permission for the computer on which the Data Reader Account is running, as follows:

    1. In Microsoft SQL Server Management Studio, in the Object Explorer pane, navigate to Security and then expand Logins.

    2. Right-click the Data Reader Account and select Properties.

    3. In the Login Properties dialog box, in the Select a page pane, select User Mapping.

    4. In the Users mapped to this login list, In the Map column, select the box that corresponds to OperationsManagerDW.

    5. In the Database role membership for: OperationsManagerDW list, select OpsMgrReader and db_datareader.

    6. Click OK to save your changes and to close the Login Properties dialog box.

  11. On the new Data Warehouse server, add the correct permission for the login of the computer on which the Data Warehouse Action Account is running, as follows:

    1. In Microsoft SQL Server Management Studio, in the Object Explorer pane, navigate to Security and then expand Logins.

    2. Right-click the Data Warehouse Action Account, and select Properties.

    3. In the Login Properties dialog box, in the Select a page pane, select User Mapping.

    4. In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManagerDW.

    5. In the Database role membership for: OperationsManagerDW list, select the following items: OpsMgrWriter and db_owner.

    6. Click OK to save your changes and to close the Login Properties dialog box.

  12. On the root management server, start the System Center Data Access Service.

  13. On the server running SQL Server Reporting Services, modify the data source as follows:

    1. In Internet Explorer, open http://localhost/reports<$instancename> (include <$instancename> only when using a named instance).

    2. On the SQL Server Reporting Services Home page, ensure that you are viewing the Contents page. Select Show Details.

    3. In the list that is displayed, click Data Warehouse Main.

    4. In the Data Warehouse Main properties page, in the Connection string text box, change the name of the database server to the new Data Warehouse server.

    5. Click Apply.

  14. On the server running SQL Server Reporting Services, update the registry to point to the name of the new Data Warehouse server as follows:

    1. Locate the key HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft Operations Manager\3.0\Reporting.

    2. Double-click the value DWDBInstance, and then modify it to be the name of the new Data Warehouse server.

      If the OperationsManagerDW database was installed on the same server as the Operations Manager Reporting Server, this key does not exist and you need to add it (as a string value).

  15. Configure the Operations Manager database with the name of the new Data Warehouse server as follows:

    1. On the server that hosts the Operations Manager database, open SQL Server Management Studio and navigate to Databases, OperationsManager, and then to Tables.

    2. Right-click dbo.MT_DataWarehouse, and then select Open Table if you are using SQL Server 2005 or select Edit Top 200 Rows if you are using SQL Server 2008.

    3. Change the value in the MainDatabaseServerName_16781F33_F72D_033C_1DF4_65A2AFF32CA3 column to the name of the new Data Warehouse server.

    4. Close SQL Server Management Studio to save your changes.

  16. Configure the OperationsManagerDW database with the name of the new Data Warehouse server as follows:

    1. On the new Data Warehouse server, open SQL Server Management Studio and navigate to Databases, OperationsManagerDW, and then to Tables.

    2. Right-click dbo.MemberDatabase table and select Open Table if you are using SQL Server 2005 or select Edit Top 200 Rows if you are using SQL Server 2008.

    3. Change the value in the ServerName column to the name of the new Data Warehouse server.

    4. Close SQL Server Management Studio to save your changes.

  17. Restart services as follows:

    1. On the root management server, restart the System Center Management Configuration Service.

    2. On all management servers, restart the System Center Management Service.

  18. Verify that the database move was successful as described in the following procedures.

To verify a successful move of the OperationsManagerDW 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 OperationsManagerDW 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 OperationsManagerDW 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 OperationsManagerDW database if events have a Source of Health Service Module and an Event Number of 10103.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft