Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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


Leave a comment on the original post [, opens in a new window]

Loading comments...