SCOM/OpsMgr: Databases Move / Failover PowerShell Script

It could happens that one day or another, you will have to move the Operations Manager (OperationsManager and OperationsManagerDW ) databases from one SQL instance to another one. The reason could be a SQL migration to another version of SQL or during a failover in case of a disaster recovery plan.

Microsoft published 2 TechNet Articles with all the steps that need to be performed when you have to move these two databases.
https://technet.microsoft.com/en-US/library/hh278848.aspx
https://technet.microsoft.com/en-US/library/hh268492.aspx

As you can see, there are a lot of manual actions like updating registry entries, SQL entries, SQL Reporting Datasources, Config File … that need to be done. Manual actions often means human errors and mistakes… and also, time consuming…

So to avoid all these problems, I decided to write a PowerShell that will take care of all these steps for us. The only prerequisites is to have already the 2 DBs restored to the new SQL instances and have the rights correctly assigned (like it was on the original SQL Instances).

Be really careful with this script, as it’s touching SQL, Registry, … it could easily break your SCOM infrastructure. Use it at your own risks and be sure to have a good backup before doing anything else.

you could download it from here: https://www.vnext.be/wp-content/uploads/2015/12/SCOMDBsFailOver.zip

<#

.Name: SCOMDBsFailOver.ps1
.Decription: Update SCOM registry/Databases records to point to the failover DBs 

.Version: 0.1
.Date: December 14th, 2015

.Author: Christopher.keyaert@inovativ.be

.Pre-requisites: Restore the OpsDB and DWDB DBS to the new SQL instances 
 and ensure that the security rights have been applied correctly

.MS TechNet Guide
https://technet.microsoft.com/en-US/library/hh278848.aspx
https://technet.microsoft.com/en-US/library/hh268492.aspx

#>

#Functions
Function GetSQLColumnName($OpsDBFailoverInstance,$OpsDBFailoverDB,$TableName,$ColumnNamePrefix)
    {
    $connectionString = "Server=$OpsDBFailoverInstance;Database=$OpsDBFailoverDB;Integrated Security=true;"
    $Fconnection = New-Object System.Data.SqlClient.SqlConnection
    $Fconnection.ConnectionString = $connectionString
    $Fconnection.Open()

    #Update the SQL Server Name for Application Performance
    $query = 'Select * from dbo.' + $TableName
    $command = $Fconnection.CreateCommand()
    $command.CommandText =  $query
    $result = $command.ExecuteReader()

    #Formating the Data
    $table = new-object "System.Data.DataTable"
    $table.Load($result)
    $result.close()

    $Fconnection.Close()

    $ColumnName = ($table | Get-Member | Where-Object {$_.Name -like "$ColumnNamePrefix*"}).Name

    Return $ColumnName
    }


#Parameters
$VerbosePreference = "Continue"
#$VerbosePreference = "SilentlyContinue"

$OpsDBFailoverInstance = 'SQL001\cloudos'
$OpsDBFailoverDB = 'OperationsManager'
$DWDBFailoverInstance = 'SQL001\cloudos'
$DWDBFailoverDB = 'OperationsManagerDW'

#Import the SCOM Module
Import-Module OperationsManager

$errorMessage = $null
Try {
    #Connection to SCOM Server
    $Connection = New-SCOMManagementGroupConnection -ComputerName "."
       
    }
catch{
     Write-Verbose $_
     exit
     }
     
Write-Verbose "Connected to SCOM MS"

#Get All SCOM MS and stopped the SCOM Services.
$RptURL = Get-SCOMReportingSetting | Select -ExpandProperty ReportingServerUrl
$SCOMMS = Get-SCOMManagementServer | where-object {$_.IsGateway -eq $false} | select DisplayName
$SCOMMS = $SCOMMS | % {$_.DisplayName}

################################
#Update the MS information
Write-Verbose "Stopping All SCOM Services + Registry update on All MS - Started"

