MDS: Deploy Master Data Services Model with Data From One Server to Another

In a multi environment system which you might have Development, UAT and Production for your master data services, the requirement for transfer MDS models across different master data services will be sensible. sometimes you just need to transfer model structure (metadata) from one server to another, but in some situations you will need to have a copy of existing data as well when you do the transfer.  

There is an option in Master Data Services System -> Deployment which create package from existing model and then this package can be used in another Master Data Services instance to import that model. The wizard for Deployment is very easy to work with, but it only transfer Metadata. So if you want to transfer data and metadata together then you cannot use deployment wizard, but you can use model deployment command line tool.

In this post I explain how to find this tool and use it with appropriate command options.

There is a command line tool to transfer models across MDS instances, this tool is MDSModelDeploy.exe

you can find executable file under SQL Server MDS installation folder, this is an example of default path for this file:

drive:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration

for working with MDSModelDeploy you can go to Command Prompt and then from command line go to configuration folder( like path above) and then run this command:

MDSModelDeploy

Running this command without any options or switches will return list of all commands which listed below:

Some Examples

Let’s start from reading the service name, (this is just a warm up for familiarizing yourself with this command line tool, so if you want to go strait to the command for transfer/deploy go to Transfer/Deploy section)

run this command :

MDSModelDeploy listservices

you will see list of MDS service instances that comes. 

please note that if you get an error related to permission in this stage you might have one of these permission issues:

-you need to run Command prompt as a different user and login with the user who has access to master data services

-you need to give access for the user to the web.config file in WebApplication folder in Master Data Services installation folder ( drive:\Program Files\Microsoft SQL Server\110\Master Data Services\WebApplication\web.config) 

then you can run command below to see list of models under each service; (in example below MDS1 is name of service instance, so you will need to use service instance name that you’ve got as a result of listservices command)

MDSModelDeploy listmodels -service MDS1 

result of this command will shows all models that user has access to them

you can also query command below to get list of versions related to a model

MDSModelDeploy listversions -service MDS1 -model MyModelName

please note that if your model name contains some spaces then you will need to put that into double quote like this: (this rule apply on every command options and switches of MDSModelDeploy tool)

MDSModelDeploy listversions -service MDS1 -model "My Model Name"

you can run this command to get help about each of other commands like:

MDSModelDeploy help listmodels

Transfer/Deploy

for transfer a model we need to create a package first in source MDS instance and then deploy that package in destination MDS. 

Create Package at source

for creating a package from existing model with only metadata you can run this command:

MDSModelDeploy createpackage -service <servicename> -model <modelname> -package <packagepath>

example:

MDSModelDeploy createpackage -service MDS1 -model "My Model" -package "e:\exported model.pkg"

for creating a package from existing model with metadata and Data you can run this command:

MDSModelDeploy createpackage -service <servicename> -model <modelname> -version <versionname> -package <packagepath> -includedata

please note that when you use -includedata option then you will need to use -version switch and mention version name exactly (if you don’t know the version you can query all versions of that model with listversions command which explained in previous section)

this command might take long time to execute (depend on volume of data in that model)

after creating package you will have a package with .PKG extension that contains metadata of model (and if you used -includedata switch it will contains data as well)

Deploy Package at Destination

you can copy the package to a path on destination or a shared path that you can access from destination server

then you can deploy package with three commands: DeployClone, DeployNew and DeployUpdate

using commands are easy for example if you want a clone copy from package file you can run this command:

MDSModelDeploy deployclone -package <packagename>

this command might take long time to execute if the package contains data. 

for those who wants to deploy package from WebUI: please note that if you have a large package file (if you included data in that file), then you might face an error because of large request size, so the best option is MDSModelDeploy utility.

Hope it helps you all. 

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.