Share via

Troubleshoot Master Data Services Database Restore

  • Article

Users of Master Data Services: If you have recently restored your MDS database, and MDS is no longer working as it should, this article should help get you back up and running.

Run each of these scripts on your MDS database. In these examples, our database is called "MDS_Sample."

1. Change the database owner

USE MDS_Sample

go

sp_changedbowner 'mds_dlp_login'

go

 

2. Enable service broker

ALTER DATABASE MDS_Sample SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MDS_Sample SET ENABLE_BROKER

ALTER DATABASE MDS_Sample SET MULTI_USER

 

3. Set trustworthy property on

ALTER DATABASE MDS_Sample SET TRUSTWORTHY ON;

 

4. Start conversations

--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

5. Enable CLR

--Switch on CLR

EXEC sp_configure 'clr_enable', 1;

RECONFIGURE WITH OVERRIDE;

GO

You might also want to check to ensure that all of these users have the permission they are supposed to have: http://msdn.microsoft.com/en-us/library/ff486994.aspx

If you run into these issues or others and want to contribute to this article, please do!