Invoke-Command -ComputerName $SCOMMS -ScriptBlock {

   param($OpsDBFailoverDB,$OpsDBFailoverInstance,$DWDBFailoverDB,$DWDBFailoverInstance)

    #Stop the SCOM Service
    Get-Service HealthService,OMSDK,cshost | Stop-Service -PassThru | Set-Service -StartupType disabled
    
    #Update the registry keys - Part 1
    $registryPath = "HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup"
    
    #OpsDB Reg Keys
    New-ItemProperty -Path $registryPath -Name DatabaseName -Value $OpsDBFailoverDB -Force | Out-Null
    New-ItemProperty -Path $registryPath -Name DatabaseServerName -Value $OpsDBFailoverInstance -Force | Out-Null

    #DWDB Reg Keys
    New-ItemProperty -Path $registryPath -Name DataWarehouseDBName -Value $DWDBFailoverDB -Force | Out-Null
    New-ItemProperty -Path $registryPath -Name DataWarehouseDBServerName -Value $DWDBFailoverInstance -Force | Out-Null
    
    #Update the configuration File
    $setupKey = Get-Item -Path $registryPath
    $installDirectory = $setupKey.GetValue("InstallDirectory") 
    $ConfigurationFilePath = $installdirectory + 'ConfigService.config'

    #Backup the file
    Copy-Item $ConfigurationFilePath -Destination "$installdirectory\ConfigService.config.bkp" -Force
    
    #Update the value
    $xml = (Get-Content $ConfigurationFilePath)
    $xml.Config.Component.Instance.Category.Setting | where {$_.Name -eq 'ServerName'} | % {$_.value = $OpsDBFailoverInstance}
    $xml.Config.Component.Instance.Category.Setting | where {$_.Name -eq 'DatabaseName'} | % {$_.value = $OpsDBFailoverDB}
    $xml.Save($ConfigurationFilePath)


    #Update the registry keys - Part 2
    $registryPath = "HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Database"
   
    #OpsDB Reg Keys
    New-ItemProperty -Path $registryPath -Name DatabaseName -Value $OpsDBFailoverDB -Force | Out-Null
    New-ItemProperty -Path $registryPath -Name DatabaseServerName -Value $OpsDBFailoverInstance -Force | Out-Null

   
    } -ArgumentList $OpsDBFailoverDB,$OpsDBFailoverInstance,$DWDBFailoverDB,$DWDBFailoverInstance 

Write-Verbose "Stopping All SCOM Services + Registry update on All MS - Done"

################################
#Update the SQL DBs

Write-Verbose "Updating OpsDB Tables - Started"

#Initiate SQL Connection
$connectionString = "Server=$OpsDBFailoverInstance;Database=$OpsDBFailoverDB;Integrated Security=true;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

#Update Table1
$ColumnName = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$ManagementGroup' 'SQLServerName_'
$ColumnName2 = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$ManagementGroup' 'OperationalDatabaseName_'
$command = $connection.CreateCommand()
$command.CommandText = 'Update dbo.MT_Microsoft$SystemCenter$ManagementGroup Set ' + "$ColumnName = '$OpsDBFailoverInstance'" + ", $ColumnName2 = '$OpsDBFailoverDB'"
$result = $command.ExecuteReader()
$result.close()

#Update Table2
$ColumnName = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring' 'MainDatabaseServerName_'
$ColumnName2 = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring' 'MainDatabaseName_'
$command = $connection.CreateCommand()
$command.CommandText = 'Update dbo.MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring Set ' + "$ColumnName = '$OpsDBFailoverInstance'" + ", $ColumnName2 = '$OpsDBFailoverDB'"
$result = $command.ExecuteReader()
$result.close()


#Clr Enalbed - Part1
$query = @'
sp_configure "show advanced options",1
reconfigure
'@

$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$result.close()

#Clr Enalbed - Part2
$query = @'
sp_configure 'clr enabled',1
reconfigure
'@
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$result.close()

#Enable SQL Broker
$query = @'
ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE OperationsManager SET ENABLE_BROKER
ALTER DATABASE OperationsManager SET MULTI_USER
'@
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$result.close()

#Update DBName DW Info
$ColumnName = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$DataWarehouse' 'MainDatabaseServerName_'
$ColumnName2 = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$DataWarehouse' 'MainDatabaseName_'
$command = $connection.CreateCommand()
$command.CommandText = 'Update dbo.MT_Microsoft$SystemCenter$DataWarehouse Set ' + "$ColumnName = '$DWDBFailoverInstance'" + ", $ColumnName2 = '$DWDBFailoverDB'"
$result = $command.ExecuteReader()
$result.close()

#Update App Monitoring DBName DW Info
$ColumnName = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring' 'MainDatabaseServerName_'
$ColumnName2 = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring' 'MainDatabaseName_'
$command = $connection.CreateCommand()
$command.CommandText = 'Update dbo.MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring Set ' + "$ColumnName = '$DWDBFailoverInstance'" + ", $ColumnName2 = '$DWDBFailoverDB'"
$result = $command.ExecuteReader()
$result.close()

