Automating the Move of System Center Operations Manager 2012 Databases using Windows PowerShell

 

In the case that you need to move your System Center Operations Manager 2012 OperationsManager and/or OperationsManagerDW database(s) to a different back-end SQL server, we have documented those step-by-step procedures for you. You can actually find those step-by-step procedures located here for the OperationsManager database ( https://technet.microsoft.com/en-us/library/hh278848.aspx ) and here for the OperationsManagerDW database ( https://technet.microsoft.com/en-us/library/hh268492.aspx ).

What I have done is taken those procedures and coded it into a PowerShell script that you can run in a completely automated fashion. The reason for the development of this script was to provide a solution to a customer that actually needed to have a System Center Operations Manager 2012 infrastructure that could manage servers in multiple geo-located datacenters, and have that infrastructure able to survive a single datacenter outage. I’m going to blog extensively on that solution later, but for now just understand that in this scenario both the OperationsManager and OperationsManagerDW are located on a mirrored SQL database back-end that is replicated throughout the geo-located datacenters. This PowerShell script will automate all of the procedures listed in the above step-by-step URLs. What I’ve tried to do was to identify in the PowerShell script the step number it was listed as in documented steps. Because I was merging both the OperationsManager and OperationsManagerDW in one PowerShell script, the steps may not totally correlate back to the documentation, but if you are familiar with what the documentation is asking to do, the PowerShell script is following those procedures as serial as possible.

A couple of preparation tips before you begin to utilize this script in your environment

  1. Ensure that you are an administrator on all of the Management Servers as well as the SQL Servers
  2. Ensure that the WS-Man protocol is enabled on all of the Management Servers as well as the SQL Servers. I’m using PowerShell Invoke-Commands, so this PowerShell script will be initiating remote sessions on those computers. You may want to run “winrm quickconfig” to enable this.
  3. Ensure that your PowerShell execution policy is set to RemoteSigned or lower for all of the Management Servers as well as the SQL Servers.
  4. Ensure that the machine you plan on executing this PowerShell script has the SQL 2008 R2 client tools installed. When I first began developing this PowerShell script I was using the ADO COM objects to query and update SQL. I have found it much easier to add the SQLServerCmdLetSnapin100 snapin and utilize the Invoke-SQLCmd cmdlet. You’ll want to test that you can add that snapin before running the script in automation.
  5. Ensure that the SQL server that you are moving your database(s) to has the same SQL logins for the System Center Operations Manager 2012 accounts that you have defined during installation. Because we will be using a different SQL server, you must ensure that your SDK, DB Reader, and DB Writer accounts continue to have the same level of access from the previous SQL server used.

 

 Automated OpsMgr 2012 DB Move PowerShell Script Breakdown

I’m going to highlight the PowerShell script key functions. You can download the complete script at the end of the post.

Lines 11 – 34 – Here you will have to change the server names to the servers in your environment. You’ll enter your Management server names, your current SQL Server(s) that host the OperationsManager and OperationsManagerDW, your new SQL Server(s) that will host the OperationsManager and OperationsManagerDW, your Operations Manager Reporting Server, and the path to ConfigService.config on each Management server. In my case the Management servers had the same standard build configuration, so the path was the same on all Management servers.

Line 36 – Adding the SQL PowerShell snapin

Lines 38 – 60 – Stopping all services on all Management Servers

Lines 62 – 86 – Updating all Management Servers registry information to point to the new SQL Server

Lines 88 – 107 – Updating the Operations Manager Report Server to point to the new SQL Server

Lines 109 – 139 – Updating the ConfigService.config XML file on all Management Servers to point to the new SQL Server

Lines 141 – 188 – Updating the OperationsManager and OperationsManagerDW with the new SQL Server name

Lines 190 – 207 – Enabling the SQL Broker for the OperationsManager DB if needed

Lines 210 – 231 – Starting all services on all Management Servers

  1: <#
  2:   Purpose  : Automate the move of System Center Operations Manager 2012 Databases
  3:             
  4:   Author   : pgibson.online@hotmail.com [MSFT]
  5:  
  6:   Disclaimer: THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
  7:               EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
  8:               WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
  9: #>
  10:  
  11: # ************ Set PS Script Environemnt Variables *************************
  12:  
  13: # Management Servers Array
  14: [Array]$MgmtServers ="MGMTSRV01.contoso.com","MGMTSRV02.contoso.com","MGMTSRV03.contoso.com"
  15:  
  16: # Current SQL OperationsManager DB Server
  17: $global:CurrentSQLOpsMgrDBSrv = "SQLSRVOPSDB01.contoso.com"
  18:  
  19: # New SQL OperationsManager DB Server
  20: $global:NewSQLOpsMgrDBSrv = "SQLSRVOPSDB02.contoso.com"
  21:  
  22: # Current SQL OperationsManagerDW DB Server
  23: $global:CurrentSQLOpsMgrDWSrv = "SQLSRVDWDB01.contoso.com"
  24:  
  25: # New SQL OperationsManagerDW DB Server
  26: $global:NewSQLOpsMgrDWSrv = "SQLSRVDWDB02.contoso.com"
  27:  
  28: # Current OpsMgr Report Server
  29: $global:CurrentOpsMgrRptSrv = "OPSMGRRPTSRV01.contoso.com"
  30:  
  31: # Enter the path of the ConfigService.config file of all Management Servers
  32: $global:CngSvcCfgFilePath = "E:\Program Files\System Center 2012\Operations Manager\Server\ConfigService.config"
  33:  
  34: # ************ End PS Script Environemnt Variables *************************
  35:  
  36: add-pssnapin SqlServerCmdLetSnapin100 -EA 0
  37:  
  38: # Step 1 - Stop the Operations Manager services
  39: write-host "Stopping Management Server(s) services."
  40: Invoke-Command -ComputerName $MgmtServers -ScriptBlock {
  41:     $OpsMgr12MgmtSrvSvcArray = @("System Center Data Access Service", "System Center Management", "System Center Management Configuration")
  42:  
  43:     ForEach ($svc in $OpsMgr12MgmtSrvSvcArray)
  44:         {
  45:             write-host "Stopping service $svc on $Env:ComputerName..."
  46:             $SCOpsMgrSvc = Get-Service | ? { $_.DisplayName -eq $svc}
  47:             If ($SCOpsMgrSvc.Status -ne "Stopped")
  48:                 {
  49:                     Stop-Service -DisplayName $svc
  50:                     Do
  51:                         { 
  52:                             Start-Sleep -s 15
  53:                             $SCOpsMgrSvc = Get-Service | ? { $_.DisplayName -eq $svc}
  54:                         } Until ($SCOpsMgrSvc.Status -eq "Stopped")
  55:                 }
  56:             write-host "The service $svc is stopped on $Env:ComputerName."            
  57:         
  58:         }
  59:  } # End Invoke-Command for stopping Management Server services
  60: write-host "Finished stopping Management Server(s) services."
  61:         
  62: # Step 6 - Update registry to refer to the new SQL Server-based computer
  63: write-host "Updating Management Server(s) registry to refer to new SQL Server."
  64: Invoke-Command -ComputerName $MgmtServers -ScriptBlock {
  65:     param(
  66:         [Parameter(Position=0)]
  67:         $LocalVarNewSQLOpsMgrDBSrv
  68:     )
  69:  
  70:     $RegKeyDatabaseServerName = Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Database" -Name "DatabaseServerName"
  71:     
  72:     If ($RegKeyDatabaseServerName.DatabaseServerName -ne $LocalVarNewSQLOpsMgrDBSrv)
  73:         {
  74:             write-host "Changing registry key HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Database\DatabaseServerName on $Env:ComputerName"
  75:             Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Database" -Name "DatabaseServerName" -Value $LocalVarNewSQLOpsMgrDBSrv
  76:             write-host "Completed registry key change HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Database\DatabaseServerName on $Env:ComputerName"
  77:         }
  78:     Else
  79:         {
  80:             write-host "No modification to registry key HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Database\DatabaseServerName needed on $Env:ComputerName"
  81:         }
  82:         
  83:     
  84: } -Argumentlist $NewSQLOpsMgrDBSrv
  85: # End Invoke-Command for changing management server registry keys  
  86: write-host "Completed updating Management Server(s) registry to refer to new SQL Server."
  87:  
  88: # Step 6.1 - Update registry on OpsMgr Report Server to refer to the new SQL Server-based computer
  89: write-host "Updating Management OpsMgr Report Server registry to refer to new SQL Server."
  90: Invoke-Command -ComputerName $CurrentOpsMgrRptSrv -ScriptBlock {
  91:     param(
  92:         [Parameter(Position=0)]
  93:         $LocalVarOpsMgrRptSrv
  94:     )
  95:     
  96:     #$RegKeyDefaultSDKServiceMachine = Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Reporting" -Name "DefaultSDKServiceMachine"
  97:     $RegKeyDWDBInstance = Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Reporting" -Name "DWDBInstance"
  98:     If ($RegKeyDWDBInstance.DWDBInstance -ne $LocalVarOpsMgrRptSrv)
  99:         {
  100:             write-host "Changing registry key HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Reporting\DWDBInstance on $Env:ComputerName"
  101:             Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Reporting" -Name "DWDBInstance" -Value $LocalVarOpsMgrRptSrv
  102:             write-host "Completed registry key change HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Reporting\DWDBInstance on $Env:ComputerName"
  103:         }
  104:  
  105: } -ArgumentList $CurrentOpsMgrRptSrv
  106: # End Invoke-Command for changing OpsMgr Report Server registry keys  
  107: write-host "Completed updating OpsMgr Report Server registry to refer to new SQL Server."
  108:  
  109: # Step 7 - Edit the ConfigService.config file
  110: write-host "Editing Management Server(s) ConfigService.config file."
  111: Invoke-Command -ComputerName $MgmtServers -ScriptBlock {
  112:     param(
  113:         [Parameter(Position=0)]
  114:         $LocalVarCngSvcCfgFilePath
  115:     ,
  116:         [Parameter(Position=1)]
  117:         $LocalVarNewSQLOpsMgrDBSrv
  118:     )
  119:  
  120:     If (test-path $LocalVarCngSvcCfgFilePath)
  121:         {
  122:             $xmlConfigSvcCfg = [XML] (get-content $LocalVarCngSvcCfgFilePath)
  123:  
  124:             $CmdbServerName = $xmlConfigSvcCfg.SelectSingleNode("/Config/Component/Instance/Category[@Name='Cmdb']/Setting[@Name='ServerName']")
  125:             $CmdbServerName.Value = $LocalVarNewSQLOpsMgrDBSrv
  126:  
  127:             $ConfigStoreServerName = $xmlConfigSvcCfg.SelectSingleNode("/Config/Component/Instance/Category[@Name='ConfigStore']/Setting[@Name='ServerName']")
  128:             $ConfigStoreServerName.Value = $LocalVarNewSQLOpsMgrDBSrv
  129:  
  130:             $xmlConfigSvcCfg.Save($LocalVarCngSvcCfgFilePath)
  131:         }
  132:     Else
  133:         {
  134:             write-host "File not found at $LocalVarCngSvcCfgFilePath"
  135:         }
  136:  
  137: } -ArgumentList $CngSvcCfgFilePath, $NewSQLOpsMgrDBSrv
  138: # End Invoke-Command for editing management server ConfigService.config file
  139: write-host "Finished editing Management Server(s) ConfigService.config file."
  140:  
  141: write-host "Updating SQL tables with new SQL Server name."
  142: # Step 8 - Udpate Operational Database with new SQL Server name
  143:  
  144: # Translate tables name which has "$" in name
  145: $MT_MSFTSysCntrMgmtGrpTbl = "dbo.MT_Microsoft`$SystemCenter`$ManagementGroup"
  146: $MT_MSFTSysCntrDWTbl = "dbo.MT_Microsoft`$SystemCenter`$DataWarehouse"
  147: $MT_MSFTSysCntrDWAppMonTbl = "dbo.MT_Microsoft`$SystemCenter`$DataWarehouse`$AppMonitoring"
  148:  
  149: # Because the SQLServerName_GUID column is dynamic, we need to find out the column name for this installation instance
  150: $GetMgmtGroupSrvNameColumn = invoke-sqlcmd -Database OperationsManager -Query "Select top 1 * from sys.syscolumns where name like 'SQLServerName_%'" -ServerInstance $NewSQLOpsMgrDBSrv
  151:  
  152: # Translate $GetMgmtGroupSrvNameColumn result to string data type
  153: $MgmtGrpSrvNameColumn = $GetMgmtGroupSrvNameColumn.Name.ToString()
  154:  
  155: # Update Opertional Database server name
  156: write-host "Updating SQL table $MT_MSFTSysCntrMgmtGrpTbl column $MgmtGrpSrvNameColumn on SQL Server $NewSQLOpsMgrDBSrv..."
  157: invoke-sqlcmd -Database OperationsManager -Query "UPDATE $MT_MSFTSysCntrMgmtGrpTbl SET $MgmtGrpSrvNameColumn = '$NewSQLOpsMgrDBSrv'" -ServerInstance $NewSQLOpsMgrDBSrv
  158: write-host "Completed updating SQL table $MT_MSFTSysCntrMgmtGrpTbl column $MgmtGrpSrvNameColumn on SQL Server $NewSQLOpsMgrDBSrv."
  159:  
  160: # Step 9 - Udpate Operational Database with new SQL Server name for APM tables
  161:  
  162: # Translate table name which has "$" in name
  163: $MT_MSFTSysCntrOpsMgrDBAppMonTbl = "dbo.MT_Microsoft`$SystemCenter`$OpsMgrDB`$AppMonitoring"
  164:  
  165: # Update Opertional Database server name
  166: write-host "Updating database OperationsManager, SQL table $MT_MSFTSysCntrOpsMgrDBAppMonTbl, column MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A on SQL Server $NewSQLOpsMgrDBSrv..."
  167: invoke-sqlcmd -Database OperationsManager -Query "UPDATE $MT_MSFTSysCntrOpsMgrDBAppMonTbl SET MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A = '$NewSQLOpsMgrDBSrv'" -ServerInstance $NewSQLOpsMgrDBSrv
  168: write-host "Completed updating database OperationsManager, SQL table $MT_MSFTSysCntrOpsMgrDBAppMonTbl, column MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A on SQL Server $NewSQLOpsMgrDBSrv."
  169:  
  170: write-host "Updating database OperationsManager, SQL table $MT_MSFTSysCntrDWTbl, column MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F on SQL Server $NewSQLOpsMgrDBSrv..."
  171: invoke-sqlcmd -Database OperationsManager -Query "UPDATE $MT_MSFTSysCntrDWTbl SET MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F = '$NewSQLOpsMgrDBSrv'" -ServerInstance $NewSQLOpsMgrDBSrv
  172: write-host "Completed updating database OperationsManager, SQL table $MT_MSFTSysCntrDWTbl, column MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F on SQL Server $NewSQLOpsMgrDBSrv."
  173:  
  174: write-host "Updating database OperationsManager, SQL table $MT_MSFTSysCntrDWAppMonTbl, column MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A on SQL Server $NewSQLOpsMgrDBSrv..."
  175: invoke-sqlcmd -Database OperationsManager -Query "UPDATE $MT_MSFTSysCntrDWAppMonTbl SET MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A = '$NewSQLOpsMgrDBSrv'" -ServerInstance $NewSQLOpsMgrDBSrv
  176: write-host "Completed updating database OperationsManager, SQL table $MT_MSFTSysCntrDWAppMonTbl, column MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A on SQL Server $NewSQLOpsMgrDBSrv."
  177:  
  178: write-host "Updating database OperationsManagerDW, SQL table dbo.MemberDatabase, column ServerName on SQL Server $NewSQLOpsMgrDWSrv..."
  179: invoke-sqlcmd -Database OperationsManagerDW -Query "UPDATE dbo.MemberDatabase SET ServerName = '$NewSQLOpsMgrDBSrv'" -ServerInstance $NewSQLOpsMgrDWSrv
  180: write-host "Completed updating database OperationsManagerDW, SQL table dbo.MemberDatabase, column ServerName on SQL Server $NewSQLOpsMgrDWSrv."
  181:  
  182: # Step 14 - Execute SQL commands on new Operations database
  183: write-host "Executing sp_configure and reconfigure on database OperationsManager on SQL Server $NewSQLOpsMgrDWSrv..."
  184: invoke-sqlcmd -Database OperationsManager -Query "sp_configure 'show advanced options', 0" -ServerInstance $NewSQLOpsMgrDBSrv
  185: invoke-sqlcmd -Database OperationsManager -Query "reconfigure" -ServerInstance $NewSQLOpsMgrDBSrv
  186: invoke-sqlcmd -Database OperationsManager -Query "sp_configure 'clr enabled',1" -ServerInstance $NewSQLOpsMgrDBSrv
  187: invoke-sqlcmd -Database OperationsManager -Query "reconfigure" -ServerInstance $NewSQLOpsMgrDBSrv
  188: write-host "Completed executing sp_configure and reconfigure on database OperationsManager on SQL Server $NewSQLOpsMgrDWSrv."
  189:  
  190: # Step 15 & 16 - Check if OperationsManager DB broker is enabled
  191: write-host "Checking to see if the DB broker is enabled on database OperationsManager on SQL Server $NewSQLOpsMgrDWSrv..."
  192: $BrokerEnabled = invoke-sqlcmd -Database OperationsManager -Query "SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'" -ServerInstance $NewSQLOpsMgrDBSrvA
  193:  
  194: If (!$BrokerEnabled.is_broker_enabled)
  195:     {
  196:         # Step 16 - Alter Operations Manager DB
  197:         write-host "The DB broker is not enabled on database OperationsManager on SQL Server $NewSQLOpsMgrDWSrv."
  198:         write-host "Altering database OperationsManager on SQL Server $NewSQLOpsMgrDWSrv to enable the DB broker..."
  199:         invoke-sqlcmd -Database OperationsManager -Query "ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE" -ServerInstance $NewSQLOpsMgrDBSrv
  200:         invoke-sqlcmd -Database OperationsManager -Query "ALTER DATABASE OperationsManager SET ENABLE_BROKER" -ServerInstance $NewSQLOpsMgrDBSrv
  201:         invoke-sqlcmd -Database OperationsManager -Query "ALTER DATABASE OperationsManager SET MULTI_USER" -ServerInstance $NewSQLOpsMgrDBSrv
  202:         write-host "Completed altering database OperationsManager on SQL Server $NewSQLOpsMgrDWSrv to enable the DB broker."
  203:     }
  204: Else
  205:     {
  206:         write-host "DB broker for database OperationsManager is enabled. No DB altering needed."
  207:     }
  208:  
  209:  
  210: # Step 17 - Start the Operations Manager services
  211:  
  212: Invoke-Command -ComputerName $MgmtServers -ScriptBlock {
  213:     $OpsMgr12MgmtSrvSvcArray = @("System Center Data Access Service", "System Center Management", "System Center Management Configuration")
  214:  
  215:     ForEach ($svc in $OpsMgr12MgmtSrvSvcArray)
  216:         {
  217:             write-host "Starting service $svc on $Env:ComputerName..."
  218:             $SCOpsMgrSvc = Get-Service | ? { $_.DisplayName -eq $svc}
  219:             If ($SCOpsMgrSvc.Status -ne "Running")
  220:                 {
  221:                     Start-Service -DisplayName $svc
  222:                     Do
  223:                         { 
  224:                             Start-Sleep -s 15
  225:                             $SCOpsMgrSvc = Get-Service | ? { $_.DisplayName -eq $svc}
  226:                         } Until ($SCOpsMgrSvc.Status -eq "Running")
  227:                 }
  228:             write-host "The service $svc is started on $Env:ComputerName."             
  229:         
  230:         }
  231:  } # End Invoke-Command for starting Management Server services
  232:  
  233:  write-host "Script Complete."

One thing that is not automated in this script is the reconfiguration of the Operations Manager Report Server SQL Reporting Services configuration. If the SSRS ReportServer and ReportServerTemp is being moved as well to a different SQL Server, you will have to manually go into the SQL Reporting Services Configuration Manager and attach that instance of SSRS to the correct SQL Server.

This is a version 1.0 development effort provided to the community. If you have any additions to include and/or questions feel free to email me.

 

Thanks,

Phil Gibson

OpsMgr2012-AutoMoveDBs.ps1