none
How to migrate the existing SCOM 2016 to SQL cluster from standalone RRS feed

  • Question

  • Hello,

    We have SCOM 2106 installed with two servers (MS and SQL) based on less number of target servers to be monitored.

    1) Management server - MS and Operations console and Web console

    2) SQL server - Operations DB, Data warehouse DB, SQL services, Reporting server. 

    Now we have a requirement to add more number of servers (1000) so we need to scale it to High availability.

    so proposing the below servers as per best practise, but need help with the steps to move existing SQL from standalone to cluster and add additional MS server.

    Management servers - 2

    SQL servers - 2 with cluster (always-on)

    Reporting server - 1 (also holds Webconsole)

    Need some clue where to start and how to proceed without any loss to the monitoring data.

    Regards

    Suresh Kumar.

    Wednesday, June 6, 2018 8:42 PM

Answers

  • I would personally add additional Management Servers after the migration.

    Step 4

    We make sure that the Management Server(s) are able to connect to the new SQL Server, by updating the registry and configuration files.

    The following steps will be done on the Management Server(s)

    • Open up the regedit (with administrator rights) and then go 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.
    • Then 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.
    • Next go to %ProgramFiles%\System Center 2016\Operations Manager\Server\ and open the ConfigService.config file in notepad and change the following:
      <Category Name="Cmdb"> Change ServerName value to ServerName\InstanceName of the new SQL server. ChangePortNumber to 1433
      <Category Name="ConfigStore"> Change ServerName value to ServerName\InstanceName of the new SQL server, Change PortNumber to 1433.

    Step 5

    We will make sure that the database tables are up to date.

    • Open up the SQL Server Management Studio on the NEW SQL Server. Go to the  OperationsManager database and expand Tables.

    • Now 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.
    • Then 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.

    Step 6

    On the NEW SQL Server open up the SQL Server Management Studio, collapse the Databases and then go to 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):

    SCOM Read

    • apm_datareader
    • apm_datawriter
    • db_datareader
    • dwsynch_users

    SCOM Write

    • db_datareader
    • db_datawriter
    • db_ddladmin
    • dbmodule_users

    SCOM DAS

    • ConfigService
    • db_accessadmin
    • db_datareader
    • db_datawriter
    • db_ddladmin
    • db_securityadmin
    • sdk_users
    • sql_dependency_subscriber

    Then run the following command on the NEW OperationsManager database instance:

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'clr enabled', 1;
    GO
    RECONFIGURE;
    GO

    Next run this SQL query:

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


    If the result of the "is_broker_enabled=0" then run the SQL queries below:

    ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE OperationsManager SET ENABLE_BROKER
    ALTER DATABASE OperationsManager SET MULTI_USER

    Now to make sure, run the following SQL query again:

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

    If the result of the "is_broker_enabled=1" then we're all good.


    Blog: https://thesystemcenterblog.com  LinkedIn:   

    Wednesday, June 6, 2018 9:28 PM
  • Hi!

    Migrating your SCOM database should be fairly straightforward, I will list the steps on a high level below:

    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.

    Since you seem to not have too big of an environment and only one management server, I believe the migration shouldn't take too long.

    Below is a few good links that will help you out:

    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com  LinkedIn:   

    Wednesday, June 6, 2018 8:57 PM

All replies

  • Hi!

    Migrating your SCOM database should be fairly straightforward, I will list the steps on a high level below:

    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.

    Since you seem to not have too big of an environment and only one management server, I believe the migration shouldn't take too long.

    Below is a few good links that will help you out:

    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com  LinkedIn:   

    Wednesday, June 6, 2018 8:57 PM
  • Hi,

    Just follow this guide to 'To install additional management servers in the management group'

    How to install an Operations Manager management server

    https://docs.microsoft.com/en-us/system-center/scom/deploy-install-mgmt-server?view=sc-om-1801


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, June 6, 2018 9:00 PM
  • Thank you so much for your quick reply - would you mind sharing few more details about the steps 4-6? - Also please let me know adding additional MS server should go first or moving DB to cluster is first?
    • Proposed as answer by Leon Laude Wednesday, June 6, 2018 9:11 PM
    • Unproposed as answer by Leon Laude Wednesday, June 6, 2018 9:11 PM
    Wednesday, June 6, 2018 9:04 PM
  • every details you need are provided on the links Leon posted.

    You should add your new MS after moving the DB, mostly because by doing so you won't have to reconfigure this extra server to use the new db.



    • Edited by CyrAz Wednesday, June 6, 2018 9:17 PM
    Wednesday, June 6, 2018 9:17 PM
  • I would personally add additional Management Servers after the migration.

    Step 4

    We make sure that the Management Server(s) are able to connect to the new SQL Server, by updating the registry and configuration files.

    The following steps will be done on the Management Server(s)

    • Open up the regedit (with administrator rights) and then go 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.
    • Then 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.
    • Next go to %ProgramFiles%\System Center 2016\Operations Manager\Server\ and open the ConfigService.config file in notepad and change the following:
      <Category Name="Cmdb"> Change ServerName value to ServerName\InstanceName of the new SQL server. ChangePortNumber to 1433
      <Category Name="ConfigStore"> Change ServerName value to ServerName\InstanceName of the new SQL server, Change PortNumber to 1433.

    Step 5

    We will make sure that the database tables are up to date.

    • Open up the SQL Server Management Studio on the NEW SQL Server. Go to the  OperationsManager database and expand Tables.

    • Now 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.
    • Then 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.

    Step 6

    On the NEW SQL Server open up the SQL Server Management Studio, collapse the Databases and then go to 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):

    SCOM Read

    • apm_datareader
    • apm_datawriter
    • db_datareader
    • dwsynch_users

    SCOM Write

    • db_datareader
    • db_datawriter
    • db_ddladmin
    • dbmodule_users

    SCOM DAS

    • ConfigService
    • db_accessadmin
    • db_datareader
    • db_datawriter
    • db_ddladmin
    • db_securityadmin
    • sdk_users
    • sql_dependency_subscriber

    Then run the following command on the NEW OperationsManager database instance:

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'clr enabled', 1;
    GO
    RECONFIGURE;
    GO

    Next run this SQL query:

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


    If the result of the "is_broker_enabled=0" then run the SQL queries below:

    ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE OperationsManager SET ENABLE_BROKER
    ALTER DATABASE OperationsManager SET MULTI_USER

    Now to make sure, run the following SQL query again:

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

    If the result of the "is_broker_enabled=1" then we're all good.


    Blog: https://thesystemcenterblog.com  LinkedIn:   

    Wednesday, June 6, 2018 9:28 PM
  • Recommend sequence of work
    1. moving SCOM DataBases Migration to SQL Cluster. for detail, you may refer to Abdul Karim blog in which it has detail step by step guide
    https://sites.google.com/site/scomblogs/scom_db_migration_to_sql_cluster
    2. Adding additional management server
    Roger
    Thursday, June 7, 2018 8:09 AM