Migrating Operations Manager 2012 R2 Databases to New Database Server

As we had to migrate the System Center Operations Manager 2012 R2 database to a new SQL cluster.  There is a series of steps we have to follow in order to have a smooth transition. Where these requires a planned downtime for the Operations Manager.

When we will we come across these scenarios(Moving the Operational Database),

• Migrating to a System Center Operations Manager 2012 R2 database to a new SQL cluster.
• System Center Operations Manager 2012 R2 cannot add roles, if the SCOM Databases using a SQL 2014 Cluster Shared Volumes as the SCOM Setup Supports only AlwaysOn or StandAlone, In order to Add features you have to bring them to a Standalone SQL , Add the SCOM features , Take them back to the cluster. In my case I had to add the Operations Manager Web Console role.

Step 1 :

It requires downtime,

Stop the following Operations Manager Services in all the Operations Manager Management Servers.

You will find only two services if the server contains only Operations Manager Management Server Role.

• System Center Data Access
• System Center Management
• System Center Management Configuration

Step 2:

Login to the existing SQL Cluster take a full back up of the database.

• OperationsManager
• OperationsManagerDW

Right Click on the Database and choose Backup Options

Have Recovery Model to Simple.

Step 3:

Now Login to the new SQL Cluster where the Databases has to be migrated , Restore both the databases to the new SQL Cluster.

If you restore the Databases in the same Domain, SQL permissions will get migrated automatically. Please compare the SQL permissions between the SQL servers, Using SQL Server Management Studio.

For example – Data Access Service Account permissions.

Reference – Forcing the SQL Database to get Offline in some cases if restore fails while rewriting the database.

ALTER DATABASE OperationsManagerDW SET OFFLINE WITH ROLLBACK IMMEDIATE

Step 4:

Open Registry on Operations Manager Management Servers and Modify these keys in both Places.

Modify –

• DatabaseServerName
• DataWarehouseDBServerName

Note : if you have modified the database name , You have to Change the Database name Entries for Both as you can can see the above keys in the below screenshot.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup

Modify

• DatabaseName

Note : if you have modified the database name , You have to Change the Database name Entry as you can can see the above key in the below screenshot.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\2010\Common\Database

Step 5:

Open Notepad (Run as administrator), Browse to below Location –

C:\Program Files\Microsoft System Center 2012 R2\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.

Save and Close

note : if you have changed Database Names, Change them as well.

Step 6:

Expand Operationsmanager Database

Right Click dbo.MT_Microsoft$SystemCenter$ManagementGroup

Edit Top 200 Rows,

Edit – SQLServer Name Entry.

Do the same for

dbo.MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring 

Step 7:

Run these SQL Queries –

SQL Query 1  –

sp_configure ‘show advanced options’,1

reconfigure

SQL Query 2 –

sp_configure ‘clr enabled’,1

reconfigure

SQL Query 3 –

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

If the result –  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

Step 8:

Start back the Operations Manager Services,

Open the Operations Manager Console.

Reference Error Log if you Try to add the WebConsole Role without migrating from the Cluster Shared Volumes –

[08:56:19]:    Info:    :Info:  Provided SQL Server is already a physical endpoint
[08:56:19]:    Info:    :Info:Connecting to Remote SQL server DB000
[08:58:28]:    Info:    :Info:Creating db path:

\\DB000\C$\ClusterStorage\DB\MSSQL12.MSSQLSERVER\MSSQL\Data 

[09:16:46]:    Error:    :Could not create valid path:

\\DB000\C$\ClusterStorage\DB\MSSQL12.MSSQLSERVER\MSSQL\Data:  

Threw Exception.Type: System.IO.IOException, Exception Error Code: 0x80070043, Exception.Message: The network name cannot be found.

[09:16:46]:    Error:    :StackTrace:   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.Directory.InternalCreateDirectory(String fullPath, String path, Object dirSecurityObj, Boolean checkHost)
at System.IO.Directory.InternalCreateDirectoryHelper(String path, Boolean checkHost)
at Microsoft.EnterpriseManagement.OperationsManager. SetupCommon. SetupUtils.CreateDirectoryForDatabase

(String physicalSqlServerInstance, String localPath, Boolean& createdDirectory)
[09:16:46]:    Error:    :Error:Could not create the directories for the specified DB Path
[09:16:46]:    Always:    :User does not have sufficient DB Access for Avicode Monitoring to succeed.