Powershell SCOM SQL Windows Server

SCOM 2012 R2 – Moving the operationsmanager DB from SQL server to new SQL Cluster

0

Moving SCOM 2012 R2 operationsdatabase to SQL Cluster. Process derived from this TechNet article http://technet.microsoft.com/en-au/library/hh278848.aspx

Quick overview of the environment

Versioning of my environment:

System Center Operations Manger 2012 R2 w/ Update Rollup 4
SQL Server 2012 SP1 CU8
Windows Server 2012 R2

Existing servers and role:

OM01 – Management Server
OM02 – Management Server
OM03 – Management Server
OMSDB\OMDB – Operations Database Server / SQL Server 2012 SP1
OMSDW\OMDW – Data Warehouse Server / SQL Server 2012 SP1

Planned setup:

OMS01, OMS02 & OMS03 – Management Servers (No Change)
SCOMDB\SCOMDB – Clustered SQL in named instance for Operations DB
SCOMDW\SCOMDW – Clustered SQL in named instance for Warehouse DB

Overview of steps:

  1. Stop SCOM Services on Management server
  2. Backup OperationsManager DB on SCOMDB using SQL Management Studio
  3. Restore OperationsManager DB to SQL Cluster Instance using SQL Management Studio
  4. Update registry on Management Server
  5. Update the ConfigService.config file
  6. Update SQL DB with new Server Names
  7. Add/check appropriate SQL permission
  8. Run SQL queries
  9. Start Management Services
  10. Check connectivity

 

Steps in more details:

 

1. Stop SCOM Services on Management servers

As those that have been here before would know that I try to do as much in PowerShell as possible. On ALL management servers, run the following:

stop-service -displayname “Microsoft Monitoring Agent”
stop-service -displayname “System Center Data Access Service”
stop-service -displayname “System Center Management Configuration”

 

2. Backup OperationsManager DB on SCOMDB using SQL Management Studio

  1. Not going into detailed SQL operations here, but high level is:
  2. SQL Management Studio > Server\Instance > Databases > OperationsManager > Right-Click / Backup
  3. Backup destination is ‘disk’ and specify a location and name
  4. Backup type = Full
  5. Backup component = Database
  6. Take DB offline or shutdown SQL Server or Instance

3. Restore OperationsManager DB to SQL Cluster Instance using SQL Management Studio

  1. Not going into detailed SQL operations here, but high level is:
  2. Copy bak file from source
  3. SQL Management Studio > Server\Instance > Databases > New Database > OperationsManager
  4. OperationsManager > Right-Click Restore > Database
  5. Select Device and brose to bak file
  6. Select backup set and confirm file locations
  7. In options > Overwrite the existing database (WITH REPLACE)
  8. Restore DB

 

4. Update registry on ALL Management Servers

Again let’s do this with PowerShell:

$SQLServer = “SCOMDB”
$SQLInstance = “SCOMDB”

Set-ItemProperty -Path ‘HKLM:\Software\Microsoft\System Center\2010\Common\Database’ -Name DatabaseServerName -Value $SQLServer\$SQLInstance

 

5. Update the ConfigService.config file on ALL Management Servers

Again, since we have the PowerShell console open…

$SCOMDB = “SCOMDB\\SCOMDB”
$SCOMConfig = Get-Item “C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server\ConfigService.config”
$OldSQLServer = “OMSDB\\OMDB”

(gc $SCOMConfig) -replace $OldSvr,$SQLServer | Out-File $SCOMConfig

 

6. Update SQL DB with new Server Name

  1. Open SQL Management Studio and connect to the new SQL Cluster instance
  2. Expand Databases > OperationsManager > Tables
  3. Select dbo.MT_Microsoft$SystemCenter$ManagementGroup
    1. Right-click and select Edit top 200 rows
    2. Change the value SQLServerName_43FB076F_7970_4C86_6DCA_8BD541F45E3A to your new SQL Cluster and Instance name (In my case, SCOMDB\SCOMDB)
  4. Select dbo.MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring
    1. Right-click and select Edit top 200 rows
    2. Change the value MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A to your new SQL Cluster and Instance name (In my case, SCOMDB\SCOMDB)

 

7. Add/check appropriate SQL permission

The existing DB already has it’s appropriate permissions but the accounts will probably not have access to the SQL Instance.

Expand the instance security > logins and add the SCOM Management Servers and service accounts. I didn’t have to configure mappings as they were already present in the DB. Just create the login and the rest will be inherited from the DB restore.

Things to check: (Again, this info is available on this TechNet article: http://technet.microsoft.com/en-au/library/hh278848.aspx)

  • Management Servers have are present in the Instance > Security > Logins and are mapped to the OperationsManager DB with these role memberships:
    • ConfigService
    • db_accessadmin
    • db_datareader
    • db_datawriter
    • db_ddladmin
    • db_securityadmin
    • sdk_users
    • sql_dependency_subscriber
  • Action account is present and has these role memberships:
    • db_datareader
    • db_datawriter
    • db_ddladmin
    • dbmodule_users
  • Data Warehouse Writer account has these role memberships:
    • apm_datareader
    • apm_datawriter
    • db_datareader
    • dwsynch_users
  • Data Access Service (DAS)/Configuration account has these role memberships:
    • ConfigService
    • db_accessadmin
    • db_datareader
    • db_datawriter
    • db_ddladmin
    • db_securityadmin
    • sdk_users
    • sql_dependency_subscriber

 

 

8. Run SQL Queries

Make sure you are running this against the OperationsManager database

sp_configure ‘show advanced options’,1
reconfigure

sp_configure ‘clr enabled’,1
reconfigure

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

If the result of this query was an is_broker_enabled value of 1, skip this step. Otherwise, run the following SQL queries:

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

 

9. Start Management Services

This should be our last PowerShell command for this process..

start-service -displayname “Microsoft Monitoring Agent”
start-service -displayname “System Center Data Access Service”
start-service -displayname “System Center Management Configuration”

 

10. Check connectivity

Open SCOM Console (wait a minute or two after the DAS services are started)

Check health of Management Servers via all the usual suspects.

Cheers’
Dan

p.s. I’ll be migrating this SCOM environments warehouse DB to the same cluster so will publish a Warehouse DB move blog soon.

Leave a Reply