#Update App Monitoring DBName DW Info
$ColumnName = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring_Log' 'Post_MainDatabaseServerName_'
$ColumnName2 = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring_Log' 'Post_MainDatabaseName_'
$command = $connection.CreateCommand()
$command.CommandText = 'Update dbo.MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring_Log Set ' + "$ColumnName = '$DWDBFailoverInstance'" + ", $ColumnName2 = '$DWDBFailoverDB'"
$result = $command.ExecuteReader()
$result.close()

#Update App Monitoring DBName DW Info
$ColumnName = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$DataWarehouse_Log' 'Pre_MainDatabaseServerName_'
$ColumnName2 = GetSQLColumnName $OpsDBFailoverInstance $OpsDBFailoverDB 'MT_Microsoft$SystemCenter$DataWarehouse_Log' 'Pre_MainDatabaseName_'
$command = $connection.CreateCommand()
$command.CommandText = 'Update dbo.MT_Microsoft$SystemCenter$DataWarehouse_Log Set ' + "$ColumnName = '$DWDBFailoverInstance'" + ", $ColumnName2 = '$DWDBFailoverDB'"
$result = $command.ExecuteReader()
$result.close()

#Close the connection
$connection.Close()
Write-Verbose "Updating OpsDB Tables - Done"

################################
#Update the DW DB
Write-Verbose "Updating DWDB Tables - Started"

#Initiate SQL Connection
$connectionString = "Server=$DWDBFailoverInstance;Database=$DWDBFailoverDB;Integrated Security=true;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$command = $connection.CreateCommand()
$command.CommandText = 'Update dbo.MemberDatabase Set ' + "ServerName = '$DWDBFailoverInstance'" + ", DatabaseName = '$DWDBFailoverDB'"
$result = $command.ExecuteReader()
$result.close()

#Close the connection
$connection.Close()
Write-Verbose "Updating DWDB Tables - Done"

###############################
#Start All the SCOM Services
Write-Verbose "Starting All SCOM Services on All MS - Started"

Invoke-Command -ComputerName $SCOMMS -ScriptBlock {
    Get-Service HealthService,OMSDK,cshost | Set-Service -StartupType Automatic -PassThru | Start-Service 
    }

Write-Verbose "Starting All SCOM Services on All MS - Done"

################################
#Update the Reporting Server
Write-Verbose "Updating Reporting Server - Started"
Start-Sleep 10

$RptSrv = (($RptURL).split('/')[2]).split(':')[0]
$NewConnectionString =  "data source=$DWDBFailoverInstance;initial catalog=$DWDBFailoverDB;Integrated Security=SSPI"

$url = "$($RPTUrl)/reportservice2005.asmx?WSDL";
$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential

#Get the DataSource for the Reports and AppMonitoring 
$DSlist = $SSRS.ListChildren("/", $true) | Where-Object {$_.Type -eq "DataSource"}  

#Update the connectionString
Foreach($DS in $DSlist)  
 { 
  $Ref = $ssrs.GetDataSourceContents($DS.Path)
  $Ref.ConnectString = $NewConnectionString
  $ssrs.SetDataSourceContents($DS.Path,$ref)
 }

Invoke-Command -ComputerName $RptSrv -ScriptBlock {

   param($DWDBFailoverDB,$DWDBFailoverInstance)

   #Update the registry keys 
   $registryPath = "HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Reporting"
    
   #OpsDB Reg Keys
   New-ItemProperty -Path $registryPath -Name DWDBInstance -Value "$DWDBFailoverInstance" -Force | Out-Null
   New-ItemProperty -Path $registryPath -Name DWDBName -Value "$DWDBFailoverDB" -Force | Out-Null

   } -ArgumentList $DWDBFailoverDB,$DWDBFailoverInstance 

Write-Verbose "Updating Reporting Server - Done"


 

Cheers
Christopher

Tweet about this on TwitterShare on FacebookShare on LinkedInShare on Google+Email this to someoneShare on TumblrPin on PinterestDigg thisShare on RedditFlattr the authorBuffer this pageShare on StumbleUpon

About Christopher Keyaert

Christopher Keyaert is a Consultant, focused on helping partners to leverage the System Center and Microsoft Azure cloud platform. He is also a Microsoft Most Valuable Professional (MVP) for Cloud and Data Center Management and a Microsoft Certified Trainer (MCT).
This entry was posted in Operations Manager. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *