Migrate SQL Server Master Data Services (MDS) model objects and data

By: Simon Liew   |   Updated: 2016-11-07   |   Comments (3)   |   Related: > Master Data Services

Problem

The model deployment wizard in the SQL Server Master Data Services (MDS) web application only allows for the creation of a deployment package which contains model objects only. This tip describes the steps to copy both the MDS model objects and data.

Solution

To copy the MDS model objects and data, you need to use MDSModelDeploy.exe which is a command line tool located on the MDS server. The migration process requires the MDS administrator to have full access to the MDS source and destination server.

The MDSModelDeploy tool is available in the folders below:

The syntax to be executed on the MDS source server to create a deployment package which contains the model objects and data is as follows:

MDSModelDeploy.exe createpackage -package "output deployment package" -model "MDS model name" -service "MDS service name" -version "MDS Model version" -includedata

All model objects that are included in a package are: Entities, Attributes, Attribute Groups, Hierarchies, Collections, Business Rules, Version Flags and Subscription Views.

The syntax to be executed on the MDS destination server to import the deployment package which contains the model objects and data is as follows:

MDSModelDeploy.exe deploynew –package "output deployment package" –model "MDS model name" –service "MDS service name"

File attributes, and user and group permissions are not included in a deployment package and this needs to be updated manually after a model is deployed.

For our test, we will migrate the model objects and data from SQL Server 2012 to SQL Server 2016.

MDS in SQL Server 2012

Let's say we have MDS on SQL Server 2012 installed and configured for the purpose of this tip. A model "MDS Test with Data" is created and contains two entities – Department and Employees.

The Department entity and Employees entity are populated with sample data. Note, multiple versions of master data can be created within a model, but in this tip we only have one version which is VERSION_1.  

Department Entity

Employees Entity

Extract Model objects and data into a deployment package

Log in to the source MDS server, launch a command prompt with administrator privileges and execute the below command in the appropriate MDSModelDeploy folder to get the MDS service name.

MDSModelDeploy.exe listservices

The MDS service name is indicated by MDS services (Service, Website, Virtual Path) which is MDS1.  For this tip, there is only one MDS Service installed and the service name is MDS1.

There are 3 input parameters required to generate a deployment package:

The command to generate the deployment package for this tip is shown below:

MDSModelDeploy.exe createpackage -package "C:\temp\MDS Migrate.pkg" -model "MDS Test with Data" -service MDS1 -includedata -version VERSION_1 

The above command will create a deployment package called "MDS Migrate.pkg" in the C:\temp folder. Basically the deployment package is XML and viewable with a text or XML editor.

It is advisable to type the command because sometimes copy and paste generates an error message like the one below which could mean an incomplete command or issue due to hidden special characters.

MDSModelDeploy operation failed. Elapsed time: 00:00:00.0272080
Error:
The CreatePackage command failed because either there is a missing - option
or the name is not in quotes. All names that contain spaces must be enclosed
 in quotation marks.

Deployment to MDS in SQL Server 2016

On MDS in SQL Server 2016, Explorer and Integration is grayed out when no Model exists.

We need to copy the deployment package created earlier to the MDS Server for SQL Server 2016 or have this package available at a location accessible by the 2016 server. In our case, we will copy the deployment package "MDS Migrate.pkg" to C:\temp folder on the destination MDS server.

Next login to the destination MDS server, launch a command prompt with administrator privileges and execute the command below to determine the name of the MDS service to create the package:

MDSModelDeploy listservices

From the above screen shot, we can see the MDS Server is MDS1.

The command below will deploy the package "MDS Migrate.pkg" which contains the MDS model and data to the destination MDS server.

MDSModelDeploy.exe deploynew –package "C:\temp\MDS Migrate.pkg" –model "MDS Test with Data" –service MDS1

After running the above command, the browser needs to be refreshed to view the deployed model in the MDS web application.

The deployed model can be managed from System Administration.

The data in the Department and Employees entity will be populated.

Department Entity

Employees Entity

Summary

The model deployment wizard is used to create and deploy packages with model objects only. The MDSModelDeploy.exe tool is more flexible which allows you to create and deploy packages with model objects only, or both model objects and data.

The model deployment feature, as the name suggests, is not only useful for creating a new model, creating a clone of a model, or updating an existing model and data, but also to move the MDS environment to the same or higher version.

Next Steps

• Create a Model Deployment Package by Using MDSModelDeploy
• More Master Data Services tips

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips