Tuesday, June 27, 2017

SCOM 2016 - Moving the Operations Database

There may come a time where you require to move your Operational database from one machine to another. In my case I have outgrown my existing SQL server and need to move it to a server that has more headroom. This is going to be the first of two segments where I will cover the migration of the Operations, Data Warehouse and Reporting databases to an alternate SQL server.

At a high level the process is fairly straight forward.
  1. Stop the Operations Manager services
  2. Create the backup of the Ops Database
  3. Restore the backup of the Ops Database on new hardware
  4. Update the registry and configuration files
  5. Update the Database tables
  6. Update credentials in SQL
  7. Start Operations Manager services
Depending on how many management servers you have and how large the database is this process could take a while and monitoring will be down during this time, so plan accordingly. Lets get started

Stop the Operations Manager services:
On ALL of your management servers go to services and stop the following:
  • System Center Data Access
  • Microsoft Monitoring Agent
  • System Center Management Configuration
Create the backup of the Ops Database:
On the SQL server that currently hosts the Operations database log into SQL Server Management Studio with an administrator account. Expand the SQL Server then Databases. Right Click on OperationsManager (assuming you left the name default, select the appropriate db if not) then Tasks > Back Up...

The Back Up Database Wizard will start. On the General page, In the Database field verify that OperationsManager is selected. Otherwise grab the pulldown and choose the correct database. Backup type is Full, Backup component is Database, Back up to Disk and select an appropriate location to save to (I left it default).

On the Media Options Page Select Back up to new media set, and erase all existing backup sets. Give it a name and description. For Reliability select Verify backup when finished and Click OK

Depending on how large the database is this could take a while. You will get a success message when finished.

Tip: If you want to save some time in the following segment, repeat this step for OperationsManagerDW, SCOMReports and SCOMReportsTempDB. 

Copy all four of the .bak files you just created from the old SQL server to the new SQL server and you are ready for the next step.

Restore the backups onto new hardware:
Once you have copied the .bak files to the new SQL server open SQL Server Management Studio (on the new machine) Right Click on Databases and Select Restore Database...

When the Restore Database Wizard starts you will need to select device since the destination server has no backup history information you will not be able to select Database. Click on the ... and navigate to the location of the OperationsManager.bak file created earlier. Since we are moving to a new server leave everything else default and Click OK

Success is good

Verify that the database exists and it is running

Update the Registry and Configuration Files:
The following steps will need to be performed on ALL management servers to ensure they will be able to connect to the new SQL server successfully.
  1. Run regedit on the management servers from an elevated command prompt. Navigate to HKEY Local Machine > Software > Microsoft > System Center > 2010 > Common > Database. Change DatabaseServerName to the ServerName\InstanceName,PortNumber of the new SQL server. If you left it default, the PortNumber should be 1433.
  2. Now navigate to HKEY Local Machine > Software > Microsoft > Microsoft Operations Manager > 3.0 > Setup. Change DatabaseServerName to the ServerName\InstanceName,PortNumber of the new SQL server.
  3. Next go to %ProgramFiles%\System Center 2016\Operations Manager\Server\ Open the ConfigService.config file in notepad and change the following:
    1. <Category Name="Cmdb"> Change ServerName value to ServerName\InstanceName of the new SQL server. ChangePortNumber to 1433
    2. <Category Name="ConfigStore"> Change ServerName value to ServerName\InstanceName of the new SQL server, Change PortNumber to 1433
Update the Database Tables:
Now go back to SQL Server Management Studio on the new SQL Server. In Object Explorer expand Databases > OperationsManager > Tables

Right Click on dbo.MT_Microsoft$SystemCenter$ManagementGroup and Edit Top 200 Rows. Scroll to the right and find the column SQLServerName. Change it to the new SQL ServerName\Instance,PortNumber
Right Click on dbo.MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring and Edit Top 200 Rows. Scroll to the right and find the column MainDatabaseServerName. Change it to the new SQL ServerName\Instance,PortNumber


Update Credentials in SQL:
Collapse Databases and expand Security > Logins. Add the following accounts and associated user mappings (if the account did not exist previously, the restore should have set these permissions for you):
  1. SCOM Read
    1. apm_datareader
    2. apm_datawriter
    3. db_datareader
    4. dwsynch_users
  2. SCOM Write
    1. db_datareader
    2. db_datawriter
    3. db_ddladmin
    4. dbmodule_users
  3. SCOM DAS
    1. ConfigService
    2. db_accessadmin
    3. db_datareader
    4. db_datawriter
    5. db_ddladmin
    6. db_securityadmin
    7. sdk_users
    8. sql_dependency_subscriber
Run the following command on the new SQL instance were the OperationsManager db is now running
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Then run the following SQL query
SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'
If the result of is_broker_enabled = 1 skip the following commands and go to the final step
If the result of is_broker_enabled = 0 run the following commands
ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE OperationsManager SET ENABLE_BROKER
ALTER DATABASE OperationsManager SET MULTI_USER
Run the first command again and verify you are set to is_broker_enabled = 1, if not, rerun the commands

Start the Operations Manager Services:
On ALL of your management servers go to services and start the following:
  • System Center Data Access
  • Microsoft Monitoring Agent
  • System Center Management Configuration
Give it a minute for the Data Access service to initialize and open up the console. If everything was done properly you shouldn't get any errors. If you do then review the changes you made, specifically the areas where ServerName\Instance,PortNumber are involved. A typo in any of these places can prevent you from reconnecting to the database.

In the next segment we will move the Data Warehouse database


More to come!

If you like this blog give it a g+1




Contributing Documentation:
Create a Full Database Backup
Restore a Database Backup
How to configure Operations Manager to communicate with SQL Server

No comments:

Post a Comment