How to Move the OperationsManager Database in Operations Manager 2007

  • Article

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

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

SQL Server 2005 and SQL Server 2008 support the ability to change the location of the data files and of the log files between SQL Server-based computers, between instances on the same SQL Server-based computer, and different volumes on the same SQL Server-based computer. For more information about using this function in SQL Server, see the SQL Server documentation (https://go.microsoft.com/fwlink/?LinkId=93787).

The high-level steps of moving the OperationsManager database are as follows:

  1. Back up the OperationsManager database.

  2. Uninstall the OperationsManager database.

  3. Delete the Operations Manager database.

  4. Restore the OperationsManager database.

  5. Update management servers with the new database server name.

  6. Update the Operations Manager database with the new database server name.

  7. Update the Operations Manager database logins on the new database server. Ensure that for the root management server, the SDK Account and the Action Account are included in the logins and that they have appropriate permissions. If reporting is installed, ensure that the Data Warehouse Action Account has appropriate permissions.

  8. Set ENABLE_BROKER if needed.

  9. Verify that the move is successful by ensuring that the console is displaying valid data.

OperationsManager Database Relocation

Use the procedure below to move the OperationsManager database to a new server.

To move the OperationsManager database

  1. Install and configure a new SQL Server-based computer. Ensure that you have system administrator permissions on both the original SQL Server-based computer and the new SQL Server-based computers.

  2. Back up the following:

    • Back up all databases. On the current server that hosts the Operations Manager database, use SQL Server Management Studio to back up the Operations Manager (default name) database.

    • Back up the encryption key on the root management server by using the SecureStorageBackup.exe utility.

  3. Stop the Operations Manager services (System Center Management, System Center Data Access, and System Center Management Configuration for root management servers, and System Center Management for management servers) on the management servers in the management group.

    In a clustered root management server environment, use Cluster Administrator (Windows Server 2003) or Failover Cluster Management (Windows Server 2008) to configure each of the three services listed above with the Take Offline option.

  4. On the current server that hosts the OperationsManager database, uninstall the database component as follows (these steps do not physically remove the OperationsManager database from SQL Server):

    Note

    Perform this step if the database is the only component on the server. Otherwise, you will still be able to delete the database following the next step.

    1. Click Start, click Control Panel, and then click Add or Remove Programs for Windows Server 2003 or Programs and Features for Windows Server 2008.

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

    3. Complete the wizard.

  5. On the current server that hosts the OperationsManager database, delete the OperationsManager database as follows:

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

    2. Right-click OperationsManager, and then click 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.

    4. Click OK to complete the operation.

  6. On the new server, use Microsoft SQL Server Management Studio to restore the OperationsManager database that you previously backed up. To access the database backup file, copy the backup file to a local drive or map a local drive to the folder that contains the backup file.

  7. Update the registry on each management server in the management group to reference the new SQL Server-based computer. Complete this step also on the root management server. If the root management server is clustered, you must complete this step on all the nodes in the cluster.

    Note

    Before editing the registry, follow your site'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\Setup, double-click the value DatabaseServerName, and then change the value to the hostname of the SQL Server-based computer now hosting the OperationsManager database. If you are using a named instance of SQL Server, be sure to use the ServerName\Instance name format.

    4. Click OK.

    5. Close the Registry Editor.

    6. After you have completed this step on all management servers in the management group, restart the System Center Management, System Center Data Access, and System Center Management Configuration services on the root management server, and then restart only the System Center Management service on the remaining management servers.

      Important

      Do not start the System Center Management Configuration and System Center Data Access services on the management servers, as these services should be running only on the root management server.

  8. Update the OperationsManager database with the New Database Server Name, and ensure that the account that you are logged on with has sufficient privileges on the SQL Server instance.

    1. Open SQL Server Management Studio.

    2. Expand Databases, OperationsManager, and Tables.

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

    4. Change the value in the SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C5930B04 column to reflect the name of the new SQL Server-based computer.

    5. Save your change.

  9. On the new server hosting the OperationsManager database, add the correct permission for the login of the root management server on which the SDK Account is running, as follows:

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

    2. Locate the SDK Account, and add the account if it is not listed.

      Note

      If the SDK Account is running as LocalSystem, use the format <domain\computername$> in SQL Logins, where <computername> is the name of the root management server.

    3. Right-click the SDK Account, and select Properties.

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

    5. In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManager (default name).

    6. In the Database role membership for: OperationsManager list, ensure that the following items are selected: configsvc_users, db_datareader, db_datawriter, db_ddladmin, and sdk_users.

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

  10. On the new server hosting the Operations Manager database, add the correct permission for the login of the root management server on which the Action Account is running, as follows:

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

    2. Locate the Action Account, and add the account if it is not listed. If the Action Account is running as LocalSystem, use the format <domain\computername$> in SQL Logins, where <computername> is the name of the root management server.

    3. Right-click the Action Account, and select Properties.

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

    5. In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManager (default name).

    6. In the Database role membership for: OperationsManager list, ensure that the following items are selected: db_datareader, db_datawriter, db_ddladmin, and dbmodule_users.

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

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

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

    2. Locate the Data Warehouse Action Account, and add the account if it is not listed.

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

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

    5. In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManager (default name).

    6. In the Database role membership for: OperationsManager list, ensure that the following items are selected: db_datareader and dwsynch_users.

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

Set ENABLE_BROKER

Before you can run tasks and use the Discovery Wizard to install agents, you need to set the ENABLE_BROKER value.

After moving the Operations Manager database, the status of the Sql Broker Availability Monitor might be set to Critical or Sql Broker is disabled. You can check the state of the Sql Broker Availability Monitor by running the following SQL query:

SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'

Where ‘OperationsManager’ is the default database name, replace this name as appropriate.

If the query result is ‘0’, the Sql Broker is disabled and you must re-enable it using the following procedure.

To set ENABLE_BROKER

  1. Open SQL Server Management Studio.

  2. In the Connect to Server dialog box, select the appropriate values in the Server type list, Server name list, and Authentication list, and then click Connect.

  3. Click New Query.

  4. In the query window, enter the following query:

    ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  5. Click Execute.

  6. Enter the following query:

    ALTER DATABASE OperationsManager SET ENABLE_BROKER

  7. Click Execute.

  8. Close SQL Server Management Studio.

    Note

    Closing SQL Server Management Studio closes the connection to the database in single-user mode. Depending on your configuration, you might have to manually stop any process that is connected to the database before completing the ALTER query below.

  9. Open SQL Server Management Studio.

  10. In the Connect to Server dialog box, select the appropriate values in the Server type list, Server name list, and Authentication list, and then click Connect.

  11. Click New Query.

  12. In the query window, enter the following query:

    ALTER DATABASE OperationsManager SET MULTI_USER

  13. Click Execute.

You can verify the setting for ENABLE_BROKER is set to 1 by using this SQL query: SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'.

Note

Before you can use discovery, you must restart the following services: System Center Data Access, System Center Management Configuration, and System Center Management Services. You might also need to restart the following services: SQL Server and SQL Server Agent.