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
Login to the existing SQL Cluster take a full back up of the database.
Right Click on the Database and choose Backup Options
Have Recovery Model to Simple.
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
Open Registry on Operations Manager Management Servers and Modify these keys in both Places.
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
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.
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.
Expand Operationsmanager Database
Right Click dbo.MT_Microsoft$SystemCenter$ManagementGroup
Edit Top 200 Rows,
Edit – SQLServer Name Entry.
Do the same for
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
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:
[09:16:46]: Error: :Could not create valid path:
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.