Moving SCOM 2012 R2 Data Warehouse Database to New SQL Server Cluster

This is the second of two posts in regards to moving Operations Manager 2012 R2 Databases.  The first post about moving the OperationsManager database can be found here.  I won’t rehash any of that information but will instead just get right into the How To portion.  The TechNet article for moving the SCOM DataWarehouse Database can be found here.

  1. Stop the following services on OPSMGR01 and change their type to Manual.  Otherwise they will restart and cause you problems.
    1. System Center Data Access
    2. System Center Management Configuration
  2. Stop the following services on OPSMGR02 and change their type to Manual.  Otherwise they will restart and cause you problems.
    1. System Center Data Access
    2. System Center Management Configuration
  3. On OLDSQLServer, use SQL Server Management Studio to create a full backup of the data warehouse database. The default name is OperationsManagerDW. We recommend that you also back up the associated master database.
    1. Copy backup file to \\SQLCLUSTERNODE1\SHARE
  4. Open SQL Management Studio on SQLCLUSTERNODE1 and connect to SQLCLUSTER\SCOMDW Instance
    1. Restore OperationsManagerDW Database using SQL
  5. Backup registry on OPSMGR01
  6. Backup registry on OPSMGR02
  7. Update Registry on OPSMGR01
    1. Regedit
    2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup
    3. For each of the following keys, double-click the name, change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your changes.
      1. DatabaseWarehouseDBServerName: SQLCLUSTER\SCOMDW
      2. DatabaseName: OperationsManagerDW
  8. Update Registry on OPSMGR02
    1. Regedit
    2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup
    3. For each of the following keys, double-click the name, change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your changes.
      1. DatabaseWarehouseDBServerName: SQLCLUSTER\SCOMDW
      2. DatabaseName: OperationsManagerDW
  9. Login to the server you are using for reporting off of the SCOM Data Warehouse
    1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center Operations Manager\3.0\Reporting,\ DWDBInstance double-click the name and change the value to the hostname of the SQL Server-based computer now hosting the operations manager DW database, and then click OK to save your change.
      1. In my case that would be SQLCLUSTER\SCOMDW
    1. Start the System Center Data Access Service on OPSMGR01 associated with the reporting server. This is needed to access the reports page
    2. On reporting server, change the connection strings.
      1. Open a browser and go to the reporting webpage
      2. Click Show Details and then click Data Warehouse Main. Change the Connection String to contain the new data warehouse server name, and then click Apply. SQLCLUSTER\SCOMDW.
      3. Test the connection
      4. Click Application monitoring, and then click .NET monitoring.
      5. Click AppMonitoringSource.
      6. On the AppMonitoringSource page, click Properties and change Connection string to contain SQLCLUSTER\SCOMDW, and then click Apply.
      7. Test the connection
      8. Close the browser.
    3. On SQLCLUSTERNODE1 update the OperationsManager database table. (Note that this is NOT the DataWarehouse instance!)
      1. Open SQL Server Management Studio. SQLCLUSTER\SCOM
      2. Expand Databases, OperationsManager, and Tables.
      3. Right-click dbo.MT_Microsoft$SystemCenter$DataWarehouse, and then click Edit Top 200 Rows.
      4. Change the value in the MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F column to SQLCLUSTER\SCOMDW
    4. Update the OperationsManager database for Application Performance Monitoring functionality.
      1. Right-click dbo.MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring, and then click Edit Top 200 Rows.
      2. Change the value in the MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A column to SQLCLUSTER\SCOMDW
      3. Do the same for the following tables.
      4. Right-click dbo. MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring_Log and then click Edit Top 200 Rows. Change the value of column Post_MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A to SQLCLUSTER\SCOMDW
      5. Right-click dbo.MT_Microsoft$SystemCenter$DataWarehouse_Log and then click Edit Top 200 Rows. Change the value of column.Pre_MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F TO SQLCLUSTER\SCOMDW.
      6. Close SQL Server Management Studio.
    5. On SQLCLUSTERNODE1, update the member database.
      1. Open SQL Server Management Studio and connect to SQLCLUSTER\SCOMDW instance.
      2. Expand Databases, OperationsManagerDW, and Tables.
      3. Right-click dbo.MemberDatabase, and then click Edit Top 200 Rows.
      4. Change the value in the ServerName column to reflect the name of the new SQL Server, SQLCLUSTER\SCOMDW
      5. Close SQL Server Management Studio.
    6. Update security credentials on SQLCLUSTER\SCOMDW
      1. All of my security credentials carried over and I had no issues.  However, your environment may be completely different.  Definitely make sure to read this section in the TechNet documentation
    7. Stop SQL Services relating to SCOM on OLDSQLServer
    8. Open PowerShell.  Type Write-Host “Bye Kitty” to sacrifice a kitten
    9. Restart OPSMGR01 and OSPMGR02 (this was to ensure registry setting changes were picked up)
    10. Start the following services on OPSMGR01 and change their startup type to Automatic
      1. System Center Data Access
      2. System Center Management Configuration
    11. Start the following services on OPSMGR02 and change their startup type to Automatic
      1. System Center Data Access
      2. System Center Management Configuration
    12. Spam refresh OperationsManager event log on OPSMGR01.  Hopefully you see lots of informational messages and no errors about connections to the SQL Server.

To verify a successful move of the Operations Manager Data Warehouse Database:

  1. Verify that you can successfully run a report from the console.
  2. Ensure that the health state of all management servers in the management group are Healthy.
  3. If the health state of any management server is Critical, open Health Explorer, expand Availability – <server name>, and then continue to expand until you can navigate to Data Warehouse SQL RS Deployed Management Pack List Request State. Check the associated events to determine if there is an issue accessing the data warehouse database.
  4. Check operating system events:
    1. Open the operating system’s Event viewer. Navigate to Event Viewer, and then to Operations Manager.
    2. In the Operations Manager pane, search for events with a Source of Health Service Module and a Category of Data Warehouse.
  1. The move was successful if event number 31570, 31558, or 31554 exists.
  2. There is an issue accessing the data warehouse database if event numbers 31563, 31551, 31569, or 31552 exists.
  3. Check events in Operations Manager:
    1. In the Operations console, select Monitoring.
    2. Navigate to Monitoring, Operations Manager, Health Service Module Events, and then to Performance Data Source Module Events.
    3. Search the Performance Data Source Module Events pane for events with a Date and Time that is later than the move.

 

Leave a Reply