How to make Master Data Services database operational after restore?

From time to time we need to migrate databases to other instance or we just need to copy database within multi-tier environment. Some databases including Master Data Services database need some after restore attention. Although script bellow is not my own I decided to re-blog it because it took me a few days before I finally found it.

Problem

When you restore your Master Data Services database it is no longer working properly i.e. batches remain at Queued to Run status or e-mails responding to your business rules are not sent.

Solution

Restore your Master Data Services database to new instance. In this article database would be called [MDS]. Open new query window and run following script:

--1. Change the database owner

USE MDS_Sample

go

sp_changedbowner 'mds_dlp_login'
go

--2. Enable service broker

ALTER DATABASE MDS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MDS SET ENABLE_BROKER

ALTER DATABASE MDS SET MULTI_USER

--3. Set trustworthy property on

ALTER DATABASE MDS 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

Make sure that account running your application pool of MDS application has access to new instance ..

.. and is member of mds_exec databse role at MDS database.

And that’s it!

Advertisements

About Jiří Hubáček

Experienced consultant and blogger for Microsoft SQL Server and related products. Likes cats, workouts and delicious food. Enjoys sailing, traveling, time spent with wife and friends.
This entry was posted in Microsoft SQL Server and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s