Share via

Fixing Service Manager Data Warehouse Registration Information

  • Article

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

SCSMRegisterDW.zip

Comments

  • Anonymous
    January 01, 2003
    The comment has been removed
  • Anonymous
    January 01, 2003
    Fixed - now it should not run into the error if you try to do -a:rem when the initial User Account which was given on the original DW register from the Console is missing from the SecureReferences. This is actually exactly one of the few scenarios where you need to run REM instead of ADD because I don't create the SecureReference if it's not there already for security purposes.
  • Anonymous
    January 01, 2003
    No doubt a handy piece of work. Great tool indeed.

    I wish somebody could make a utility to export existing data from corrupted DW house and import into freshly registered one.

    This because DW is the weakest link in the chain in the solution. Any upgrade, can render it corrupted. Above tool would really make the solution dependable.
  • Anonymous
    October 13, 2014
    Mihai Sarbulesco , Support Escalation Engineer from Romania, have written a cool blog post on troubleshooting
  • Anonymous
    December 02, 2014
    Mihai Sarbulescu a écrit un article pour dépanner et fixer l'enregistrement du service manager data warehouse.
    Vous trouverez également un petit outil permettant de résoudre les problèmes de votre data warehouse
  • Anonymous
    June 13, 2016
    Very helpful page, thank you!!!
  • Anonymous
    June 24, 2016
    Muchas gracias! Salvo mi ambiente de desarrollo :)
  • Anonymous
    November 17, 2016
    FIXED - I had a problem registering the DW after moving the database to another server . After troubleshooting, I discovered the cause of the problem. Thanks!
  • Anonymous
    December 02, 2016
    HiFantastic tool - was tearing my hair out and looking to move to a totally new enviornment - ran this and have now successfully restored the connection to the DW. Many thanks.
  • Anonymous
    December 21, 2016
    Muchas gracias! I had a problem registering the DW after Password Change of Service User. Problem resolved!