Restoring Master Data Services databases to different domains

Overview

• Before performing the restore on the desired DEV/TEST environment, you'll need the SSIDs for the user (who will be admin on DEV and TEST as admin on Production). There are ways to access this data using Active Directory but the easiest way is to log onto the existing mdm_hub and query the information out of the [mdm.tblUser] table (first row).
• Make a note of the MUID, SID, UserName and DisplayName. 

Restore the mdm database

You may need to run the following SQL to clear connections to the database to allow the restore to run:

alter database mdm_db set single_user with rollback immediate;
go

alter database mdm_db set multi_user;
go

• Restore the mdm_db database from the backup file over the top of the existing database

Correct user SID values in mdm.tblUser

• Update the admin user in mdm.tblUser to reflect the TEST admin details

Delete any mdm_db snapshots from the target server

See below for more information

Update the SID for the service account

This is a problem found when restoring MDS from TEST where the SID for the admin user (which is also the service account) didn't match the SID in the master database.
Run this to fix:

USE master;
ALTER AUTHORIZATION ON DATABASE:: mdm_db TO admin;

Check IIS website authentication settings

• Open up the IIS manager using the object explorer to browse to the MDS website.
  • Check that the Authentication at the website level has everything disabled apart from Windows Authentication
• Expand the MDS website to click once on the Service folder
  • Check that Anonymous Authentication is enabled here
• Restart the web services using the "iisreset" command in Start->Run
• Open IE on the mds web server and navigate to http://mds and the website should open successfully
• From here you can add in the TEST users as required

If you find that business rules or the staging batch process don't run properly run the following code to get the background processes running (not required yet but could be depending on when the backup was taken):

ALTER DATABASE mdm_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mdm_db SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mdm_db SET ENABLE_BROKER;
ALTER DATABASE mdm_db SET MULTI_USER;
ALTER DATABASE mdm_db SET TRUSTWORTHY ON;


--Script to start the member security message queue
DECLARE @memberHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @memberHandle
      FROM SERVICE [microsoft/mdm/service/system]
      TO SERVICE N'microsoft/mdm/service/securitymember'
      ON CONTRACT [microsoft/mdm/contract/securitymember]
      WITH ENCRYPTION=OFF; --is by default

--Script to Start the member security timer queue
DECLARE @memberSecHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @memberSecHandle
      FROM SERVICE [microsoft/mdm/service/securitymembertimer]
      TO SERVICE N'microsoft/mdm/service/system'
      --WITH RELATED_CONVERSATION_GROUP=0x1;
      WITH ENCRYPTION=OFF --is by default
BEGIN CONVERSATION TIMER (@memberSecHandle) TIMEOUT = 30;
GO
--Script to Start the StagingBatch Queue
DECLARE @stagingHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @stagingHandle
      FROM SERVICE [microsoft/mdm/service/stagingbatch]
      TO SERVICE N'microsoft/mdm/service/system'
      WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@stagingHandle) TIMEOUT = 30;
GO
--Script to Start the Notification Queue
DECLARE @notifyHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @notifyHandle
      FROM SERVICE [microsoft/mdm/service/notification]
      TO SERVICE N'microsoft/mdm/service/system'
      WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@notifyHandle) TIMEOUT = 30;
GO

EXEC sp_configure 'clr_enable', 1;
RECONFIGURE WITH OVERRIDE;
GO

Orphaned Database Snapshots prevents configuration manager database connection

Problem:

Orphaned database snapshots in dev and test environments can stop you being able to use the MDS Configuration Manager to connect to the database. This occurs if you restore the mdm_db database when a database snapshot exists on the target server. Because the snapshot is orphaned it gets left in SUSPECT mode.

SELECT name,state_desc 
FROM sys.databases

Solution:

Delete the database snapshot and make sure the database is in ONLINE mode.

More Info:

To check for the error you can run a profiler trace which will bring up an Exception 926 when trying to connect with configuration manager.
Books online error 926