I’ve always been in favor of an orthodox strategy when it comes to applying SQL Server updates which often goes like:
在应用SQL Server更新时，我一直都支持正统策略，这种更新通常是这样的：
- Instead of installing SQL Server Cumulative Updates, wait for release of service packs 等待安装Service Pack，而不是安装SQL Server累积更新。
- When a service pack is released, install it in phases starting from the non-production environment (i.e. DEV, UAT) to eventually roll it out on production 发布Service Pack时，请从非生产环境（即DEV，UAT）开始分阶段安装它，以最终在生产中推出
However, I was recently involved in a project whereby I needed to upgrade a client’s SQL Server 2008 based MIS environment to SQL Server 2016. The MIS environment consisted of your typical Microsoft BI stack services. This was going to be a side-by-side upgrade which meant we were to set up the SQL Server 2016 MIS version on a new box without interfering with the then SQL Server 2008-based MIS production environment. It just so happens that days prior to the start of our project, SQL Server 2016 Service Pack 2 (SP2) was released and in the interest of time and the client trying to avoid having to bring in consultants again at a later time for applying SP2, it was decided that we should roll out SP2 as part of our project delivery. As you might have guessed it and as self-fulfilling prophecies go, the installation of SQL Server 2016 Service Pack 2 did not yield positive results. This is because soon after the installation, our Integration Services Catalog packages that executed Analysis Services Processing Tasks started returning the following error message:
但是，我最近参与了一个项目，该项目需要将客户端基于SQL Server 2008的MIS环境升级到SQL Server2016。MIS环境由典型的Microsoft BI堆栈服务组成。 这将是并行升级，这意味着我们将在不干扰当时基于SQL Server 2008的MIS生产环境的情况下，在新包装上设置SQL Server 2016 MIS版本。 恰好在项目开始前几天发布了SQL Server 2016 Service Pack 2（SP2），并且为了节省时间，客户端试图避免在以后的时间里再引入顾问来应用SP2 ，因此决定在项目交付过程中推出SP2。 您可能已经猜到了，随着自我实现的预言不断发展，SQL Server 2016 Service Pack 2的安装没有产生积极的结果。 这是因为安装后不久，执行Analysis Services处理任务的 Integration Services Catalog程序包开始返回以下错误消息：
In this article, I will demonstrate the effects of SQL Server 2016 Service Pack 2 on an environment that relies on SSIS to process SSAS cubes. I also present you with different options that you could utilize to resolve some of the issues that come about as a result of installing SP2.
在本文中，我将演示SQL Server 2016 Service Pack 2在依赖SSIS处理SSAS多维数据集的环境上的影响。 我还将为您提供不同的选项，您可以使用它们来解决由于安装SP2而引起的一些问题。
SSAS和SSIS解决方案 (SSAS and SSIS solutions)
Figure 1 shows my sample Demo cube.
图1显示了我的示例Demo多维数据集。
The cube solution shown is processed using a SSIS package shown in Figure 2.
所示的多维数据集解决方案使用图2所示的SSIS包进行处理。
This package is then deployed as a project into the Integration Services Catalog as shown in Figure 3.
然后将此包作为项目部署到Integration Services目录中， 如图3所示。
In addition to the SSAS and SSIS solutions, I have also created several snapshots of the VirtualBox virtual machine that I will be using as part of the demo. As shown in Figure 4, I have broken my snapshots according to type of updates that I have applied to my SQL Server 2016 instance.
除了SSAS和SSIS解决方案之外，我还创建了VirtualBox虚拟机的多个快照，将在演示中使用它。 如图4所示，我已根据应用到SQL Server 2016实例的更新类型破坏了快照。
快照1-2：SQL Server 2016 RTM和SP1 (Snapshot 1-2: SQL Server 2016 RTM & SP1)
In the SQL Server 2016 snapshots running both RTM and SP1, I’m able to successfully execute my SSIS package directly from the Integration Services Catalog without any failures as shown in Figures 6.
在同时运行RTM和SP1SQL Server 2016快照中，我能够直接从Integration Services目录成功执行我的SSIS包，而不会出现任何故障， 如图6所示。
快照3 – SQL Server 2016 Service Pack 2 (Snapshot 3 – SQL Server 2016 with Service Pack 2)
Unfortunately, once we upgrade our SQL Server 2016 instance from Service Pack 1 to Service Pack 2, the execution of my sample SSIS package from the Integration Services Catalog starts running into errors.
不幸的是，一旦我们将SQL Server 2016实例从Service Pack 1升级到Service Pack 2，从Integration Services Catalog执行我的示例SSIS程序包就会开始出现错误。
The details of the errors are outlined in Figure 8.
错误的细节在图8中概述。
解决丢失的Microsoft.AnalysisServices.AdomdClientUI.dll程序集错误的选项 (Options for resolving missing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error)
The options for resolving the missing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error ranges from simple to complex.
解决丢失的Microsoft.AnalysisServices.AdomdClientUI.dll程序集错误的选项范围从简单到复杂。
Option 1: Replace SSIS package cube processing steps with XMLA
选项1：用XMLA替换SSIS程序包多维数据集处理步骤
If the package that throws an error is as simple as my demo package in that it performs only one task – processing of analysis services cube using Analysis Services Processing task – you could replace that SSIS package step with XMLA script. The XMLA script could be called as part of SQL Server Analysis Services Command within a SQL Server Agent job step, as indicated in Figure 9.
如果引发错误的程序包与我的演示程序包一样简单，因为它仅执行一项任务–使用Analysis Services Processing任务处理Analysis Services多维数据集–您可以用XMLA脚本替换该SSIS程序包步骤。 在SQL Server代理作业步骤中，可以将XMLA脚本作为SQL Server Analysis Services命令的一部分进行调用， 如图9所示 。
Figure 9 图9 The benefit of this approach is that your SQL Server 2016 instance retains its latest service pack update and the downside being that you may require some additional time for converting Analysis Services Processing tasks to XMLA scripts.
这种方法的好处是您SQL Server 2016实例保留了其最新的Service Pack更新，并且不利的一面是您可能需要一些额外的时间才能将Analysis Services处理任务转换为XMLA脚本。
Option 2: Rollback to SQL Server 2016 Service Pack 1
选项2：回滚到SQL Server 2016 Service Pack 1
Obviously, you could have complex SSIS packages with several Analysis Services Processing Task steps that attempting to convert them to XMLA scripts could take days and further delay your project go-live. Thus, in such a scenario you are better off simply rolling back to Service Pack 1. If your SQL Server 2016 instance is hosted on a VM like in my demo instance, then you could just rollback your virtual machine to a previous snapshot containing Service Pack 1. However, if your instance is not sitting on a virtual machine or you don’t have snapshots, you could implement a rollback by uninstalling Service Pack 2 as shown in Figure 10.
显然，您可能拥有带有多个Analysis Services处理任务步骤的复杂SSIS程序包，这些步骤试图将它们转换为XMLA脚本可能需要几天的时间，并进一步延迟了项目的启动。 因此，在这种情况下，最好只回滚到Service Pack1。如果将SQL Server 2016实例托管在VM上（如我的演示实例一样），则可以将虚拟机回滚到包含Service Pack的先前快照。 1.但是，如果您的实例不在虚拟机上，或者您没有快照，则可以通过卸载Service Pack 2来实现回滚， 如图10所示。
Figure 10 图10 Option 3: Assembly binding redirect
选项3：程序集绑定重定向
The final option involves manually changing the machine configuration file – although this option works, it is my least favorite because it can easily lead to mistakes that can affect the entire server. Nevertheless, the machine configuration file is likely to be familiar to web developers than BI developers but it essentially contains server-wide configurations that often overrides configuration of web.config file. Thus, one way of dealing with the missing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error would be to install either version 12 or 14 of Microsoft.AnalysisServices.AdomdClientUI.dll and then adjust the machine configuration file to look at version 12 or 14 whenever Integration Services Catalog is looking for version 13 of Microsoft.AnalysisServices.AdomdClientUI.dll assembly file.
最后一个选项涉及手动更改计算机配置文件-尽管此选项有效，但它不是我最不喜欢的选项，因为它很容易导致会影响整个服务器的错误。 尽管如此，Web开发人员可能比BI开发人员更熟悉机器配置文件，但它实际上包含服务器范围的配置，通常会覆盖web.config文件的配置。 因此，处理丢失的Microsoft.AnalysisServices.AdomdClientUI.dll程序集错误的一种方法是安装Microsoft.AnalysisServices.AdomdClientUI.dll的版本12或14，然后调整计算机配置文件以在任何时候查看版本12或14。 Integration Services目录正在寻找Microsoft.AnalysisServices.AdomdClientUI.dll程序集文件的版本13。
You can easily find version 12 and 14 of Microsoft.AnalysisServices.AdomdClientUI.dll by simply doing a file search on your root drive as shown in Figure 11.
您只需在根驱动器上执行文件搜索即可轻松找到Microsoft.AnalysisServices.AdomdClientUI.dll的版本12和14， 如图11所示。
Figure 11 图11 Once you have located the version of Microsoft.AnalysisServices.AdomdClientUI.dll assembly file you want to install, you can then add it into the global assembly cache using the GACUTIL.exe. In my case, I have decided to install version 12 of the Microsoft.AnalysisServices.AdomdClientUI.dll assembly file using the command:
找到版本后 您要安装的Microsoft.AnalysisServices.AdomdClientUI.dll程序集文件，然后可以使用GACUTIL.exe将其添加到全局程序集缓存中。 就我而言，我决定使用以下命令安装Microsoft.AnalysisServices.AdomdClientUI.dll程序集文件的版本12：
C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools>gacutil /i “C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\Microsoft.AnalysisServices.AdomdClientUI.dll”
C：\ Program Files（x86）\ Microsoft SDKs \ Windows \ v8.1A \ bin \ NETFX 4.5.1工具> gacutil / i“ C：\ Program Files（x86）\ Microsoft Visual Studio 12.0 \ Common7 \ IDE \ PrivateAssemblies \ Microsoft.AnalysisServices.AdomdClientUI.dll”
As it can be seen in Figure 12, the assembly was successfully loaded.
从图12中可以看到，该组件已成功加载。
Figure 12 图12 Thereafter, I get the metadata of the assembly I’ve just installed using the lr option of GACUTIL. Take special note of the publicKeyToken value as we will need it when editing the machine configuration file.
之后，我使用GACUTIL的lr选项获取刚刚安装的程序集的元数据。 请特别注意publicKeyToken值，因为在编辑计算机配置文件时将需要它。
Figure 13 图13 Finally, I switch over to the machine configuration (machine.config) file which in my VM is located under C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config. I then make a copy of the machine.config file and edit the original configuration file by replacing <runtime/> element with the following:
最后，我切换到计算机配置（ machine.config ）文件，该文件在我的VM中位于C：\ Windows \ Microsoft.NET \ Framework64 \ v4.0.30319 \ Config下。 然后，我复制了machine.config文件，并通过将<runtime />元素替换为以下内容来编辑原始配置文件：
<runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="Microsoft.AnalysisServices.AdomdClientUI" publicKeyToken="89845dcd8080cc91" culture="neutral"/> <bindingRedirect oldVersion="13.0.0.0" newVersion="12.0.0.0" /> </dependentAssembly> </assemblyBinding> </runtime>Following the restarting of all my SQL Server services, I received a successful message when I next ran my SSIS package as shown in Figure 14.
重新启动所有SQL Server服务之后，下一次运行SSIS包时，我收到一条成功消息， 如图14所示。
Figure 14 图14 Option 4: Apply cumulative update for Service Pack 2
选项4：为Service Pack 2应用累积更新
At the time of writing this article, a cumulative update (CU) was released that fixes, amongst other things, the missing Microsoft.AnalysisServices.AdomdClientUI.dll assembly file error. The CU can be downloaded from this link.
在撰写本文时，已发布了累积更新（CU），该更新尤其修复了丢失的Microsoft.AnalysisServices.AdomdClientUI.dll程序集文件错误。 可以从此链接下载CU。
参考资料 (References)
- Schedule SSAS Administrative Tasks with SQL Server Agent 通过SQL Server代理计划SSAS管理任务
- Rollback procedure for SQL Server updates SQL Server更新的回滚过程
- Create an Analysis Services Job Step 创建Analysis Services作业步骤
767
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
