SCOM2007: Moving the Operations Manager Database
There may be certain situations where you need to move the Operations Manager database (OperationsManager) from one SQL Server 2005-based computer to another, including the following:
· The current server is experiencing hardware issues and is not considered reliable.
· The OperationsManager database is collocated on the same server as the Root Management Server and you have decided to dedicate a SQL Server 2005 for the database to improve scalability and performance.
· You need to move the database and log file to a different volume because of space or performance reasons.
· The current server is leased and is due to expire soon.
· New hardware standards have been developed and approved, and you must upgrade your SQL Server-based computer to the new hardware specification.
SQL Server 2005 supports 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 2005 documentation at https://go.microsoft.com/fwlink/?LinkId=93787. The following section describes the procedures to move the Operations Manager database from one SQL Server-based computer to another.
Before You Start
Go through this checklist before moving the OperationsManager database.
· Make a current backup of all databases, especially the master database, from their current location.
· Verify you have system administrator permissions on both SQL Server-based computers.
· Verify you have installed and configured the new SQL Server-based computer as the existing one.
· Verify you know the name and current location of the Operations Manager database.
· Stop the OpsMgr 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.
· Backup the secure encryption key on the Root Management Server using the SecureStorageBackup.exe utility.
· Stop SQL Service if you want to copy the .mdf and .idf files to a different location.
Note: If the SDK account is "LocalSystem" on the original system, you need to add that account to the new system. Specify OperationsManager as the default database for that account.
Detaching the Database
Perform the following steps on the SQL Server-based computer currently hosting the OperationsManager database:
1. Open SQL Server Management Studio.
2. In the Connect to Server dialog box, select the appropriate values in the Server type list, in the Server name list, and in the Authentication list, and then click Connect.
3. Right-click the OperationsManager database, point to Tasks, and then click Detach.
Note: This menu is visible only if you are a member of the sysadmin fixed server role and the server to which you are connected is SQL Server 2005-based.
4. In the Detach Database dialog box, check the status of the database. To successfully detach a database, the Status should be The database is ready to be detached. Optionally, you can select to update statistics prior to the detach operation.
5. To terminate any existing connections from the database, click Clear.
6. Click OK. The database node for the detached database is removed from the Database folder.
7. Once the database has been detached, you can copy the OperationsManager.mdf and OperationsManager.ldf files to a destination directory on the new SQL Server-based computer.
Note: The default location of the OperationsManager.mdf is Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Dataand the default location of the OperationsManager.ldf is Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.
Attaching the Database
Perform the following steps on the SQL Server-based computer that will be hosting the OperationsManager database:
1. Open SQL Server Management Studio.
2. In the Connect to Server dialog box, select the appropriate values in the Server type list, in the Server name list, in the Authentication list, and then click Connect.
3. Right-click the Databases folder, and then click Attach.
Note: This menu is visible only if you are a member of the sysadmin fixed server role and the server to which you are connected is SQL Server 2005-based.
4. In the Attach Database dialog box, click Add to specify the database to be attached.
5. In the Local Database Files dialog box, select the disk drive were the database resides and expand the directory tree to find and select the OperationsManager.mdf file.
6. Click OK, and the database node for the attached database will appear when you refresh Object Explorer by clicking Refresh on the View menu.
Update the Database Server Name on the Management Servers
After you have successfully moved the OperationsManager database, update the Registry on each Management Server in that Management Group to reference the new SQL Server-based computer.
1. Log onto 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.
Note: Before editing the Registry, follow your site's backup policies with regard to the registry.
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.
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 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. .
Update the dbo.MT_ManagementGroup table
1. On the Windows desktop, click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
2. In the Connect to Server dialog box, in the Server Type list, select Database Engine; in the Server Name list, select the server and instance for your new Operations Manager database (for example, computer\INSTANCE1); in the Authentication list, select Windows Authentication; and then click Connect.
3. In the Object Explorer pane, expand Databases, expand OperationsManager, and then expand Tables.
4. Right-click dbo.MT_ManagementGroup, and then click Open Table.
5. In the results pane, scroll to the right to the column titled SQLServerName_<guid> .
6. In the first row, update the value to reflect the name of the new database server name, replacing the name of the old server.
7. Click File, and then click Exit.
Setting ENABLE_BROKER
Before you can run tasks and use the Discovery Wizard to install agents, you need to set the ENABLE_BROKER value:
1. Open SQL Server Management Studio.
2. In the Connect to Server dialog box, select the appropriate values in the Server type list, in the Server name list, in the 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 may have to manually kill 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, in the Server name list, in the 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: OpsMgr SDK Service, OpsMgr Config Service, and OpsMgr Health Service. You may have to restart the following services: SQL Server and SQL Server Agent.
Backup Custom Management Packs
Perform the following steps to backup your custom Management Packs.
1. Log on to a Management Server with an account that is a member of the Operations Manager Administrators role for the Operation Manager 2007 Management Group.
2. In the Operations Console, click Administration.
3. In the Administration pane, click Management Packs.
4. In the Management Packs pane, right-click the Management Pack you want to export, and then click Export Management Pack.
5. In the Save As dialog box, type the path and file name for the Management Pack file, or click Browse to save the file to a different directory, and then click Save.
J.C. Hornbeck