Export (0) Print
Expand All

Moving the Operational Database

Updated: November 1, 2013

Applies To: Operations Manager for System Center 2012, System Center 2012 R2 Operations Manager, 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

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.

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

  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

      noteNote
      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

-----
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.
-----
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft