SCSM - Verify AlwaysOn for ServiceManager - Hell Difficulty
Those letters above aren't just a forgotten spacer, they are the screams from SCSM admins, of all those who've had to try to use AlwaysOn for the Live SCSM Database!!!
Much like the ending to the first Diablo game, trying to get this "little gem" of a program Always On ....↶( ╰ סּ _ סּ╯) ψ ..... can be quite painful. For those who are unfamiliar, jamming a jagged rock into your forehead so it's "AlwaysOn" and constantly trying to contain the Devil within is just as unpleasant as getting AlwaysOn working 100% for the Live SCSM Database.
Fortunately, this guide is to help avoid that issue for those who already have SCSM installed with AlwaysOn and want to verify they haven't missed any parts that need to be updated to point to the Listener Node instead of a SQL server.
This guide was made with SCSM 2012 R2, though it will most likely also be compatible for SCSM 2010 and SCSM 2016.
Let's say the SQL servers hosting the ServiceManager database are SCSM-SQL-A and SCSM-SQL-B.
Let's say the Listener Node for AlwaysOn is called SCSM_LN.
** **
In this case, SCSM was installed on SCSM-SQL-A which is referenced all over the place and must be changed now that we want ServiceManager to be AlwaysOn.
Important: You validate the name of the Listener Node in SSMS (SQL Server Management Studio).
IT IS HIGHLY RECOMMENDED YOU TURN OFF THE FOLLOWING SERVICES on all the SCSM Management Servers and SCSM Data Warehouse Management Server:
- Microsoft Monitoring Agent
- System Center Data Access Service
- System Center Management Configuration
Registry
On each System Center Service Management Servers (including Primary Workflow Server) open regedit.exe and go to the following location:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\2010\Common\Database
Ensure the "DatabaseServerName" value is set to to your Listener Node Name
(These servers are the ones you connect to through your SCSM Console)
ServiceManager Database
Using SSMS, Database Engine connect to your Listener Node, in this case SCSM_LN
Expand into Databases -> ServiceManager -> Tables
Now update the following Tables by right clicking on each and choosing "Edit Top 200 Rows"
[dbo].[MT_Microsoft$SystemCenter$ManagementGroup]
Alter all rows, "SQLServerName_<GUID>" Column, change value from SCSM-SQL-A to SCSM_LN
[dbo].[MT_Microsoft$SystemCenter$ResourceAccessLayer$CmdbResourceStore]
Alter "Ral.CmdbResourceStore.Cmdb" row, "Server_<GUID>" Column, change value from SCSM-SQL-A to SCSM_LN
[dbo].[MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore]
Alter "Ral.SqlResourceStore.Workflows" row, "Server_<GUID>" Column, change value from SCSM-SQL-A to SCSM_LN
Alter "Ral.SqlResourceStore.Ods" row, "Server_<GUID>" Column, change value from SCSM-SQL-A to SCSM_LN
What are these things I've changed???
ManagementGroup à where SCSM Data is (Operational LIVE SCSM Database)
Ral.CmdbResourceStore.Cmdb à where LIVE SCSM Data is.
Ral.SqlResourceStore.Workflows à workflows rely on this.
Ral.SqlResourceStore.Ods à Operational Data Store where formatted tables are to be synced to Data Warehouse.
DataWarehouse DWStagingAndConfig Database
If you have setup and registered the SCSM Data Warehouse to SCSM then you need to use SSMS and Database Engine connect to your Data Warehouse SQL server.
To find out your DWSQL server, in the SCSM Console:
Go to \Data Warehouse\Data Warehouse\Data Sources
Select your "DW_" prefixed Data Source
Under the Data Warehouse CMDB Source, look at the Database Server field
Expand into Databases -> DWStagingAndConfig -> Tables
Now update the following Table by right clicking on it and choosing "Edit Top 200 Rows".
[dbo].[MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore]
Alter "SCSM.ExtractionSource" row (determined by "DisplayName" column), "Server_<GUID>" Column, change value from SCSM-SQL-A to SCSM_LN
This ensures the Data Warehouse is syncing through the Primary SQL server hosting the ServiceManager Database.
IF YOU HAVE HAD TO MAKE ANY CHANGES, PLEASE RESTART YOUR SCSM Management Servers and SCSM Data Warehouse Management Server, the services will start back up again after a restart (if they had been stopped as recommended):
- Microsoft Monitoring Agent
- System Center Data Access Service
- System Center Management Configuration
Now you have finished validating and ensuring AlwaysOn for the Live SCSM Database! Congratulations, go celebrate!
No, wait, put it down, put down the jagged rock, that's not how you are supposed to celebrate, NOOOOOOO!!!!
