Just over a year ago, I published How to migrate SQL Server 2017 Master Data Services Models into another server article, that essentially demonstrated the migration of a SQL Server Master Data Services (MDS) model from one MDS instance to another. Like many of my previously published articles, I have since used the aforementioned article as a technical reference (i.e. syntax lookup for using MDSModelDeploy.exe utility) during the implementation of MDS-related projects for my clients.
Figure 1: SQL Server 2016 MDS instance with multiple models
As shown in Figure 1, SQL Server Master Data Services production environments tend to consist of more than one models and in recent weeks, I participated in a project that required a migration of multiple MDS models from one instance to another. It was from this project wherein I noticed that the article I have been using as my technical reference did not cover bulk-model migration of MDS models. In this follow-up article, I plan to address such a limitation by demonstrating several approaches that could be utilised in order to perform an MDS bulk-model migration from one instance to another.
MDS Bulk-Model Migration Approach #1: CMD Command Scripting
Let us begin with a simpler approach, which involves following the steps outlined in the How to migrate SQL Server 2017 Master Data Services Models into another server article. The only change required is that you will have to repeat the same process (of model migration using MDSModelDeploy.exe utility) according to the number of models you have to migrate. For instance, suppose we have to redeploy all three models shown in Figure 1 into their respective standalone MDS instances as illustrated in Figure 2. For each model, we will have to run a CMD command script that will:
- Export the model into a package file, and then,
- Run another CMD command script that will essentially deploy the model to its target MDS instance
Figure 2: MDS source to target model mapping
One obvious downside to such an approach is that it can easily be tedious and error-prone. This is because, the more MDS models you have to migrate, the longer the process becomes as you need to edit and maintain your CMD command script to ensure, amongst other things, that your source-to-target parameters are correctly specified including model name, MDS service as well as the location of the package file, as indicated in Figure 3.
Figure 3: MDSModelDeploy parameters for deploying MDS model
MDS Bulk-Model Migration Approach #2: MDS Database Backup/Restore
Instead of writing CMD commands to migrate one MDS model at a time, consider the MDS database backup/restore option which involves taking a backup of an MDS database and restoring it in another MDS environment.
-
Step 1: Backup & Restore MDS Database
Both the backup and restore of the database can be done either using a GUI-based tool like SQL Server Management Studio or it can be done using a T-SQL script.
-
Step 2: Configure the Target Database
Following a successful restore, you will then have to launch the MDS Configuration Manager and edit the Database Configuration setting of your MDS target instance to utilise the newly restored database as shown in Figure 4.
Figure 4: Database Configuration in MDS Configuration Manager
-
Step 3: Delete Unused Models
Because this approach restores the entire MDS instance, you might have to remove model(s) that are not applicable to the target environment. In order to do that, open the MDS web app, click System Administration and click Delete to remove model(s).
Figure 5: Deleting models in SQL Server MDS
Similar to CMD command scripting, the MDS database backup/restore approach has its own shortcomings. Firstly, depending on the number of models you have to migrate, you will have to copy and restore the same database backup file into several MDS environments. Once, restored, you will again have to edit Database Configuration settings of your MDS instance as well as remove copies of any models that are not applicable in a given environment.
Secondly, unlike the CMD command scripting approach, the MDS database backup/restore approach requires one to have elevated permissions to perform, amongst other tasks:
- SQL Server database backup
- SQL Server database restore
- Database file copy/share access, as well as,
- System administration access for removing unnecessary models in the MDS web app
Thirdly, this approach would require MDS web app downtime so you can perform database reconfiguration and removal of unused models. Depending on your organisation, taking your MDS instance offline might be disruptive to business operations.
MDS Bulk-Model Migration Approach #3: SSIS Scripting
Whilst both approaches (CMD command scripting and MDS database backup/restore) differ in the manner in which one goes about performing MDS bulk-model migration, they do share an underlying limitation in that both approaches strongly rely on manual execution of their processes. Most production deployments are usually performed during off-peak business hours (i.e. after hours, on weekends, holidays, etc.) and thus require deployment artefacts to be automated with little to no user intervention.
Therefore, the approaches that we have presented thus far would not pass real-world production guidelines for deployments. We rather need to look for another approach that favours automation and scheduling of all tasks related to MDS bulk-model migration. One such approach is the SSIS Scripting approach which involves calling MDSModelDeploy.exe utility within a SQL Server Integration Services (SSIS) package. For automation and scheduling, the SSIS package can also be configured to run using scheduler tools such as the SQL Server Agent job.
Just like the CMD command scripting approach, the SSIS Scripting approach employs the MDSModelDeploy.exe utility for exporting MDS models into package files and again for deployment those package files into their respective MDS target environments. However, the two approaches differ in the manner they pass parameter values to the MDSModelDeploy.exe utility. Unlike in CMD command scripting, arguments in the SSIS Scripting approach are dynamically set at SSIS package runtime.
In this article, it is assumed that the reader has a basic knowledge on SSIS components like Execute Process Task in SSIS and Foreach Loop Container.
Figure 6 shows the Execute Process Task Editor with the Executable property set to the location of our MDSModelDeploy.exe utility.
Figure 6: Execute Process Task Editor in SSIS
The Arguments property of the Execute Process Task is set via an expression as indicated in Figure 7. The expression, in turn, uses package variables to assign a model name and package file name.
Figure 7: Execute Process Task arguments in SSIS
Having configured our Execute Process Task, we turn our focus to assigning SSIS package variables. Assigning values to SSIS package variables involves a bit of work:
-
Getting a list of MDS model names
I wrote a T-SQL script that extracts a list of existing MDS models from a given MDS instance. In addition to retrieving model names, the script also created a derived column that specifies the save-as location.
1234SELECT[Name], 'C:\MDS\Export\' + [Name] + '.pkg' as [SaveAs]FROM [MDS16].[mdm].[tblModel]The execution of Script 1 returns three models from my sample MDS instance as shown in Figure 8.
Figure 8: T-Script execution results
-
Configure Execute SQL Task
Next, I wrap my T-SQL script into an Execute SQL Task component in an SSIS package.
Figure 9: Execute SQL Task Editor in SSIS
I have configured my Execute SQL Task component to return a Full result set, which gets stored in an object variable called modelList.
Figure 10: Result set mapping to an SSIS package variable
-
Configure ForEach Loop Container
Next, we configure a ForEach Loop Container to iterate through the contents of the modelList object variable.
Figure 11: ForEach Loop Editor in SSIS
For each iteration, the contents of the modelList object variable are mapped against the package variables used for assigning arguments in Figure 7.
Figure 12: Variable mappings in ForEach Loop Editor
At this point, our SSIS package has been configured with the following components:
- Execute SQL Task to extract MDS source model names
- ForEach Loop Container to iterate through the list of models and assign package variables
- Execute Process Task to export MDS model into an MDS package file
Figure 13 shows how all components have been joined together. What can also be seen is that we have added another Execute Process Task within the ForEach Loop Container to deploy MDS models to target MDS instance. Thus, for every MDS model – in our list – we export into a local directory and then import it into its target MDS instance.
Figure 13: Successful Execution of SSIS components
A copy of this package (developed in SSDT 2015) is available under the downloads section at the bottom of this article. Please feel free to customize it according to your preference. One change that comes to mind is that you can edit it to include a File System Task that will perform house cleaning by either deleting or archiving copies of MDS file packages once they have been deployed.
Summary
In this article, we’ve looked at several approaches for migrating an MDS instance containing multiple models into other MDS environments. We demonstrated the pros and cons of performing MDS bulk-model migration by running the MDSModelDeploy.exe utility via a command prompt window. We also listed all the access permissions you would need to implement the MDS database backup/restore approach. Ultimately, the SSIS Scripting approach was deemed more suitable for production deployments due to its ability to be dynamic, scheduled and automated.
Downloads
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu