介绍 (Introduction)
I had the opportunity to work and perform various tests with Database Experimentation Assistant (DEA) Technical Preview 1 (DEA v1) and also worked closely to DEA’s development team by reporting some issues and have their feedback on how to solve most of them. Some issues were really bugs and they had them fixed in this new release, the DEA Technical Preview 2 (DEA v2.0).
我有机会使用数据库实验助手(DEA)技术预览1(DEA v1)进行工作和执行各种测试,并且还通过报告一些问题并就如何解决大多数问题提供了反馈,从而与DEA的开发团队紧密合作。 有些问题确实是错误,它们已在新版本DEA技术预览版2(DEA v2.0)中修复。
If you did not have a chance to read my previous articles about DEA v1, here are the links for them:
如果您没有机会阅读我以前有关DEA v1的文章,请访问以下链接:
- How to download and install the SQL Server Database Experimentation Assistant (DEA) 如何下载和安装SQL Server数据库实验助手(DEA)
- How to use the SQL Server Database Experimentation Assistant (DEA) tool 如何使用SQL Server数据库实验助手(DEA)工具
In those articles I have made a call for attention for how DEA v1 was only a Technical-Preview release and that things could change as they really changed for v2.0 and the goal of this article is to let you know about those changes and also the new introduced features.
在那些文章中,我呼吁大家注意DEA v1仅仅是技术预览版,并且随着v2.0的实际变化,事情可能会发生变化,并且本文的目的是让您了解这些变化以及新引入的功能。
NOTE: DEA v2.0 it is also a Technical-Preview release so still expecting changes for the final release.
注意: DEA v2.0也是技术预览版,因此仍希望最终版本有所更改。
安装 (Installation)
Database Experimentation Assistant v2.0 supports migrating from SQL Server 2005 or higher versions to SQL Server 2012 and higher versions and has the following pre requisites and requirements.
Database Experimentation Assistant v2.0支持从SQL Server 2005或更高版本迁移到SQL Server 2012和更高版本,并且具有以下先决条件和要求。
先决条件 (Pre requisites)
- how to install, configure and use SQL Server Distributed Replay. 如何安装,配置和使用SQL Server分布式重放的文章 。
- The D-Replay client service account and the Windows user that will perform tasks in the DEA Application needs be configured to connect to the SQL Server instances using Windows authentication and also need to have sysadmin privileges in those SQL Server instances. D-Replay客户端服务帐户和将在DEA应用程序中执行任务的Windows用户需要配置为使用Windows身份验证连接到SQL Server实例,并且还需要在那些SQL Server实例中具有sysadmin特权。
- The SQL Server service account needs to have write access to the specified trace file path. SQL Server服务帐户需要具有对指定跟踪文件路径的写访问权。
- Required components section. 必需组件”部分。
系统要求 (System Requirements)
- Windows 7 or superior, Windows Server 2012 or superior Windows 7或更高版本,Windows Server 2012或更高版本
- The Microsoft .NET Framework version 4.5 or a later version. Microsoft .NET Framework版本4.5或更高版本。
Database Experimentation Assistant tool is available as an .exe file that can be downloaded from here: Microsoft® Database Experimentation Assistant Technical Preview 2 .
数据库实验助手工具是一个.exe文件,可以从这里下载: Microsoft®数据库实验助手技术预览2 。
Elect a computer that respects the above system requirements (Microsoft recommends to install DEA on the same machine as Distributed Replay controller) and copy the downloaded DatabaseExperimentationAssistant.exe file into the computer and execute it and follow the below step-by-step install instructions.
选择符合上述系统要求的计算机(Microsoft建议将DEA与Distributed Replay控制器安装在同一台计算机上),然后将下载的DatabaseExperimentationAssistant.exe文件复制到计算机中并执行,并按照以下分步安装说明进行操作。
NOTES:
笔记:
- In this new release, SNAC 2012 and MS VC++ 20150 Redistributable will be installed during DEA’s installation if they were not installed previously. 在此新版本中,如果先前未安装SNAC 2012和MS VC ++ 20150 Redistributable,则将在DEA的安装过程中安装它们。
- DReplay.exe file. DReplay.exe文件对Distributed Replay控制器执行远程调用。
- “I agree to the license terms and conditions” option and click “我同意许可条款和条件”选项,然后单击“ Install. In case you want to provide and alternative installation path, click on 安装” 。 如果您想提供替代安装路径,请单击“ Options: 选项” :
- Next button until the SNAC 2012 is installed. 下一步”按钮,直到安装了SNAC 2012。
- Install button to have it installed. 安装”按钮即可进行安装。
- Close to exit from the installation wizard: 关闭”退出安装向导:
- Control Panel / Programs and Features that Database Experimentation Assistant is installed: 控制面板/程序和功能中确认已安装数据库实验助手:
With these we have done with the DEA’s installation and it can be used immediately if the prerequisites have been fulfilled (see the next section).
有了这些,我们就完成了DEA的安装,如果满足先决条件,则可以立即使用它(请参阅下一节)。
所需组件 (Required components)
Database Experimentation Assistant has three main features:
数据库实验助手具有三个主要功能:
For the All Captures and All Replays options there is no need for extra components, but Analysis Reports has prerequisites that requires extra components to be installed in the same computer. When choosing this option without having one or more of those components installed a screen similar to the below one will be presented to you:
对于“ 所有捕获”和“ 所有重放”选项,不需要额外的组件,但是Analysis Reports具有先决条件,要求在同一台计算机上安装额外的组件。 在未安装一个或多个组件的情况下选择此选项时,将显示类似于以下的屏幕:
NOTE: If your computer already has those components installed, you won’t be able to see the above screen, meaning that you can immediately use the Analysis Reports option so no need to follow the below instructions.
注意:如果您的计算机已经安装了这些组件,则将无法看到上述屏幕,这意味着您可以立即使用“分析报告”选项,因此无需遵循以下说明。
For each of those necessary components there are, on the right side, direct links to download them.
对于每个必需的组件,在右侧都有直接链接下载它们。
R for Windows
Windows版R
R is a free open-source language for statistical computing and graphics and you can get the last version for Windows (is R-3.3.3 at the time I am writing this article) 32bit and 64bit, from R-3.3.3 for Windows (32/64 bit)
R是一种用于统计计算和图形的免费开源语言,您可以从Windows的R-3.3.3获得Windows的最新版本(在我撰写本文时为R-3.3.3)。 (32/64位)
After downloaded and copied into the target machine, execute the installation file (R-3.3.3-win.exe).
下载并复制到目标计算机后,执行安装文件( R-3.3.3-win.exe )。
It is a very straight forward wizard so just accept the defaults during the installation.
这是一个非常简单的向导,因此只需在安装过程中接受默认设置即可。
JSONLite library
JSONLite库
Check in the R installation path (default is: C:\Program Files\R\R-3.3.3\library) for a library called jsonlite. If not present, then you will need to download it from jsonlite: A Robust, High Performance JSON Parser and Generator for R and manually copy or extract it into the R library subfolder. This will permit the generation of the reports for the Analysis Report option.
检入R的安装路径(默认为:C:\ Program Files \ R \ R-3.3.3 \ library)以查找名为jsonlite的库。 如果不存在,则需要从jsonlite下载:一个健壮,高性能的JSON解析器和R生成器,然后手动将其复制或提取到R库子文件夹中。 这将允许为“分析报告”选项生成报告。
R Interop
R互操作
It is a Microsoft .NET managed abstraction layer for communicating with R and can be downloaded from .NET managed abstraction layer for communicating with R
它是用于与R通信的Microsoft .NET托管抽象层,可以从用于与R通信的.NET托管抽象层下载。
Download R Interop from the above link and copy the file into the target machine and run the RInteropSetup.msi file. It will then pop-up the license agreement screen. Check the “I accept the terms in the License Agreement” option and press the Install button and wait until is completed and then press the Finish button.
从上面的链接下载R Interop ,然后将文件复制到目标计算机,然后运行RInteropSetup.msi文件。 然后将弹出许可协议屏幕。 选中“我接受许可协议中的条款”选项,然后按“ 安装”按钮并等待直到完成,然后按“ 完成”按钮。
With this you should have all prerequisites fulfilled and you can start now working with the Database Experimentation Assistant.
这样,您应该满足所有先决条件,现在就可以开始使用Database Experimentation Assistant了 。
This can be confirmed in DEA tool by accessing the Analysis Reports option. You should see a screen similar to the following one (the Prerequisites screen should not appear anymore):
可以在DEA工具中通过访问“分析报告”选项来确认。 您应该看到类似于以下屏幕的屏幕(“先决条件”屏幕将不再出现):
如何使用 (How to use)
After installed, to start to use DEA v2.0 click on the respective icon in the desktop area:
安装后,要开始使用DEA v2.0,请单击桌面区域中的相应图标:
And this will load DEA v2.0 welcome screen:
这将加载DEA v2.0欢迎屏幕:
The tool has three main features that can be found on the left side of the navigation bar:
该工具具有三个主要功能,可以在导航栏的左侧找到它们:
- Capture a workload: Start a trace to capture a workload from the source database(s). 捕获工作负载:启动跟踪以从源数据库捕获工作负载。
- Replay captured workload: Replay the previous captured trace on target SQL Server instances. 重播捕获的工作负载:在目标SQL Server实例上重播先前捕获的跟踪。
- Analyze replayed traces: Generate and review generated reports which provide insight about the workload performance across the targeted SQL Server instances. 分析重播的跟踪:生成和查看生成的报告,这些报告提供有关目标SQL Server实例上的工作负载性能的见解。
捕获工作量 (Capture a workload)
Important: Before starting any Capture, it is very important to back up the database or databases that will be traced. This will guarantee that captured trace will be replayed in the target databases (restored from the required backup) at the same state, so the experimentation results will be more reliable.
重要:启动任何Capture之前,备份要跟踪的一个或多个数据库非常重要。 这将确保捕获的跟踪将以相同状态在目标数据库中重播(从所需的备份还原),因此实验结果将更加可靠。
Use this option to capture a workload from the source SQL Server instance by producing a trace file (.trc) based on TSQL_Replay template and it will request the input of some necessary information to produce the necessary trace file as giving the Trace a name and the source SQL Server instance name where trace will run to capture the Database workload (if a database name is not provided the trace will capture the workload of all the databases on the source SQL Server instance), provide the destination path to store source trace file on SQL Server machine, set the duration for the trace running in minutes (default is 5 minutes) and check the box “Yes, I have taken the backup of target database(s)” and then the Start button will be enable. Click on it to start the new Capture:
使用此选项可通过基于TSQL_Replay模板生成跟踪文件(.trc)从源SQL Server实例捕获工作负载,并且它将要求输入一些必要的信息以生成必要的跟踪文件,为跟踪指定名称和名称。跟踪将在其中运行以捕获数据库工作负载的源SQL Server实例名称 (如果未提供数据库名称,则跟踪将捕获源SQL Server实例上所有数据库的工作负载),提供存储目标跟踪文件所在的目标路径。 SQL Server计算机 ,以分钟为单位设置跟踪的持续时间(默认为5分钟),然后选中“是,我已备份目标数据库”框,然后将启用“ 开始”按钮。 单击它以启动新的Capture:
You will be able to see the progress of a capture workflow:
您将能够查看捕获工作流程的进度:
There are two new options relatively to DEA v1. Now you can stop the trace (Stop check box) so you do not need to wait until the end of it and Clone to create a new capture with the same information of the actual one (it will let you perform any change before starting the new capture).
相对于DEA v1,有两个新选项。 现在,您可以停止跟踪(“ 停止”复选框),因此您无需等到跟踪结束并克隆即可创建具有与实际跟踪相同的信息的新捕获(它将使您在开始新跟踪之前进行任何更改)捕获)。
Check this article to know what kind of information is being captured and how to solve known issues during the Capture phase: DEA 2.0 – Capture Trace FAQ
查看本文以了解要捕获的信息种类以及如何在捕获阶段解决已知问题: DEA 2.0 –捕获跟踪常见问题解答
When the Capture process finish with success, copy the generated trace file(s) to the Distributed Replay Controller machine and into the target SQL Server instances machines. Those trace files will be used during the Replay phase.
当捕获过程成功完成时,将生成的跟踪文件复制到Distributed Replay Controller计算机上并复制到目标SQL Server实例计算机上。 这些跟踪文件将在重播阶段使用。
重播捕获的工作负载 (Replay captured workload)
Replay process will allow you to replay the trace files (.trc) created during the Capture process from the source database(s) on the target SQL Server instances.
重播过程将允许您从目标SQL Server实例上的源数据库中重播在“捕获”过程中创建的跟踪文件(.trc)。
This action must be performed twice:
此操作必须执行两次:
- Once on the target SQL Server instance (A) that should have the same configuration as the source SQL Server instance where the trace has been captured from. 一旦位于目标SQL Server实例(A)上,该实例应具有与从中捕获跟踪的源SQL Server实例相同的配置。
- Once on the target upgraded SQL Server instance (B) that should have the same configuration as the final SQL Server instance where the source database will be migrated to. 一旦位于目标升级SQL Server实例(B)上,该实例应具有与将源数据库迁移到的最终SQL Server实例相同的配置。
After the backup of the source database(s) have been restored in both target SQL Server instances and the D-Replay environment is up and running then the Replay can be configured and executed.
在两个目标SQL Server实例中都还原了源数据库的备份之后,并且D-Replay环境已启动并正在运行,然后可以配置和执行Replay。
Start a new Replay by clicking in “+ New Replay”. This will load the page with the required fields to be filled up with the necessary information as given a name for the Replay (the name will be used to produce the target traces), the D-Replay Controller machine name and the Path to source trace file (include the trace file name in the path) previously Captured and copied into the D-Replay Controller server, the target SQL Server instance name and the path to store the target trace files (the path must exist in the target SQL Server instance and SQL Server service account must have write permission on it). With all those information provided, check the “Yes, I have restored the database(s)” option and the Start button will be enabled:
单击“ + New Replay ”开始一个新的重放。 这将为页面加载所需的字段,以填写必要的信息,并为重放指定名称(该名称将用于生成目标跟踪),D-Replay Controller计算机名称和源跟踪路径先前捕获并复制到D-Replay Controller服务器中的文件 (在路径中包括跟踪文件名), 目标SQL Server实例名称和存储目标跟踪文件的路径(该路径必须存在于目标SQL Server实例中,并且SQL Server服务帐户必须对此具有写权限)。 提供所有这些信息后,请选中“是,我已还原数据库”选项,并且将启用“ 开始”按钮:
NOTE: As opposite from the previous version, in this new version there is no need to provide D-Replay Clients as it will use all registered clients in the Controller.
注意:与先前版本相反,在此新版本中,无需提供D-Replay Clients,因为它将使用Controller中所有已注册的客户端。
The Replay process will start and the Replay progress can be followed in the screen:
重播过程将开始,并且可以在屏幕中跟踪重播进度:
When the Replay finish to run for the target 1 SQL Server instance, repeat this process for the target 2 SQL Server instance.
当重播完成以在目标1 SQL Server实例上运行时,对目标2 SQL Server实例重复此过程。
NOTE: In case of facing errors during the Replay phase, check if your D-Replay environment is correctly configured as explained in this article. A DEA 2.0: Replay FAQ is also available for consultation.
注意:如果在重播阶段遇到错误,请检查D-Replay环境是否如本文所述正确配置。 还可提供DEA 2.0:重播常见问题解答 。
分析报告 (Analysis Reports)
This option allows the generation of an analysis report by using the replay traces generated by the Replay option. With that we will gain insights on performance implications of the proposed change.
此选项允许使用“重播”选项生成的重播跟踪来生成分析报告。 这样,我们将对拟议变更的性能影响有深入的了解。
Provide the SQL Server instance name where the Analysis Reports databases will be created and press the Connect button:
提供将在其中创建Analysis Reports数据库SQL Server实例名称,然后按Connect(连接)按钮:
A + New analysis report option will appear. Click on that option to proceed.
将会出现+新分析报告选项。 单击该选项以继续。
NOTES:
笔记:
- Required Components必需组件 section in this article to see how to have them installed. 本文中的“小节”以了解如何安装它们。
- With this option you can also review previous generated reports that are stored in the provided SQL Server instance. 使用此选项,您还可以查看以前生成的报告,这些报告存储在提供SQL Server实例中。
After clicking on + New analysis report a screen similar to the below one will be presented and fields are required to be filled as a name for the Report and the SQL Server instance name where the Analysis databases will be created (the report name will be part of the database names). Provide also the path with the file name for the traces originated from the Replay option for Trace for Target 1 SQL Server and Trace for Target 2 SQL Server (if trace files are stored in different server then you must provide the full network path as \\servername\sharename$\tracefilename.trc):
单击“ +新建分析报告”后,将显示类似于下图的屏幕,并且需要填写字段作为报告的名称以及将在其中创建分析数据库的SQL Server实例名称 (报告名称将是一部分)数据库名称)。 还提供带有跟踪文件名的路径,该跟踪文件来自目标1 SQL Server的 跟踪和目标2 SQL Server的“重播”选项的跟踪选项(如果跟踪文件存储在其他服务器中,那么您必须提供完整的网络路径为\\ servername \ sharename $ \ tracefilename.trc ):
Click on the Start button and Analysis will process and respective Reports will be generated. A workflow progress is available to verify the details:
单击开始按钮,将进行分析并生成相应的报告。 工作流进度可用于验证详细信息:
When completed successfully, a similar screen as the below one will be presented with the Analysis Report so you can compare the query executions against the Target SQL Server instances.
成功完成后,将在“分析报告”中显示与以下屏幕类似的屏幕,以便您可以将查询执行与目标SQL Server实例进行比较。
To Analyze the Report just drill down into each item where you can see which queries originated errors and identify which one were degraded, improved or just had the same performance.
要分析报告,只需深入到每个项目,您可以在其中查看哪些查询引发了错误,并确定哪些查询已降级,改进或具有相同的性能。
The more you drill down, the more information you will have access to, including the query plan for each query execution so they can be compared.
向下钻取的内容越多,您将获得越多的信息,包括每个查询执行的查询计划,以便可以进行比较。
If you have issues during this phase, check DEA 2.0: Analysis FAQ for how to resolve known issues.
如果在此阶段存在问题,请查看DEA 2.0:分析常见问题解答以了解如何解决已知问题。
This is a generic way to show how easy it is to evaluate the issues and have the necessary information to try to solve them before going forward with the migration.
这是一种通用方法,可以显示在进行迁移之前评估问题并获得必要的信息来尝试解决问题是多么容易。
删除数据库实验助手 (Removing Database Experimentation Assistant)
After finishing upgrading databases and if not needed anymore, you can uninstall the Database Experimentation Assistant (DEA).
完成数据库升级后,如果不再需要,则可以卸载Database Experimentation Assistant(DEA) 。
DEA can be uninstalled by using Add or Remove Programs feature following these steps:
可以通过以下步骤使用“ 添加或删除程序”功能来卸载DEA:
- Add or Remove Programs. 添加或删除程序” 。
- Database Experimentation Assistant, right-click on it and then click 数据库实验助手” ,右键单击它,然后单击“ Uninstall. 卸载” 。
- Uninstall to proceed: 卸载继续:
With that, DEA has just been uninstalled.
这样,DEA才被卸载。
NOTE: Any required components that have been installed with DEA v2.0, as SNAC 2012 and MS VC++ 2015 Redistributable, will not be removed when uninstalling DEV v2.0. They should be uninstalled separately.
注:卸载DEV v2.0时,不会随DEA v2.0一起安装的所有必需组件(如SNAC 2012和MS VC ++ 2015 Redistributable)将被删除。 它们应单独卸载。
Previous articles in this series:
本系列以前的文章:
- How to download and install the SQL Server Database Experimentation Assistant (DEA) 如何下载和安装SQL Server数据库实验助手(DEA)
- How to use the SQL Server Database Experimentation Assistant (DEA) tool 如何使用SQL Server数据库实验助手(DEA)工具
翻译自: https://www.sqlshack.com/download-install-sql-server-database-experimentation-assistant-dea-v2-0/