SCSM Data Warehouse Migration / Rebuild / Move

Scenario-

1. Moving ServiceManager DB & Workflow Management Server to new Server Build
2. Reattaching Data Warehouse to the new ServiceManager DB / Workflow Server

The migration of the ServiceManager “SCJOEDB01” was pretty straight forward. I followed this guide-

https://technet.microsoft.com/en-us/library/jj900187.aspx?f=255&MSPPError=-2147217396

I installed the SCSM management role on SCJOEMGM1/2 and made SCJOEDB03 the workflow server as well as the ServiceManager DB.  I uninstalled the SCSM management role on SCJOEMGM11/12/13 and turned off the servers.  At this point SCSM was working fine from the console, the MS System Center Data Access Service was running on all servers and everything appeared to be working.  Next was to attach the Data Warehouse.  I used this guide-

https://technet.microsoft.com/en-us/library/dn464298.aspx

I had no luck attaching the existing Data Warehouse.  Luckily for this instance we didn’t archive anything to the Data Warehouse that we couldn’t recreate from the ServiceManager database.  The decision was to start from a net new server install.  This would allow us to recreate the Data Warehouse and be the simplest and quickest route (we thought).

After installing a net new Server, SQL instance, and SCSM Data Warehouse install it was time to attach the Data Warehouse to SCSM.  Here is what we got-

Error through the Console-

The Data Access Service is either not running or not yet initialized. Check the event log

I attempted to register using PowerShell on the Data Warehouse Server-

When registering with PowerShell it would half register meaning the Data Warehouse thought it registered, but it never fully initialized on the Service Manager DB.  When I attempted to re-register I would get an error “A management group with the same name is already registered to data warehouse management group.”  There are several posts out there on how to unregister with this command

“.\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1″

Get-SCDWSource.

UnRegister-SCDWSource –ComputerName serverDW –DataSourceTypeName “ServiceManager” –DataSourceName “XXX”

Now back to square one.  No matter what I did I could not attach a net new Data Warehouse or an existing from backup.  It would always error with the following-

The Data Access service is either not running or not yet initialized.  Check the event log for more information

The Data warehouse registration failed

SCOM Event viewer on ServiceManager Management Workflow server-

Log Name:      Operations Manager

Source:        Console Operations

Date:          3/1/2015 9:31:14 PM

Event ID:      33541

Task Category: None

Level:         Error

Keywords:      Classic

User:          N/A

Computer:      SCJOEDB03.scjoe.net

Description:

Unable to register Service Manager installation with Data Warehouse installation.

Data Warehouse Server: DW_SCJOE

Service Manager Management Server: Service Manager Admins

Exception: Microsoft.EnterpriseManagement.UI.Core.Shared.PowerShell.PSServiceException: The Data Access service is either not running or not yet initialized. Check the event log for more information.

at Microsoft.EnterpriseManagement.UI.Core.Shared.PowerShell.PSHostService.executeHelper(String cmd, Object[] input)

at Microsoft.EnterpriseManagement.UI.Core.Shared.PowerShell.PSHostService.Invoke(String cmd, Object[] input)

at Microsoft.EnterpriseManagement.ServiceManager.UI.Administration.DWRegistration.Registration.DWRegistrationHelper.AcceptChanges(WizardMode wizardMode)

Event Xml:

<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event“>

<System>

<Provider Name=”Console Operations” />

<EventID Qualifiers=”49152″>33541</EventID>

<Level>2</Level>

<Task>0</Task>

<Keywords>0x80000000000000</Keywords>

<TimeCreated SystemTime=”2015-03-02T05:31:14.000000000Z” />

<EventRecordID>23966</EventRecordID>

<Channel>Operations Manager</Channel>

<Computer>SCJOEDB03.scjoe.net</Computer>

<Security />

</System>

<EventData>

<Data>DW_SCJOE</Data>

<Data>Service Manager Admins</Data>

<Data>Microsoft.EnterpriseManagement.UI.Core.Shared.PowerShell.PSServiceException: The Data Access service is either not running or not yet initialized. Check the event log for more information.

at Microsoft.EnterpriseManagement.UI.Core.Shared.PowerShell.PSHostService.executeHelper(String cmd, Object[] input)

at Microsoft.EnterpriseManagement.UI.Core.Shared.PowerShell.PSHostService.Invoke(String cmd, Object[] input)

at Microsoft.EnterpriseManagement.ServiceManager.UI.Administration.DWRegistration.Registration.DWRegistrationHelper.AcceptChanges(WizardMode wizardMode)</Data>

</EventData>

</Event>

Troubleshooting Steps & Solution-

 

I validated Set-ExecutionPolicy RemoteSigned was enabled from this blog-

http://blogs.technet.com/b/servicemanager/archive/2012/06/02/faq-why-is-my-dw-registratoin-failing-with-the-data-warehouse-registration-failed.aspx

Set my Region settings to English and not “Match Windows display language”-

Set host entries from my old server names to match the corresponding new servers.  THE ERROR CHANGED.  When trying to register the DW I received an error that the credentials I was using were invalid.  That means there was something still referencing the old servers in the ServiceManager DB.

With help from a very talented and generous MS Engineer (Rob), I used the following queries against the ServiceManager DB-

select * from MT_Microsoft$SystemCenter$ResourceAccessLayer$CmdbResourceStore

select * from MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore

select * from MTV_Microsoft$SystemCenter$ResourceAccessLayer$SdkResourceStore

select * from MT_Microsoft$SystemCenter$ResourceAccessLayer$DwSdkResourceStore

What I found was the MT_Microsoft$SystemCenter$ResourceAccessLayer$SdkResourceStore table was still referencing “SCJOEMGM11” the old Management Workflow Server.

How I made the change:

• Stop the System Center Data Access, System Center Management Configuration & Microsoft Monitoring Agent services on all SCSM Management Servers
• Backup the ServiceManager DB
• Make the server name change to MT_Microsoft$SystemCenter$ResourceAccessLayer$SdkResourceStore Change this to your new SCSM WF Server Name
• Delete the Health Service State folders on your 2 management servers (C:\Program Files\Microsoft System Center 2012 R2\Service Manager\Health Service State)
• Start up the services on the 2 Management Servers
• Make sure the console opens & it works.
• Try the DW registration from PowerShell on the DW MS again

Drum roll………………………….

Conclusion-

Old entries to the previous workflow killed me.  Using Host entries hinted at the problem, and Rob’s queries pin pointed the exact table that needed to be changed.  I hope this can help someone facing this error, there is very little published on a failed Service Manager Data Warehouse registration.

LL&P