SCOM / OpsMgr : Moving the Operations Manager DB – Notes from the field

June 18, 2012 at 12:21 pm in Operations Manager by Christopher Keyaert

Last week, I had to move the Operations Manager DB (OpsDB) from one of my customer from SQL Server 2005 to SQL Server 2008. On Internet, you have a lot of person who already shared their experience, findings, problems about that migration.

I would like now to share my notes, compilation of posts coming from different sites (URL are provided at the end), that I used to successful migrate that DB.

/!\ The scope of this note is only the OperationsManger DB, not the Reporting DB, not the Datawarehouse DB. /!\


Stopping the services

  • Stop the System Center Management, System Center Data Access, and System Center Management Configuration services on the root management server and the System Center Management service on all the management servers.

Backup/Restore the DB

  • Start a backup /restore of the Operations Manager DB, from the SQL Server 2005 to the SQL Server 2008. Nothing Special at this point, just a classical backup/restore. Ensure to copy also all the existing the permissions.

Updating the registry

The following actions have to be taken on the RMS and all the MS.

  • Log on to the management server with Administrator permissions.
  • Click Start, select Run, type regedit in the Open box, and then click OK to start Registry Editor.
  • 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. If you are using a named instance of SQL Server, be sure to use the ServerName\Instance name format.
  • Click OK.
  • Close the Registry Editor.

Updating DB

On the SQL Server 2008, 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.
    1. Open SQL Server Management Studio.
    2. Expand Databases, OperationsManager, and Tables.
    3. 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.
    4. Change the value in the SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C5930B04 column to reflect the name of the new SQL Server-based computer.

Enable  CLR functions

In order to support regular expressions in Operations Manager SQL queries, the development team needed to create CLR functions that use .NET’s RegEx library. 
Operations Manager Setup configures SQL to allow execution of the CLR code. When the customer in this case moved the database from one SQL server to another they 
lost this setting in the OperationsManager Database.

To resolve this issue, on the SQL Server 2008, run the following query on the OperationsManager database:

sp_configure @configname=clr_enabled, @configvalue=1
GO

Let that command execute successfully then run

RECONFIGURE
GO

Add back the missing information into the MASTER database

After moving your OperationsManager Database–you might find event 18054 errors in the SQL server application log. Because in this process – we simply restore the Operations Database ONLY, we do not carry over some of the modifications to the MASTER database that are performed when you run the Database Installation during setup to create the original operations database.

To resolve this – you should run the attached SQL script against the Master database of the SQL instance that hosts your OperationsManager Database

http://scug.be/christopher/files/2012/06/Fix_OpsMgrDB_ErrorMsgs.zip

Check the permissions on the DB

Just check if the permissions of the new server are ok.

  1. On the new server hosting the OperationsManager database, add the correct permission for the login of the root management server on which the SDK Account is running, as follows:
    • Open Microsoft SQL Server Management Studio, and in the Object Explorer pane, navigate to Security and then expand Logins.
    • Locate the SDK Account, and add the account if it is not listed.
    • If the SDK Account is running as LocalSystem, use the format <domain\computername$> in SQL Logins, where <computername> is the name of the root management server.
    • Right-click the SDK Account, and select Properties.
    • In the Login Properties dialog box, in the Select a page pane, select User Mapping.
    • In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManager (default name).
    • 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.
    • Click OK to save your changes and to close the Login Properties dialog box.
  1. On the new server hosting the Operations Manager database, add the correct permission for the login of the root management server on which the Action Account is running, as follows:
    • Open Microsoft SQL Server Management Studio, and in the Object Explorer pane, navigate to Security and then expand Logins.
    • 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.
    • Right-click the Action Account, and select Properties.
    • In the Login Properties dialog box, in the Select a page pane, select User Mapping.
    • In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManager (default name).
    • In the Database role membership for: OperationsManager list, ensure that the following items are selected: db_datareader, db_datawriter, db_ddladmin, anddbmodule_users.
    • Click OK to save your changes and to close the Login Properties dialog box.
  1. On the new server hosting the Operations Manager database, add the correct permission for the login of the Data Warehouse server on which the Data Warehouse Action Account is running, as follows:
    • Open Microsoft SQL Server Management Studio, and in the Object Explorer pane, navigate to Security and then expand Logins.
    • Locate the Data Warehouse Action Account, and add the account if it is not listed.
    • Right-click the Data Warehouse Action Account, and select Properties.
    • In the Login Properties dialog box, in the Select a page pane, select User Mapping.
    • In the Users mapped to this login list, in the Map column, select the box that corresponds to OperationsManager (default name).
    • In the Database role membership for: OperationsManager list, ensure that the following items are selected: db_datareader and dwsynch_users.
    • Click OK to save your changes and to close the Login Properties dialog box.

Set Enable BROKER

Before you can run tasks and use the Discovery Wizard to install agents, you need to set the ENABLE_BROKER value.After moving the Operations Manager database, the status of the Sql Broker Availability Monitor might be set to Critical or Sql Broker is disabled. You can check the state of the Sql Broker Availability Monitor by running the following SQL query:

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

Where ‘OperationsManager’ is the default database name, replace this name as appropriate. If the query result is ‘0’, the Sql Broker is disabled and you must re-enable it using the following procedure.

To set ENABLE_BROKER

  1. Open SQL Server Management Studio.
  2. In the Connect to Server dialog box, select the appropriate values in the Server type list, Server name list, and 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 might have to manually stop any process that is connected to the database before completing the ALTER query below.

  1. Open SQL Server Management Studio.
  2. In the Connect to Server dialog box, select the appropriate values in the Server type list, Server name list, and Authentication list, and then click Connect.
  3. Click New Query.
  4. In the query window, enter the following query:
    ALTER DATABASE OperationsManager SET MULTI_USER
  5. 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’

Starting the services

  • On all management servers in the management group, restart the System Center Management, System Center Data Access, and System Center Management Configuration services on the root management server, and then restart only the System Center Management service on the remaining management servers.

Conclusion

Normally, if you followed the notes above, the move of your Operations Manager DB must be successful as the one I did.
As I said, I’m not the author of the notes, I just compiled them into one post. All the information are coming for the links below :

Resources :

http://technet.microsoft.com/en-us/library/cc540384.aspx
http://blogs.technet.com/b/kevinholman/archive/2010/08/26/moving-the-operations-database-my-experience.aspx
http://blogs.technet.com/b/smsandmom/archive/2007/10/11/scom2007-moving-the-operations-manager-database.aspx
http://blogs.technet.com/b/kevinholman/archive/2010/10/26/after-moving-your-operationsmanager-database-you-might-find-event-18054-errors-in-the-sql-server-application-log.aspx

Regards
Christopher