Change location or Move OperationsManger Database in Operations Manager 2007.

In some situations we need to move OperationsManger Database to another computer for different reasons, for example:

• Hardware Limitations
• Database and Root Management server is on the same server.
• Fault Tolerance

Procedure:

1. Back up Operations Manager database including the Master.
2. Back up the encryption key on the root management server by using the SecureStorageBackup.exe utility.
3. Verify you have installed and configured the new SQL Server Instance as the existing one.
4. Stop the Operations Manager Services (OpsMgr Config Service, OpsMgr SDK Service, and OpsMgr Health Service for Root Management Servers and OpsMgr Health Service for Management Servers) on the Management Servers in the Management Group before proceeding.
   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.
5. Take Offline the Operations Manager database in current server.
6. Restore Operations Manager in new SQL Server with the same name.
7. In New SQL Server navigate to Security and then expand Logins.
8. Locate the SDK Account, Add the account if it is not listed.
1. Right-click the SDK Account, and select Properties.
2. In the Login Properties dialog box, in the Select a page pane, select User Mapping.
3. In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManager (default name).
4. 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.
5. Click OK to save your changes and to close the Login Properties dialog box.
9. 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.
1. Right-click the Action Account, and select Properties.
2. In the Login Properties dialog box, in the Select a page pane, select User Mapping.
3. In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManager (default name).
4. In the Database role membership for: OperationsManager list, ensure that the following items are selected: db_datareader, db_datawriter, db_ddladmin, and dbmodule_users.
5. Click OK to save your changes and to close the Login Properties dialog box
10. Locate the Data Warehouse Action Account, and add the account if it is not listed.
1. Right-click the Data Warehouse Action Account, and select Properties.
2. In the Login Properties dialog box, in the Select a page pane, select User Mapping.
3. In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManager (default name).
4. In the Database role membership for: OperationsManager list, ensure that the following items are selected: db_datareader and dwsynch_users.
5. Click OK to save your changes and to close the Login Properties dialog box.
11. 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.
12. Expand Databases, OperationsManager, and Tables.
13. 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.
14. Change the value in the SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C5930B04 column to reflect the name of the new SQL Server-based computer.
     
15. Log on to the management server with Administrator permissions.
16. Click Start, select Run, type regedit in the Open box, and then click OK to start Registry Editor.
17. 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.
18. After you have completed this step on all Management Servers in the Management Group, restart the OpsMgr Config Service, OpsMgr SDK Service and OpsMgr Health Service on the Root Management Server and restart only the OpsMgr Health Service on the remaining Management Servers.
    IMPORTANT: Do not start the OpsMgr Config Service and OpsMgr SDK Service on the Management Servers, as these services should only be running on the Root Management Server.
19. In SQL Server run the following SQL query:
    SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'
20. if the query result is ‘0’, the Sql Broker is disabled and you must re-enable it using the following procedure.
1. Open SQL Server Management Studio.
2. Click New Query. enter the following query:
   ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
3. Click Execute.
4. Enter the following query:
   ALTER DATABASE OperationsManager SET ENABLE_BROKER
5. Click Execute.
6. 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.
7. Open SQL Server Management Studio.
8. Click New Query. Enter the following query:
   ALTER DATABASE OperationsManager SET MULTI_USER
9. Click Execute.
10. 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: OpsMgr SDK Service, OpsMgr Config Service, and OpsMgr Health Service. You may have to restart the following services: SQL Server and SQL Server Agent.

See:
http://technet.microsoft.com/en-us/library/cc540384.aspx