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.
就在一年多以前，我发表了《 如何将SQL Server 2017主数据服务模型迁移到另一台服务器》一文，从本质上演示了SQL Server主数据服务（MDS）模型从一个MDS实例到另一个实例的迁移。 像我以前发表的许多文章一样，此后我在为客户实施与MDS相关的项目期间，将上述文章用作技术参考（即，使用MDSModelDeploy.exe实用程序进行语法查找）。
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.
如图1所示，SQL Server Master Data Services生产环境往往包含多个模型，最近几周，我参加了一个项目，该项目需要将多个MDS模型从一个实例迁移到另一个实例。 正是从这个项目中，我注意到我一直用作技术参考的文章没有涵盖MDS模型的批量模型迁移。 在这篇后续文章中，我计划通过演示几种可以用来执行从一个实例到另一个实例的MDS批量模型迁移的方法来解决这种限制。
MDS批量模型迁移方法＃1：CMD命令脚本 (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:
让我们以一种更简单的方法开始，该方法涉及遵循如何将SQL Server 2017主数据服务模型迁移到另一服务器文章中概述的步骤。 唯一需要进行的更改是，您将不得不根据必须迁移的模型数量重复相同的过程（使用MDSModelDeploy.exe实用程序进行模型迁移）。 例如，假设我们必须将图1所示的所有三个模型重新部署到它们各自的独立MDS实例中， 如图2所示 。 对于每个模型，我们将必须运行CMD命令脚本，该脚本将：
- 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 运行另一个CMD命令脚本，该脚本实际上会将模型部署到其目标MDS实例
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.
这种方法的一个明显的缺点是，它很容易繁琐且容易出错。 这是因为，必须迁移的MDS模型越多，编辑和维护CMD命令脚本的过程就越长，以确保除其他外，正确指定了源到目标参数，包括模型名称，MDS服务以及包文件的位置， 如图3所示 。
MDS批量模型迁移方法2：MDS数据库备份/还原 (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.
与其编写CMD命令来一次迁移一个MDS模型，不如考虑使用MDS数据库备份/还原选项，该选项涉及对MDS数据库进行备份并将其还原到另一个MDS环境中。
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.
数据库的备份和还原都可以使用基于GUI的工具（例如SQL Server Management Studio）来完成，也可以使用T-SQL脚本来完成。
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.
成功还原后，您将必须启动MDS Configuration Manager并编辑MDS目标实例的Database Configuration设置，以利用新还原的数据库， 如图4所示。
Figure 4: Database Configuration in MDS Configuration Manager
图4：MDS Configuration Manager中的数据库配置
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).
因为此方法还原了整个MDS实例，所以您可能必须删除不适用于目标环境的模型。 为此，请打开MDS Web应用程序，单击“ 系统管理” ，然后单击“ 删除”以删除模型。
Figure 5: Deleting models in SQL Server MDS
图5：在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.
与CMD命令脚本相似， MDS数据库备份/还原方法也有其自身的缺点。 首先，根据要迁移的模型数量，您将必须将同一数据库备份文件复制并还原到多个MDS环境中。 还原后，您将不得不再次编辑MDS实例的数据库配置设置，并删除在给定环境中不适用的任何模型的副本。
Secondly, unlike the CMD command scripting approach, the MDS database backup/restore approach requires one to have elevated permissions to perform, amongst other tasks:
其次，与CMD命令脚本方法不同， MDS数据库备份/还原方法要求其中一个具有较高的权限才能执行，以及其他任务：
- SQL Server database backup SQL Server数据库备份
- SQL Server database restore SQL Server数据库还原
- Database file copy/share access, as well as, 数据库文件的复制/共享访问，以及
- System administration access for removing unnecessary models in the MDS web app 系统管理访问权限，用于删除MDS Web应用程序中不必要的模型
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 Web应用程序停机，以便您可以执行数据库重新配置和删除未使用的模型。 根据您的组织，使MDS实例脱机可能会破坏业务运营。
MDS批量模型迁移方法＃3：SSIS脚本 (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.
尽管这两种方法（ CMD命令脚本和MDS数据库备份/恢复 ）在执行MDS批量模型迁移的方式上有所不同，但它们确实存在一个潜在的局限性，因为这两种方法都强烈依赖于其过程的手动执行。 大多数生产部署通常在非高峰时段（例如，下班后，周末，节假日等）执行，因此需要在几乎没有用户干预的情况下使部署工件自动化。
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.
因此，到目前为止，我们介绍的方法不会通过实际的生产部署准则。 我们宁愿寻找另一种支持自动化和调度与MDS批量模型迁移相关的所有任务的方法。 一种这样的方法是SSIS脚本方法，它涉及在SQL Server集成服务 （SSIS）包中调用MDSModelDeploy.exe实用程序。 对于自动化和计划，还可以将SSIS包配置为使用计划程序工具（例如SQL Server代理作业）运行。
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.
就像CMD命令脚本方法一样， SSIS脚本方法使用MDSModelDeploy.exe实用程序将MDS模型导出到程序包文件中，然后再次将这些程序包文件部署到各自的MDS目标环境中。 但是，这两种方法在将参数值传递到MDSModelDeploy.exe实用程序的方式上有所不同。 与CMD命令脚本不同， SSIS脚本方法中的参数是在SSIS包运行时动态设置的。
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.
在本文中，假定读者具有SSIS组件的基本知识，例如SSIS中的执行流程任务和Foreach循环容器。
Figure 6 shows the Execute Process Task Editor with the Executable property set to the location of our MDSModelDeploy.exe utility.
图6显示了Execute Process Task Editor ，其Executable属性设置为MDSModelDeploy.exe实用程序的位置。
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.
Execute Process Task的Arguments属性是通过一个表达式设置的， 如图7所示 。 该表达式又使用包变量来分配模型名称和包文件名。
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:
配置了执行流程任务后 ，我们将重点放在分配SSIS包变量上。 将值分配给SSIS包变量涉及一些工作：
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.
我编写了一个T-SQL脚本，该脚本从给定的MDS实例中提取现有MDS模型的列表。 除了检索模型名称之外，该脚本还创建了一个派生列，用于指定另存为位置。
SELECT [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.
脚本1的执行从我的示例MDS实例返回了三个模型， 如图8所示。
Figure 8: T-Script execution results
图8：T脚本执行结果
Next, I wrap my T-SQL script into an Execute SQL Task component in an SSIS package.
接下来，我将T-SQL脚本包装到SSIS包中的“ 执行SQL任务”组件中。
Figure 9: Execute SQL Task Editor in SSIS
图9：在SSIS中执行SQL任务编辑器
I have configured my Execute SQL Task component to return a Full result set, which gets stored in an object variable called modelList.
我已经配置了Execute SQL Task组件以返回Full结果集 ，该结果集存储在名为modelList的对象变量中。
Figure 10: Result set mapping to an SSIS package variable
图10：结果集映射到SSIS包变量
Next, we configure a ForEach Loop Container to iterate through the contents of the modelList object variable.
接下来，我们配置一个ForEach循环容器，以遍历modelList对象变量的内容。
Figure 11: ForEach Loop Editor in SSIS
图11：SSIS中的ForEach循环编辑器
For each iteration, the contents of the modelList object variable are mapped against the package variables used for assigning arguments in Figure 7.
对于每次迭代，都会将modelList对象变量的内容与用于分配图7中的参数的包变量进行映射。
Figure 12: Variable mappings in ForEach Loop Editor
图12：ForEach循环编辑器中的变量映射
At this point, our SSIS package has been configured with the following components:
至此，我们的SSIS软件包已配置了以下组件：
- Execute SQL Task to extract MDS source model names 执行SQL任务以提取MDS源模型名称
- ForEach Loop Container to iterate through the list of models and assign package variables ForEach循环容器遍历模型列表并分配包变量
- Execute Process Task to export MDS model into an MDS package file 执行流程任务以将MDS模型导出到MDS包文件中
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.
图13显示了如何将所有组件连接在一起。 还可以看到，我们在ForEach循环容器中添加了另一个执行过程任务 ，以将MDS模型部署到目标MDS实例。 因此，对于列表中的每个MDS模型，我们都导出到本地目录，然后将其导入到其目标MDS实例。
Figure 13: Successful Execution of SSIS components
图13：成功执行SSIS组件
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.
该软件包的副本（在SSDT 2015中开发）可在本文底部的下载部分下找到。 请随时根据自己的喜好对其进行自定义。 想到的一项更改是，您可以对其进行编辑以包含一个文件系统任务 ，该任务将通过在部署MDS文件包后删除或存档副本来执行内部清理。
摘要 (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.
在本文中，我们研究了几种将包含多个模型的MDS实例迁移到其他MDS环境的方法。 我们通过在命令提示符窗口中运行MDSModelDeploy.exe实用工具，演示了执行MDS批量模型迁移的利弊 。 我们还列出了实现MDS数据库备份/还原方法所需的所有访问权限。 最终，由于SSIS脚本方法具有动态，计划和自动化的功能，因此被认为更适合生产部署。
资料下载 (Downloads)
- MDS Bulk Model Migration – SSIS PackageMDS批量模型迁移– SSIS包
翻译自: https://www.sqlshack.com/bulk-model-migration-in-sql-server-master-data-services/
9703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
