SCSM: Data Warehouse Troubleshooting Part 2 (Reinstall DW)

If all else fails: Reinstall the Data Warehouse (System Center Service Manager.)

IMPORTANT: Take a backup of SQL DBs before you reinstall DW. In case there are special SQL Objects that haven’t been exported or saved.

Reinstalling the DW is pretty straightforward and done in the main SCSM console. This post is targeted at individuals who know what they are doing with SCSM and DW. Hence I am not going to go through how to remove and reinstall. I though maybe more interesting to know what happens under the Hood.

Before DW is functional, it needs to run a number of (#53) deployment jobs in SQL (only visible in SQL). This has to happen before the DW can be registered in SCSM. (I don’t know, it seems that the DW will regulate this itself, so that if you register the DW directly after install the sync jobs will be queued. I am doing this very granularly)

So, while these deployment jobs are running the DWMaintenance job is stuck in Waiting status. (consequently all other DW Jobs are also “stuck” or “queued”). Notice the DWMaintenance is not just Waiting but also has Errors in the ErrorSummary (this prevents sync jobs from running)

Once these deployment jobs are finished, DW should create a new Batch job for DWMaintenance which will be released from Waiting and should then run ok.

Step-by-step

Immediately after installation has completed, SQL will look like this:

SCSM_DW_PT2_1

 

 

Query:

select WI.BatchId, WI.StatusId, WI.ErrorSummary from infra.WorkItem(nolock) WI

join infra.Batch BAT on WI.BatchId = BAT.BatchId

join infra.Process PRO on BAT.ProcessId = PRO.ProcessId

where PRO.ProcessName = ‘DWMaintenance’

select * from DeploySequenceView where DeploymentStatusId != 6

select * from DeploySequenceStaging

 

Until all the jobs in DeploySequenceView disappear. Ca 1 ½ hours

SQL will then look like this:

SCSM_DW_PT2_2

 

 

 

With a new Batch job for DWMaintenance.

In Powershell we can now see the DW_EXTRACT, Transform and Load jobs. Ready for registration.

You can check the Deployment with: select * from DeployItemView

All Items should be completed.

The Extract, Load and Transform jobs all then run, even though the DW is not registered it is an initialization of the jobs. They don’t take long to run through.

Now we can try to register the DW.

After successful Registration the Extract Job will run. All jobs can now be seen in Powershell via the Warehouse Cmdlets.

Start (Resume) the MPSyncJob if you can’t wait.

The jobs run in the following order (sort of):

 

MPSyncJob/Disable Deployment Jobs

MPSyncJob/Synchronize ServiceManger MPs

MPSyncJob/Create ServiceManagerExtracts

Extract_DW_/*

MPSyncJob/Associate Imported MP Vertex

 

Wait for a long time until all these jobs finish……. probably overnight.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s