At long last we have the Data Warehouse move documentation! This is a follow up blog post to the one about How to move the Service Manager Database.
The documentation how to do this will be added to the TechNet library once it has been formatted but you can get a copy of it here now as an attachment to this blog post.
Note: this document is for 2012 so if you need to move your 2010 DW DBs - upgrade to 2012 first and then move.
Please provide us any feedback on this in the comments below.
A special thanks to Manoj Parvathaneni (Senior Support Escalation Engineer) for going to extra effort to get this documented
Update: July 3rd 2012 - attached 2010 and 2012 versions of the DW move document.
Hi Travis, thanks a lot for this great documentation.
I have a few questions though concerning the 2012 documentation
- page 8: I can't see any "reportuser" role attached to DWRepository on my lab databases, whether on the original SQL Server or on the new SQL Server. Have I missed something ?
- when updating the dbo.MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore and the Server_ 48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA column, I see a row with a DataService named ServiceManager. I also read the "move SCSM database" documentation and this table and row is not mentioned to be modified with the new SCSM database SQL Server (in case of SCSM database move of course). Did i miss something ?
- finally, I don't see anything about moving the Analysis Service database. How can I move this one ?
Thanks again for the great job :D !!
Thanks for the feedback!
See my responses below:
[Manoj] We will verify this piece and amend the documentation accordingly. I know for sure that we need this for the DataMart databases.
[Manoj] We didn't explicitly release the database move guides for moving the ServiceManager database. The steps were released as part of the Upgrade guide under the section to build a test environment, where you rebuild the DW from scratch hence this step is not required.
However if you are just moving the ServiceManager database then the update to the row referencing the ServiceManager database in the table dbo.MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore is required. If you don't do it then you'll break the Extract job. Pretty soon we will publish similar documentation for ServiceManager database move which will include this information. Until then if you do not want to rebuild the DW then please do take this additional step.
[Manoj] Our engineering team hasn't tested this yet. However we have guidelines on how to accomplish them. Since they haven't been specifically tested by our engineering team, it would mean these steps are unsupported at this point. In our limited testing we do know these steps work. If you are willing to take the risk, you can reference the below steps to move the AS DB:
To move your Analysis Server role for SCSM 2012, here are the high level steps to follow:
1. Backup the DWASDataBase on the old AS server
2. Restore the DWASDatabase on the new AS server
3. Stop Analysis service on old AS server
4. Update DWStagingAndConfig table:
Select * from MT_Microsoft$SystemCenter$ResourceAccessLayer$ASResourceStore
--copy the BaseManagedEntityId
Set Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA='new AS Server name'
Where BaseManagedEntityId='BaseManagedEntity from above'
5. Ensure the service account for the DW services is part of the SCDW_Administrators role for DWASDatabase.
1. Restart services on DW, to ensure the health service picks up the new server name.
2. To test if the move was successful: Create some incidents, Ensure ETL jobs run to update incidentDim table with new Incidents, then process cubes and check for new Incidents on the cube.
I'll amend the 2012 DW guide pretty soon to include the AS DB move information as well!
Keep the feedback coming! :)
Thanks a lot for the fast answers and the extra procedure concerning the ASDatabase.
I'll feedback anything strange as soon as I can test this in a decent environment.
Thanks again, you rock :D !
The instructions for the section "To identify the OLAP Account used by the Data Warehouse Management Server"
state in Step g. to "Double click on CMDataMart"
I'm not seeing CMDataMart in our Test or Production environments. Instead I see "DWDataMart"
Is this the same thing or am I missing something? This is in SCSM 2012.
Any update on when the instructions for moving the AS database will be included in these documents?
I moved the DW databases to a new server running SQL Enterprise 2008 R2 and now I'm seeing lots of errors in the event viewer on the DW Management Server. Most frequently is this error:
An error countered while attempting to execute ETL Module:
ETL process type: Extract
Batch ID: 95554
Module name: Extract_Microsoft.SystemCenter.DataWarehouse.ConfigurationManager.DataSourceHasSubject_DW_PPS_SCSM_Managers
Message: Cannot open database "SMDWStagingAndConfig" requested by the login. The login failed.
Login failed for user 'PPS\svc_SCSM'.
Stack: at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at Microsoft.SystemCenter.Warehouse.Utility.Helper.GetConnection(String connectionString, DomainUser securedUser)
at Microsoft.SystemCenter.Warehouse.Utility.SqlBulkOperation.Insert(String sourceConnStrg, String sourceQuery, String destinationTable, Dictionary`2 mapping, String sqlConnectionStrg, Boolean& readerHasRows, DomainUser sourceSecureUser, DomainUser destSecureUser)
at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.Insert(DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Warehouse.Etl.ExtractModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 batchSize)
at Microsoft.SystemCenter.Warehouse.Etl.ExtractModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)
I'm not sure where to check the login credentials for the svc_SCSM account to ensure they're entered correctly. I wasn't seeing this error message prior to the move. Any thoughts?
OK, the login errors I posted above were actually a little misleading. It turns out that after we moved our three DW databases (StagingAndConfig, Repository, DataMart) we were still seeing references to the old server in the DWStagingAndConfig database. I verified this by having our DBA run some scripts on this DB looking for menttion of the %OldServerName%
It found instances of the server in the following tables:
After manually changing the entries in those two tables and restarting the System Center services on both of our management servers, the errors stopped.
Hi Travis, Manoj,
Great document(s). I've have been able to move our databases to new servers using it. The only thing I'm currently missing is how to move/replace the reporting server. We've a remote SQL Reporting Server and we want to upgrade the reporting to another OS and SQL version. Is there a writeup for this?
Data Access service on DW server (Management server is fine) won't start. I posted about this here but have no responses yet: