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
- Ensure that you are an administrator on all of the Management Servers as well as the SQL Servers
- 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.
- Ensure that your PowerShell execution policy is set to RemoteSigned or lower for all of the Management Servers as well as the SQL Servers.
- 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.
- 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