I recently worked with a client that hosted the Service Manager database on a SQL cluster. They manually failed over the SCSM database to the other node in the SQL cluster. Request Management in Azure Pack and the SCSM data warehouse were still trying to communicate with Service Manager using the old node in the SQL cluster. We needed a way to manually flip both Azure Pack and The DW over to the new node in the SQL cluster. Below is how I did this. I am posting about this in case it saves anyone else time and for me if I need it for future reference.
GridPro Request Management in Windows Azure Pack Failover:
Navigate to: C:\inetpub\MgmtSvc-RequestManagementAPI
Open the “solidConnectionSettings.config” file in a text editor such as notepad.
Modify the highlighted value in the connection string to the name of the second node in the SQL cluster:
<connectionStrings>
<add name=”ServiceManagerCMDB” connectionString=”Server=SQLSERVER1,1433;Database=ServiceManager;Integrated Security=True”/>
</connectionStrings>
After this is modified an IIS reset is needed. After that the Request Management in Windows Azure Pack should now attempt to connect to Service Manager on the correct SQL node.
Service Manager Data Warehouse failover:
In regards to the data warehouse we needed to re-point this to the second node in the SQL cluster. To do this I:
Opened the DWStagingAndConfig database.
Open the MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore table.
And
Run Select * from MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore
Or
Right click on the table and Select Top 1000 rows. (Don’t worry there are only 10 rows in this table.)
Note the 5th row column DataService_98B2DDF9_D9FD_9297_85D3_FCF36F1D016B has Service Manager listed. Further along in row 5 under column Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA it has the SQL server for Service Manager that it is pointed to listed. This is what needs to be changed. You can see this in the following screenshot:
To change this run the following:
UPDATE MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore
SET Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA = ‘SQLSERVER2’
WHERE DataService_98B2DDF9_D9FD_9297_85D3_FCF36F1D016B = ‘ServiceManager’
NOTE: Be sure to replace SQLSERVER2 with the name of your new SQL node.
Now if you Run Select * from MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore
You will see row 5 in the Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA column will reflect the change.
Thanks to SCSM Master and Concurrency co-worker Steve Seibold for help tracking down the SCSM DB in the DW DB’s!
