How to Move 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

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

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.

Operational Database Relocation

Use the procedure below to move the operational database to a different server.

To move the operational database

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

2. 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).

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 original server that hosts the operational database, delete the operational database.

5. On the new server, use Microsoft SQL Server 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. Update the registry on each management server in the management group 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\Setup, double-click the following names, 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. 
      
      
      • DatabaseName
        
        
      • DatabaseServerName
        
        

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

   4. Close the Registry Editor.
      
      

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

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

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

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

11. Also in Logins, add the action account.

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

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

    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 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 login and database are set to the same values as the previous login: DW Data Writer: apm_datareader, apm_datawriter, db_datareader, dwsynch_users Action account: db_datareader, db_datawriter, db_ddladmin, dbmodule_users DAS/Configuration account: ConfigService, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, sdk_users, sql_dependency_subscriber If DAS/Configuration uses the LocalSystem account, specify computer account in form <domain>\<computername>$.

14. Execute the following SQL commands on new Operations database instance:

    sp_configure ‘show advanced options’,1

    reconfigure

    sp_configure ‘clr enabled’,1

    reconfigure

15. Run the following SQL query:

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

16. If the result of the preceding 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

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

See Also

Concepts

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

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