Monday, December 11, 2017

#OpsMgr: script to assist with moving the OperationsManager and OperationsManagerDW databases

It happens from time to time that we need to move the databases used in our System Center Operations Manager environments from one server to another. While this is a very well documented process for both the OperationsManager database and the Data Warehouse database, it often happens that we miss a step somewhere along the line.

To assist with this process, I have created two scripts, a PowerShell script for updating the registry keys on the management servers and restarting the services, and a SQL script for updating the relevant tables in the database.

I have commented the scripts as much as possible, so it should be fairly easy to figure out what to do. I would recommend running the SQL script first, and then the PowerShell one.

Update the variables (highlighted) before running. Scripts have been tested with OpsMgr 2012 and OpsMgr 2016 in a lab environment. Results may vary in production, and it is highly recommended you test in your lab environment before using in production.

---------------------------------------------------------------------------------------------------------------------

SQL Script:

-- Declare the variable to be used.
DECLARE @OpsDBServer nvarchar(255) = '<server name here>',
         @DWDBServer nvarchar(255) = '<server name here>';


Use OperationsManager

UPDATE MT_Microsoft$SystemCenter$ManagementGroup
SET SQLServerName_43FB076F_7970_4C86_6DCA_8BD541F45E3A = @OpsDBServer


UPDATE MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring
SET MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A = @OpsDBServer

UPDATE MT_Microsoft$SystemCenter$DataWarehouse
SET MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F  = @DWDBServer

UPDATE MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring
SET MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A  = @DWDBServer


UPDATE MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring_Log
SET Post_MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A = @DWDBServer

select SQLServerName_43FB076F_7970_4C86_6DCA_8BD541F45E3A from MT_Microsoft$SystemCenter$ManagementGroup
select MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A from MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring
select MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F from MT_Microsoft$SystemCenter$DataWarehouse
select MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A from MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring
select Post_MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A from MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring_Log


sp_configure ‘show advanced options’,1
reconfigure
sp_configure ‘clr enabled’,1
reconfigure


DECLARE @broker integer

set @broker = (SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager')

IF @broker = 0
BEGIN
     ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
     ALTER DATABASE OperationsManager SET ENABLE_BROKER
     ALTER DATABASE OperationsManager SET MULTI_USER

    PRINT 'Broker version is incorrect.';
END
ELSE
     PRINT 'Broker version is correct.';

GO


Use OperationsManagerDW

UPDATE MemberDatabase SET ServerName = @DWDBServer

select ServerName from MemberDatabase

GO

---------------------------------------------------------------------------------------------------------------------

PowerShell script:

############################################################################
#
#   SCOM-UpdateDBServerLocations.ps1
#
#
#    This script can be used when moving both the OperationsManager and Data Warehouse
#    databases to a new location.
#    The script will update the registry on all management servers.
#    The script should be run once the databases have been migrated and you are ready
#    to make the final updates.
#    User running this script should have full admin rights on all management servers
#    and update rights on both databases.
#
############################################################################

#Variables

$ManagementServers = "ms1,ms2,ms3,ms4" # add management server names comma separated, this creates an array of the management servers

$stopservices = "Yes" # change to no if services are already stopped


$OpsDBServer = "opsdbservername\instancename,port"

$DWDBServer = "dwservername\instancename,port"


$KeyLocation1 = "HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Database"
$KeyLocation2 = "HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup"


$ReportDate = Get-Date -format "yyyy-M-dd"

$rnd = Get-Random -minimum 1 -maximum 1000
$ReportLocation = "C:\Temp"
$ReportName = "$ReportDate-$rnd-SCOM-DBMigrationLog.csv"
$SavetoFile = "$ReportLocation\$ReportName"


# there should be no need to update the script beyond this point

# Delete any previous versions of the report for in case

If (Test-Path $SavetoFile){
     Remove-Item $SavetoFile
}

# Open the content
$body = @()

$body += "SCOM DB Migration Log - "+$ReportDate



# Stop services on Management Servers

if ($stopservices -eq "Yes")
{
foreach ($ms in $ManagementServers)
{

    (Get-Service -DisplayName 'System Center Data Access Service' -ComputerName $ms).Stop()
     (Get-Service -DisplayName 'System Center Management Configuration'-ComputerName $ms).Stop()
     (Get-Service -DisplayName 'Microsoft Monitoring Agent' -ComputerName $ms).Stop()

$body += "Services stopped on " + $ms

}
}

# sleep for a few seconds
Start-Sleep -s 20


# update registry key values on MS

$cred = Get-Credential

foreach ($ms in $ManagementServers)
{
$session = New-PSSession -Authentication Kerberos -ComputerName $ms  -Credential $cred -Name RegUpdate

Set-ItemProperty -Path $KeyLocation1 -Name DatabaseServerName  -Value $OpsDBServer
Set-ItemProperty -Path $KeyLocation2 -Name DatabaseServerName  -Value $OpsDBServer
Set-ItemProperty -Path $KeyLocation2 -Name DataWarehouseDBServerName  -Value $DWDBServer

Disconnect-PSSession $session

$body += "Registry updated on " + $ms

}


# Start Services on Managment Services

foreach ($ms in $ManagementServers)
{

    (Get-Service -DisplayName 'System Center Data Access Service' -ComputerName $ms).Start()
     (Get-Service -DisplayName 'System Center Management Configuration'-ComputerName $ms).Start()
     (Get-Service -DisplayName 'Microsoft Monitoring Agent' -ComputerName $ms).Start()


$body += "Services stopped on " + $ms
}


# write-report

$body >> $SavetoFile


---------------------------------------------------------------------------------------------------------------------

As always, copy the scripts into Notepad or similar first and make sure the quotes are not mangled.

No comments:

Related Posts with Thumbnails