MDS: Copying models and data to another server

Having recently completed a model in Master Data Services (MDS), I needed to move the model from a development environment to a QA environment.  Below is some helpful tips if you need to do the same, along with details on a bug I ran across.

Use the Model Deployment wizard or the MDSModelDeploy tool to move models and data from dev to QA (see Deploying Models (Master Data Services).  Note the model deployment wizard does not copy master data, but the MDSModelDeploy tool will.  From Create a Model Deployment Package by Using MDSModelDeploy, if you want to move data, use “-includedata” when creating the package, otherwise, leave that switch off to copy just the model objects, business rules, and subscription views.

When using MDSModelDeploy, note that when entering the model name for the “-model” switch, it is case-sensitive.

There is a point of confusion with MDSModelDeploy with the “deploynew” command line option: the MUID (the internal model ID in table mdm.tblModel) from the source, which is stored in the package file when you create it, is not copied into the destinations mdm.tblModel table when you use “deploynew”.  Instead, a new MUID is created, but the model name stays the same.  This will result in the following two problems when trying to do an update of a model that had its MUID changed:

  • When using MDSModelDeploy with the “deployupdate” option (see Deploy a Model Deployment Package by Using MDSModelDeploy), you will get the message “The xxx model cannot be updated. There is no match for a model with the same name and MUID”, which means the model on the server you are trying to update has a different MUID from the source.  In that case you will need to delete the model on the destination and use MDSModelDeploy with the “deploynew” option.
  • When using the model deployment wizard and trying to update an existing model by following the instructions at Deploy a Model Deployment Package by Using the Wizard, if you try to use the same model name, and the model’s have a different MUID, you get “A model with this name already exists. Type a different name”.  So you will need to either delete the existing model and then run the wizard and add the model, or use a different model name.

This does not happen if you use the model deployment wizard (it will use the same MUID from the source in the destination).

So, to prevent this problem, you should use the “deployclone” command line option instead of “deploynew”, as “deployclone” will create a clone of the model and use the same MUID.  The difference between these two options was not very clear to me and I wasted a bit of time finding out the hard way.

Note that on the page Deploy a Model Deployment Package by Using the Wizard there is an error in saying the model deployment wizard will also copy the master data (“Master data is populated” under “Notes”), but it will not copy data.

Also, on pages Deploy a Model Deployment Package by Using MDSModelDeployCreate a Model Deployment Package by Using MDSModelDeployCreate a Model Deployment Package by Using the Wizard, and Deploy a Model Deployment Package by Using the Wizard, the spots where it says “model objects only” should really say “model objects/business rules/subscription views only”, as the model deployment wizard deploys all three, not just model objects.  Microsoft has said their intent has been for the term “model objects” to include those things (business rules, subscription views) and not just entities, so hopefully they will clear up the documentation for that.

I have created a Connect item about this: Master Data Services 2012 deploy

Be aware this is another bug in MDSModelDeploy where you can get an out of memory error when deploying models with large tables.  There is a fix for it:
FIX: SystemOutOfMemory error when you try to deploy a model deployment package that contains more than 100,000 rows by using the MDSModelDeploy tool in SQL Server 2012

Here are sample MDSModelDeploy commands:

C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration>MDSModelDeploy createpackage -model FMD -service mds1 -package FMD

C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration>MDSModelDeploy deployclone -service MDS1 -package FMD.pkg -model FMD

More info:
Deploying MDS Samples in SQL Server 2012

SQL 2012 Master Data Services – Model migration across environments and MDS upgrade

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in MDS/MDM, SQLServerPedia Syndication. Bookmark the permalink.

5 Responses to MDS: Copying models and data to another server

  1. Pingback: MDS: Copying models and data to another server – SQLServerCentral | Megan's Space

  2. Megan Brooks says:

    Thank you! I was preparing to do just such a migration from development to production (for the first time), and this information should save a lot of time and trouble.

  3. Hi James,

    Any word on deploying Security Permissions? It seems like they are done manually for every environment you are deploying to. Which could be tedious if you have lots of users/groups.

    Is there a way to make that process faster?


    • Rafael Barros says:

      Yeah! I have this same doubt regarding to copy security settings from an environment to another… I really appreciate any feedback about that =)

      Thanks in advance

  4. Gary says:

    Curious you used -model with deployclone…if you try that you get Error: Unexpected option -model for the DeployClone command

Leave a Reply

Your email address will not be published. Required fields are marked *