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 informationUpdate 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
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;
GOOrphaned 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
