Migrate SQL Server Master Data Services (MDS) model objects and data
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.
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||
|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.
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.
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.
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:
|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.
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:
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.
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.
About the author
View all my tips