SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. 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 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...