http://www.sqlservercentral.com/blogs/jamesserra/2012/10/16/mds-copying-models-and-data-to-another-server/

Printed 2014/11/26 07:52PM

MDS: Copying models and data to another server

2012/10/16

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:

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.