In my line of work, it is essential to have monitoring software in place that will alert you of potential problems before they occur. Having a preemptive alert allows your team to utilize pro-active management rather than reactive. This is much more desirable in today's "always on, always available" standards.
My team utilizes System Center Operations Manager 2007 R2 for our monitoring solution. This is a very robust monitoring solution offered by Microsoft. Ever since releasing the software, we have seen more proactive management and less surprise hardware and software failures. This software has the ability to monitor hardware, DBMS software, Security software, Exchange, and much more.
The one problem with monitoring software is if the hardware it is hosted on, or the software itself is corrupted or failing, your monitoring solution is irrelevant. In my environment we have had to move the Ops Mgr database multiple times and in this post I plan on giving you the steps required to do so. This is a relatively painless process, but it is crucial that is goes flawlessly.
Before You Start
- Make a backup of the current operations manager database
- Verify that you have administrator rights on both servers where the SQL instances are located
- Verify that you know the location of the current Operations Manager Database in the program files of the current database holding server
- 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
Detach the Database
- In the current host of the Operations manager Database, you will want to detach that database
- Once the database has been detached, locate the database .mdf file and move it to a location on the new server
Attach the Database
- You must now attach the database to the SQL server instance in the new location
Update the Management Servers
- Now that the database is located in a new location, you need to point the management servers to that new location
- This needs to be done on each management server individually
- Login to the management server as an Administrator and run the regedit command
- Inside the registry editor navigate to: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft Operations Manager\3.0\Setup
- Double Click databaseservername and change the value to the new database hosting server
- Hit ok and then close the registry editor
- Restart these services on the Root Management Servers: OpsMgr Config Service, OpsMgr SDK Service, and OpsMgr Health Service
- Restart just this service for Management Servers: OpsMgr Health Service
Update SQL Server
- Start SQL Server Management Studio and connect to the new database host service
- Expand the operations manager database
- Expand the tables
- Find the table : dbo.MT_ManagementGroup and right click and select ‘Edit Top 200 Rows’
- Find the column named: SQLServerName_... and change the value of the first row in that column to the name of the new host server for the database
- Hit Execute and then click File and Exit
Enable Broker
- Open the SQL Server Management Studio and connect to the host database server
- Click New Query in the upper left hand corner
- In the query window input: ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Click Execute
- Now in the query window clear out the previous statement and input: ALTER DATABASE OperationsManager SET ENABLE_BROKER
- Click Execute
- Close Management Studio by hitting File > Exit
- Reopen SQL Server Management Studio and connect once again to the host database server
- Click New Query in the upper left hand corner
- In the query window input: ALTER DATABASE OperationsManager SET MULTI_USER
- Click Execute
- Before continuing, verify that the setting for ENABLE_BROKER is set to 1 by using the following query: SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'
- Close Management Studio by hitting File > Exit
SQL Accounts: User Mapping
- You need to make sure that Operation Manager’s SDK and Action accounts are in the security logins within the host database server
- Open the SQL Server Management Studio and connect to the host database server
- Open the Security folder and then open the Logins folder
- Search for your SDK account and open its properties
- If there is no account, right click and make a new one that uses your SDK service account
- On the left in the Select a Page section, choose User Mapping
- Under the Users mapped to this login list, select the checkbox for the operations manager database
- Under the Database role membership for: OperationsManager list, make sure the following are selected: configsvc_users, db_datareader,db_datawriter, db_ddladmin, andsdk_users
- Click OK
- Now Search for your Action account and open its properties
- If there is no account, right click and make a new one that uses your Action account service account
- On the left in the Select a Page section, choose User Mapping
- Under the Users mapped to this login list, select the checkbox for the operations manager database
- Under the Database role membership for: OperationsManager list, make sure the following are selected: db_datareader,db_datawriter, db_ddladmin, dbmodule_users
- Click OK
