• Article

Moving the Operational Database

 

Updated: December 15, 2016

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

Hardware and software updates often mean that it’s time to make changes to your Operations Manager configuration. Moving the operational database is such a change. If your current hardware is failing or out of date and newer hardware is available—or perhaps your organizational policy requires that you move the database to a newer operating system and server—then it’s likely time to move the operational database. In either case, when you move the operational database, you move it to a newer operating system and server. Here’s how to do it.

Moving the operational database requires Microsoft SQL Server configuration. During the move, 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.

In this topic

1. Stop Operation Manager services

2. Create a backup of the old operational database and move it to the new server

3. Restore the operational database on the new server

4. Update the registries and configuration files on the management servers

5. Update the operational database with the new database server name

6. On the new server, update the operational database with the new database server name to specify the location of the Application Performance Monitoring tables

7. Update security credentials on the new server hosting the operational database

8. Start Operation Manager services

To move the operational database

1. Stop Operation Manager services

On all the management servers in the management group, stop the Operations Manager services:

  • System Center Data Access

  • System Center Management

  • System Center Management Configuration

2. Create a backup of the old operational database and move it to the new server

  1. On the original operational database server, use Microsoft SQL Server Management Studio to create a full backup of the operational database. The default name is OperationsManager.

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

  2. Copy the backup file to a local drive of the new database server.

  3. Optionally, on the old server that hosts the operational database, delete the operational database.

3. Restore the operational database on the new server

Do these steps on the new SQL Server:

  1. Use Microsoft SQL Server Management Studio to restore the operational database. (In the previous step, you moved the database backup file to a local drive of the new server.) In this step, you can change the name of the database and choose the file location.

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

  2. In the SQL Server Management Studio, verify that the database is online.

4. Update the registries and configuration files on the management servers

Do these steps on each management server in the management group:

  1. 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. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup.

    4. For each of the following keys, double-click the name, change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your changes.

      • DatabaseServerName

        Note

        If you are using a named instance of SQL Server, be sure to use the ServerName\Instance name format.

    5. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\2010\Common\Database and repeat step d.

    6. Close the Registry Editor.

  2. On each management server, edit the following file:

    %ProgramFiles%\System Center 2012\Operations Manager\Server\ConfigService.config

    In the <Category> tags named “Cmdb” and “ConfigStore”, change the value for ServerName to the name of the new SQL server.

5. Update the operational database with the new database server name

  1. Open SQL Server Management Studio.

  2. Expand Databases, OperationsManager, and Tables.

  3. Right-click dbo.MT_Microsoft$SystemCenter$ManagementGroup, and then click Edit Top 200 Rows.

    Note If the SCOM was installed by upgrading from SCOM 2007 instead of by using the SCOM 2012 install media, you need to modify the dbo.MT_ManagementGroup table instead of the dbo.MT_Microsoft$SystemCenter$ManagementGroup table.

  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 the change.

6. On the new server, update the operational database with the new database server name to specify the location of the Application Performance Monitoring tables

  1. Open SQL Server Management Studio.

  2. Expand Databases, OperationsManager, and Tables.

  3. Right-click dbo.MT_Microsoft$SystemCenter$OpsMgrDB$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-based computer.

  5. Save the change.

7. Update security credentials on the new server hosting the operational database

  1. Expand Security, expand Logins, and then do the following:

    1. Add the data writer account. For more information, see How to Create a SQL Server Login.

    2. Add the action account.

    3. Add the Data Access Service (DAS) computer account, using the form “domain\computername$”.

    4. For the DAS computer account, add the following user mappings:

      • ConfigService

      • db_accessadmin

      • db_datareader

      • db_datawriter

      • db_ddladmin

      • db_securityadmin

      • sdk_users

      • sql_dependency_subscriber

    5. 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 operations 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 log in and database are set to the same values as the previous login as follows:

      Log in Database
      DW Data Writer - apm_datareader

      - apm_datawriter

      - db_datareader

      - dwsynch_users
      Action account - db_datareader

      - db_datawriter

      - db_ddladmin

      - dbmodule_users
      DAS/Configuration account Note: If DAS/Configuration uses the LocalSystem account, specify computer account in form <domain>\<computername>$. - ConfigService

      - db_accessadmin

      - db_datareader

      - db_datawriter

      - db_ddladmin

      - db_securityadmin

      - sdk_users

      - sql_dependency_subscriber

  2. Execute these SQL commands on new Operations database instance:

    sp_configure ‘show advanced options’,1

    reconfigure

    sp_configure ‘clr enabled’,1

    reconfigure

  3. Run the following SQL query:

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

    If the result of this query was an is_broker_enabled value of 1, skip this step. Otherwise, run the following SQL queries:

    ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    ALTER DATABASE OperationsManager SET ENABLE_BROKER

    ALTER DATABASE OperationsManager SET MULTI_USER

8. Start Operation Manager services

On all the management servers in the management group, start the Operations Manager services:

  • System Center Data Access

  • System Center Management

  • System Center Management Configuration

See Also

Making Changes to an Operations Manager Environment
How to Move the Data Warehouse Database