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


By:   |   Updated: 2016-11-07   |   Comments (3)   |   Related: More > 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:

MDS based on SQL Server version

Path

MDS in SQL Server 2012 C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration
MDS in SQL Server 2014 C:\Program Files\Microsoft SQL Server\120\Master Data Services\Configuration
MDS in SQL Server 2016 C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

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.

MDS Test with Data model that 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

Department Entity in SQL Server 2012 Master Data Services

Employees Entity

Employee Entity in SQL Server 2012 Master Data Services

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

Execute the command in the appropriate MDSModelDeploy folder to get the MDS service name

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:

Input parameter

Value

MDS model name MDS Test with Data
MDS service name MDS1
MDS model version VERSION_1

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.

The SQL Server Master Data Services command to generate the deployment package

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

Execute the command to determine the name of the MDS service to create the package

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

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

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

Refresh the browser to view the deployed model in the MDS web application

The deployed model can be managed from System Administration.

Manage Entities in the SQL Server 2016 Master Data Services

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

Department Entity

Department Entity Populated

Employees Entity

Employee Entity Populated

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


Last Updated: 2016-11-07


get scripts

next tip button



About the author
MSSQLTips author Simon Liew 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.

View all my tips
Related Resources





Comments For This Article




Wednesday, May 03, 2017 - 6:35:11 AM - PADMARAO Back To Top (55408)

 

while applying business rules in 2016 MDS, i'm getting below errors, can you pls guide me,

Can not specify more than one entity in MetadataGet

The attribute referance is not valid. The attribute was not found.g.


Tuesday, January 17, 2017 - 11:32:21 PM - Simon Liew Back To Top (45385)

Hi trishanth,

Apology but I haven't seen this error message before. I can only suggest you to enable logging in MDS to try to understand the exception

https://blogs.msdn.microsoft.com/jason_howell/2012/09/13/enabling-logging-to-troubleshoot-mdsmodeldeploy-exe/


Monday, January 16, 2017 - 12:59:42 PM - trishanth Back To Top (45351)

 Hi this article is greatly helpful. I am new to MDS and was trying to migrate MDS from 2014 to 2016. I am still unable to figure out this below error, also cant find any information online about this. it will be great help if you can provide any information on this.

 

 

Error message: package deployment failed with 6 errros

Can not specify more than one entity in MetadataGet

The attribute referance is not valid. The attribute was not found.

 



download





Recommended Reading

Resolving SQL Server Master Data Services (MDS) Patching Errors

SQL Server Master Data Services (MDS) Tips and Tricks

Business Rules Extension in Master Data Services 2016

How to use Attribute Filters in Master Data Services 2016

Master Data Services Versions and Views for SQL Server








get free sql tips
agree to terms


Learn more about SQL Server tools