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

Posted by on Mar 18, 2013 in MDS | 2 Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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:

Command

Description

listservices

Return a list of all service instances

listmodels

Return a list of all models for the specified service

listversions

Return a list of all versions for the specified model

Createpackage

Create an xml package file for the specified model

Deployclone

Deploy a clone of a model, all names and IDs will be same as package

Deploynew

Create a new model by deploying a package

Deployupdate

Update a model version by deploying a package

help

Display detailed information about a particular command

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. 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

2 Comments

  • Reza – what are the options for deploying from a SQL 2008R2 MDS to 2014? I’m preparing an upgrade of our infrastructure, but reading the docs, it seems the only supported upgrade path is an in-place upgrade of the database, not deploying models+data. This seems limiting, because yes, I could install MDS2008R2 to a “new” server, deploy packages from current production, then upgrade the new server (for testing). But then when we’re ready to actually upgrade the system, I have to start over – construct a new 2008R2 server, deploy packages, then upgrade again. Is that the only possible scenario?

    • Hi Todd, Nice to see you here 🙂
      Upgrade from 2008R2 to 2012 would be tricky. Because staging structure totally changed in 2012. I haven’t done such upgrade, so can’t tell an exact solution. But I’m thinking event in-place upgrade might not work because of that change in structure. And if you do an upgrade in a new (testing) server. then for production you can just deploy models+data from your new server to the production.
      Cheers,
      Reza

Leave a Reply

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