Fixing Service Manager Data Warehouse Registration Information
There are quite a few situations where we end up having "corrupt" - actually orphaned or missing - information about the DW Registration.
I have created a tool which you can use to fix *mostly* all such issues automatically. Using this tool you can delete all basic information about the DW Registration or if you are sure about what is going on, just re-create all basic information about it. The cool part with deleting the existing information which may be incomplete, is that you can then simply re-register the DW from the SM Console.
But - let's first talk about how this actually works under the hood! ;)
When we register the DW from the Console in Administration tab, there are several things which happen:
- A new object of class Microsoft.SystemCenter.ResourceAccessLayer.DwSdkResourceStore is created into the ServiceManager database - now, the SM Console knows where/how to connect to the DW SDK service and the Data Warehouse Tab appears in the Console
- A new object of class Microsoft.SystemCenter.DataWarehouse.CmdbSource is created into the DWStagingAndConfig database
- We create a new SecureReference for the Account we have chosen in the DW Registration Wizard in the Console
- A new object of class Microsoft.SystemCenter.ResourceAccessLayer.SqlResourceStore is created into the DWStagingAndConfig database
- A new relationship of class Microsoft.SystemCenter.ResourceAccessLayer.StoreHasProperty is created between the new SqlResourceStore object and the ExtractionSource object of class Microsoft.SystemCenter.ResourceAccessLayer.StoreProperty
- A new object of class Microsoft.SystemCenter.ResourceAccessLayer.SdkResourceStore is created into the DWStagingAndConfig database
- A new relationship of class Microsoft.SystemCenter.ResourceAccessLayer.StoreHasProperty is created between the new SdkResourceStore object and the Sdk object of class Microsoft.SystemCenter.ResourceAccessLayer.StoreProperty
- If it is a "managed" DataSource (like SvcMgr or OpsMgr style) then we also create a new MPSync Rule and Extract Rule for this DataSource
- Now in the end, we end up calling the [Staging].[AddDatasource] stored procedure on the DWStagingAndConbfig database which ads an entry to the [Staging].[Datasource] table for this new DataSource with the appropriate type
These are the tables of interest:
1. On the ServiceManager database, make sure we have the DwSdkResourceStore object for the Data Warehouse - this is the way SM (and the Console) "know" that they have a DW registered and where/how to connect to it - this is how the Data Warehouse tab appears in the SM Console:
select *<br>from MT_Microsoft$SystemCenter$ResourceAccessLayer$DwSdkResourceStore
2. On the DWStagingAndConfig database, make sure we have the CMDBSource object created for the SM DataSource - check the properties in the results and look at DataSourceName, SdkServer, Database, DatabaseServer. There should be at least 2 entries here - 1 for your DW (it's actually registered itself to itself as a DataSource - another story for another time) and 1 entry for your ServiceManager DataSource (Management Group):
select *<br>from MTV_Microsoft$Systemcenter$Datawarehouse$CMDBSource
3. On the DWStagingAndConfig database, make sure we have the Datasource entry in the Staging.Datasource table because it is an entry which is needed for the registration to actually work. There should be at least 2 entries here - 1 for your DW and 1 entry for your ServiceManager DataSource (Management Group):
select ds.DatasourceId, ds.TimeAdded, cmdb.DataSourceName_AC09B683_AE61_BDCA_6383_2007DB60859D<br>from Staging.Datasource as ds<br>join MTV_Microsoft$Systemcenter$Datawarehouse$CMDBSource as cmdb<br> on ds.DatasourceId = cmdb.DataSourceId_17109AB9_58CD_F741_8AE3_3A9F29C83709
4. On the DWStagingAndConfig database, make sure that we have the SdkResourceStore object created. It is how the DW SDK knows to what (managed) DataSource to connect to for a certain DataSource as it connects to a SDK service. There should be at least 3 entries here - 1 is an internal source (Ral.SdkResourceStore.Sdk), 1 for your DW and 1 entry for your ServiceManager DataSource (Management Group). Check the properties here and see if they are correct - DisplayName should have YOUR_SM_MG.Sdk and then check also if the Server is correct - should be the SM Workflow Management Server:
select *<br>from MTV_Microsoft$SystemCenter$ResourceAccessLayer$SdkResourceStore
5. On the DWStagingAndConfig database, make sure that the SqlResourceStore object is created. It is how the DW SDK knows to which SQL Server to connect to for getting the data for the DataSource from its database. There should be at least 2 entries here - 1 for your DW and 1 entry for your ServiceManager DataSource (Management Group). Again, check the properties, DataService, Name, Server:
select *<br>from MTV_Microsoft$Systemcenter$ResourceAccessLayer$SqlResourceStore<br>where DisplayName like '%.ExtractionSource'
6. On the DWStagingAndConfig database, make sure you have the relationships needed here of type StoreHasProperty for the SM DataSource. There should be at least 5 entries here. 1 is for the internal DataSource (Ral.SdkResourceStore.Sdk) and then 2 for the DW DataSource (YOUR_DW_MG.Sdk and YOUR_DW_MG.ExtractionSource) and 2 for your SM DataSource (YOUR_SM_MG.Sdk and YOUR_SM_MG.ExtractionSource) - so make sure these exist:
select r.RelationshipId, bmes.FullName as 'SourceEntity', bmet.FullName as 'TargetEntity'<br>from Relationship as r<br>join BaseManagedEntity as bmes<br> on r.SourceEntityId = bmes.BaseManagedEntityId<br>join BaseManagedEntity as bmet<br> on r.TargetEntityId = bmet.BaseManagedEntityId<br>where r.TargetEntityId in (<br> select BaseManagedEntityId<br> from MTV_Microsoft$SystemCenter$ResourceAccessLayer$StoreProperty<br> where DisplayName in ('ExtractionSource', 'Sdk')<br>) and r.RelationshipTypeId = (<br> select RelationshipTypeId<br> from RelationshipType<br> where RelationshipTypeName = 'Microsoft.SystemCenter.ResourceAccessLayer.StoreHasProperty'<br>)
What the Tool does *not* do is to add or remove the SecureReference, MPSync Rule and Extract Rule for the new DataSource - so make sure that you *know* that the DW was registered and working before.
Another idea and recommendation if you are not sure is to run the tool with " -a:rem" so that we delete the basic registration information and then just re-register it normally from the SM Console, thus creating everything needed including SecureReference, MPSync Rule and Extract Rule for the ServiceManager database.
To run the tool, you need to run it on a computer which has network access to both the SM Management Server and the DW Management Server. This includes of course Kerberos Authentication working and the User Account with which you are running the tool needs to be a SM Admin Account.
This application needs these 4 mandatory parameters below to be passed and the format is "-PARAMETER:VALUE" with the "-" and the ":".
-u: User Account which will be used for the DW in DOMAIN\USER format.
-sm: Service Manager Workflow Management Server NetBios.
-dw: Service Manager Data Warehouse Management Server NetBios.
-a: "add" or "rem"
Use "add" as action (-a:add) if you want to try to re-create the core objects and relationships needed.
Use "rem" as action (-a:rem) if you want to try to delete the core objects and relationships needed and afterwards register normally from the SM Console.
Example: SCSMRegisterDW.exe -u:CONTOSO\SMAdmin -sm:SMServer -dw:DWServer -a:rem
Run this tool at your own risk!
Make a full Backup of the ServiceManager and DWStagingAndConfig database before running the tool!
The best suggestion is to open a case with Microsoft Support before attempting to use the tool!
Happy Data Warehouse-ing! :D