Upgrading MDS 2012 to 2019

When upgrading from Master Data Services 2012 to 2019 the upgrade wizard crashes as the database objects are not compatible. So you will need to export the model and import to a new instance.
Add a panic attack when you realise that the consultant that set up MDS is not availible anymore and you dont have any access to the mds service.

The first thing to do is to create a login for you user in the master data managment database. To find your SID just open command prompt and type WHOAMI /USER

Then execute the following in the MDM database to create a user.

EXEC mdm.udpUserSave 1, NULL, ‘S-1-5-21-xxxx’,’DOMAIN\user’, 1, ‘Joe User’,’Marketing Manager’,’joe.user@domain.com’

Then give that user full permissions (mdm administrator) rights.

DECLARE @v_UserName NVARCHAR(100)
              , @v_SID NVARCHAR(250)

-- new MDS Admin
SET @v_UserName = 'DOMAIN\user'
 
-- find the new MDS Admin's SID
SELECT @v_SID = [SID] FROM  mdm.tblUser 
 WHERE UserName = @v_UserName;

-- Set the new MDS System Administrator
EXEC [mdm].[udpSecuritySetAdministrator] 
  @UserName=@v_UserName
, @SID = @v_SID, @PromoteNonAdmin = 1;  

And now you own the application – You remembered to backup the database, right?
Now, if you are on mdm 2019 the [mdm].[udpSecuritySetAdministrator] stored procedure is not in the database anymore. What it does is simple enough so you can run the script from an older one. Or just create it in the database from older versions, the world might end, there might be plagues starting but it will work.

Now find the name of the mds service by starting CMD with ‘Run as admin’ and typeing

MDSModelDeploy listservices

From the C:\Program Files\Microsoft SQL Server\1x0\Master Data Services\Configuration folder

The model names are in the [mdm].[tblModel] table and then you export the models like this

MDSModelDeploy deploynew -package c:\temp\mds\planning.pkg -model „model“ -service mds1

Then you set your permissions and you are off.