sql server分布式_如何安装，配置和使用SQL Server分布式重播-CSDN博客

本文介绍了SQL Server分布式重播功能，该功能自SQL Server 2012开始提供，用于在升级的测试环境中重播工作负载。与SQL Server Profiler不同，它能从多台计算机重播，更有效地模拟关键任务工作负载。分布式重播也可用于应用程序兼容性测试，帮助从旧版SQL Server升级。文章详细阐述了安装、配置和使用步骤，包括控制器和客户端的安装、环境设置、工作负载的捕获和重播，以及结果审查。

摘要由CSDN通过智能技术生成

sql server分布式

介绍 (Introduction)

The Microsoft SQL Server Distributed Replay feature has been provided by Microsoft since SQL Server 2012 and it is used to replay captured traces against an upgraded test environment.

自SQL Server 2012起，Microsoft已提供Microsoft SQL Server分布式重播功能，该功能用于针对升级的测试环境重播捕获的跟踪。

It is similar to SQL Server Profiler with the difference that it can replay a workload from multiple computers (up to 16) and better simulate a mission-critical workload (performance testing and capacity planning).

它与SQL Server Profiler相似，区别在于它可以从多台计算机（最多16台）重播工作负载，并更好地模拟关键任务工作负载（性能测试和容量计划）。

It can also be used for application compatibility testing by help upgrading databases from older SQL Server versions (MSSQL 2005 or higher versions) to the more recent one (MSSQL 2016 when this article has being written), based in a given workload that is previously captured from the source database and later replayed on the target SQL Server instances for comparison.

它还可以根据先前捕获的给定工作负载，通过帮助将数据库从较旧SQL Server版本（MSSQL 2005或更高版本）升级到较新的版本（在撰写本文时为MSSQL 2016）来用于应用程序兼容性测试。从源数据库中读取，然后在目标SQL Server实例上重播以进行比较。

安装 (Installation)

先决条件 (Prerequisites)

The only prerequisite needed for Microsoft SQL Server Distributed Replay is to have 2 service accounts created in the Active Directory. One of the service accounts will be used for the Distributed Replay Controller service and the other will be used for the Distributed Replay Client services.

Microsoft SQL Server分布式重放的唯一先决条件是在Active Directory中创建2个服务帐户。其中一个服务帐户将用于“分布式重播控制器”服务，另一个将用于“分布式重播” 客户服务。

NOTE: You can still install and work with Microsoft SQL Server Distributed Replay by using local accounts only but this will not let you work with many computers to test the workload, i.e. you will need to use a single server as Distributed Replay Controller and Client.

注意：您仍然可以仅通过使用本地帐户安装和使用Microsoft SQL Server分布式重播，但这不能让您使用多台计算机来测试工作负载，即，您将需要使用一台服务器作为分布式重播控制器和客户端。

Check further at the end of this chapter for the necessary configurations after installed the environment.

安装环境后，请在本章末进一步检查必要的配置。

服务器（控制器）安装 (Server (Controller) installation)

Microsoft SQL Server Distributed Replay is a feature that can be installed through the SQL Server setup media. Follow these to have it installed:

Microsoft SQL Server分布式重放是一项可以通过SQL Server安装媒体安装的功能。请按照以下步骤进行安装：

Run the setup.exe file that can be found in your SQL Server installation media. In the Installation Center screen click on Installation and then on New SQL Server stand-alone-installation or add features to an existing installation:

运行在SQL Server安装媒体中可以找到的setup.exe文件。在“安装中心”屏幕中，单击“ 安装” ，然后单击“ 新建SQL Server独立安装”或向现有安装中添加功能 ：
After providing the Product Key, accepting the License Terms and configuring the Microsoft Updates you will be facing the Feature Selection screen. In case of adding the feature to an existing installation you will only need to check the Distributed Replay Controller and Distributed Replay Client features (only install the Client in the Controller computer if you want that server to act as client as well) otherwise select all the features that you need plus these last two related to Distributed Replay feature:

提供产品密钥，接受许可条款并配置Microsoft更新后，您将面临“ 功能选择”屏幕。如果将功能添加到现有安装中，则只需要检查Distributed Replay Controller和Distributed Replay Client功能（如果希望该服务器也充当客户端，则仅在Controller计算机中安装Client），否则请选择所有您需要的功能以及与分布式重播功能有关的后两个功能：
In the Server Configuration step you will see the services and the respective virtual server accounts that will be created (note: the services may differ depending on the features you choose to install):

在“ 服务器配置”步骤中，您将看到服务以及将要创建的各个虚拟服务器帐户（注意：这些服务可能会有所不同，具体取决于您选择安装的功能）：
In the Distributed Replay Controller step, specify the users that should have permissions for the service. You will need to had the service accounts for the Distributed Replay Controller and Distributed Replay Client services mentioned in the Prerequisites section (you can also add your user by clicking on the “Add Current User” button):

在“ 分布式重播控制器”步骤中，指定应具有该服务权限的用户。您将需要具有先决条件部分中提到的Distributed Replay Controller和Distributed Replay Client服务的服务帐户（也可以通过单击“添加当前用户”按钮来添加用户）：
In the Distributed Replay Client step you need to provide the Controller Name (the target SQL Server that will receive the workload) and the Working and Result directories (note: you can configure this later, after install it, by editing the Distributed Replay Client configuration file):

在“分布式重放客户端”步骤中，您需要提供“控制器名称”（将接收工作负载的目标SQL Server）以及“工作”和“结果”目录（注意：安装后，您可以在以后通过编辑“分布式重放客户端”配置进行配置）文件）：
Review the installation information and if everything is correct click on Install button and wait until it is completed successfully:

查看安装信息，如果一切正确，请单击“ 安装”按钮，然后等待其成功完成：
It can be confirmed with the Window Services that the features have been installed successfully and it can also be confirmed by checking if the directories (DReplay*) and files exists in the installation path:

可以通过Window Services确认功能已成功安装，也可以通过检查安装路径中是否存在目录（ DReplay * ）和文件来确认：

With these we have done with the Distributed Replay installation in the Controller server. Now proceed with the installation of the Distributed Replay Client in each machine that was elected to run the traces against the Controller server, by running the SQL Server setup again in each of those machines and chose only the Distributed Replay Client to be installed.

有了这些，我们就完成了Controller服务器中的Distributed Replay安装。现在，通过在每台机器上再次运行SQL Server安装程序并仅选择要安装的Distributed Replay Client，在选择对Controller服务器运行跟踪的每台计算机上继续安装Distributed Replay Client。

分布式重播配置 (Distributed Replay Configuration)

After completely install the Controller and the Clients you should now configure your environment.

完全安装Controller和客户端之后，现在应该配置环境。

NOTE: For the following instructions I am assuming that no firewall exists between the servers or if it exists then I am assuming that all rules are configured in the firewall(s) to let pass the necessary traffic as explained in this post-installation steps article.

注意：对于以下说明，我假设服务器之间不存在防火墙，或者如果存在，则假设防火墙中配置了所有规则，以使必要的流量通过，如本安装后步骤文章所述。。

登录到已安装分布式重放控制器的服务器，然后执行以下操作：

In the target SQL Server instance create the login for the Distributed Replay Client service account and give it the sysadmin server role:

在目标SQL Server实例中，为“分布式重播客户端”服务帐户创建登录名，并将其赋予sysadmin服务器角色：
```
 
USE [master]
GO
CREATE LOGIN [DOMAIN\ReplayClient_service] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\ReplayClient_service]
 
```
Have all Distributed Replay Client machines pointing to the correct Distributed Replay Controller. This should be done if you indicated the proper Distributed Replay Controller during the Distributed Replay Client installation. Otherwise edit the file DReplayClient.config located in C:\Program Files (x86)\Microsoft SQL Server\<MSSQL version>\Tools\DreplayClient (that is the Distributed Replay Clients default location) and provide the correct Distributed Replay Controller name:
使所有Distributed Replay Client计算机都指向正确的Distributed Replay Controller。如果在“分布式重播客户端”安装过程中指定了正确的“分布式重播控制器”，则应执行此操作。否则，编辑文件DReplayClient.config位于C：\ Program Files文件（x86）的\ Microsoft SQL Server的\ <MSSQL版本> \工具\ DreplayClient（也就是分布式重播客户端的默认位置），并提供正确的分布式重播控制器名称：

<Controller>Controller Server Name</Controller>
<Controller> 控制器服务器名称 </ Controller>
Open the Computer Management tool and in the left pane drill down to Computer Management (Local)\Local Users and Groups, click on Groups folder and in the detail pane right click on Distributed COM Users and chose the “Add to Group…” option and add the Distributed Replay Controller and Client service accounts:

打开“ 计算机管理”工具，然后在左窗格中深入到“ 计算机管理（本地）\本地用户和组” ，单击“ 组”文件夹，然后在详细信息窗格中右键单击“ 分布式COM用户”，然后选择“添加到组…”选项，然后添加分布式重播控制器和客户端服务帐户：
Go to Control Panel\System and Security\Administrative Tools and open the Local Security Policy tool. In the left pane, drill down to Security Settings\Local Policies\User Rights Assignment then on the right pane right click on “Allow log on locally” and choose Properties and add the Distributed Replay Controller and Client service accounts:

转到“ 控制面板” \“系统和安全性” \“管理工具”，然后打开“ 本地安全策略”工具。在左窗格中，向下钻取到“ 安全性设置\本地策略\用户权限分配”，然后在右窗格中右键单击“允许本地登录”，然后选择“ 属性”并添加“分布式重播控制器”和“客户端服务”帐户：
Go to Control Panel\System and Security\Administrative Tools and open the Component Services tool. In the left pane, drill down to Console Root\Component Services\Computers\My Computer\DCOM Config and right click on “DReplayController” and choose Properties. In the Properties window, go to Security tab and for each of the available permission options, click on Customize and Edit… button and then add Distributed Replay Controller and Client service accounts and check all available permissions for each one of them:

转到控制面板\系统和安全性\管理工具，然后打开组件服务工具。在左窗格中，向下钻取到Console Root \ Component Services \ Computers \ My Computer \ DCOM Config，然后右键单击“ DReplayController”并选择Properties。 在“属性”窗口中，转到“ 安全性”选项卡，对于每个可用的权限选项，单击“ 自定义和编辑...”按钮，然后添加“分布式重播控制器”和“客户端”服务帐户，并检查每个帐户的所有可用权限：
Start or Restart the Distributed Replay Controller service and do the same for each of the Distributed Replay Client service as well.

启动或重新启动Distributed Replay Controller服务，并对每个Distributed Replay Client服务也执行相同的操作。
Test the environment with Dreplay.exe status by opening a command prompt window and position yourself in the folder where DReplay.exe is installed (default location is the same path for management tools – C:\Program Files (x86)\Microsoft SQL Server\<MSSQL version>\Tools\Binn) and run it with the status parameter. It should show all registered clients with status=READY (if not, check for errors in the log in DreplayController\Log subfolder and review the configurations if necessary):

通过打开命令提示符窗口并以Dreplay.exe状态测试环境，并将自己置于安装DReplay.exe的文件夹中（默认位置与管理工具的路径相同– C：\ Program Files（x86）\ Microsoft SQL Server \ <MSSQL版本> \ Tools \ Binn ），然后使用status参数运行它。它应显示所有已注册的客户端，其状态为READY （如果不是，请检查DreplayController \ Log子文件夹中日志中的错误，并在必要时查看配置）：

NOTE: There are many other configurations that can be set but for the sake of this article I will continue with the default parameters. For more configurations items please refer to the proper MSDN article, Configure Distributed Replay.
注意：可以设置许多其他配置，但是出于本文的考虑，我将继续使用默认参数。有关更多配置项目，请参阅正确的MSDN文章“ 配置分布式重播” 。

使用分布式重放 (Working with Distributed Replay)

设置环境 (Setting the environment)

The goal of the Microsoft SQL Server Distributed Replay feature is to perform an assessment for a database migration and/or workload so you can check about the reliability and expected performance before the migration occurs.

Microsoft SQL Server分布式重放功能的目标是对数据库迁移和/或工作负载执行评估，以便您可以在迁移发生之前检查可靠性和预期性能。

To replicate the environment you will need to take a backup of the database or databases that will be migrated and restore those in the target server where it will be tested (same server where Distributed Replay Controller is installed). Be aware that any necessary object it will also needed to be created in the target server (for example Linked Servers).

要复制环境，您将需要备份要迁移的一个或多个数据库，并在将要对其进行测试的目标服务器（安装了Distributed Replay Controller的同一服务器）中还原这些数据库。请注意，还需要在目标服务器（例如，链接服务器）中创建任何必需的对象。

捕获工作量 (Capturing the workload)

First step to be able to work with the Distributed Replay feature is to have at least a trace file created with the required events captured. To save time defining the trace you can use SQL Server Profiler since it has a template really prepared for Distributed Replay traces called TSQL_Replay:

能够使用分布式重播功能的第一步是至少创建一个捕获了所需事件的跟踪文件。为了节省定义跟踪的时间，可以使用SQL Server Profiler，因为它确实为分布式重放跟踪准备了一个模板，称为TSQL_Replay ：

支持的输入跟踪和目标服务器组合 (Supported Input Trace and Target Server Combinations)

The following table lists the supported versions of trace data for the source database and for each the supported versions of the target SQL Server that data can be replayed against.

下表列出了源数据库的跟踪数据的受支持版本以及可以重播数据的目标SQL Server的每个受支持版本。

Version of Input Trace Data	Supported Versions of SQL Server for the Target Server Instance
SQL Server 2005/2008	From SQL Server 2008 to SQL Server 2014
SQL Server 2008 R2	From SQL Server 2008 R2 to SQL Server 2014
SQL Server 2012	SQL Server 2012 and SQL Server 2014
SQL Server 2014	SQL Server 2014
SQL Server 2016	SQL Server 2016

输入跟踪数据的版本	目标服务器实例SQL Server支持的版本
SQL Server 2005/2008	从SQL Server 2008到SQL Server 2014
SQL Server 2008 R2	从SQL Server 2008 R2到SQL Server 2014
SQL Server 2012	SQL Server 2012和SQL Server 2014
SQL Server 2014	SQL Server 2014
SQL Server 2016	SQL Server 2016

In resume, the lowest SQL Server version that can be used as source database is MSSQL 2005 and the minimum version for the target database is SQL Server 2008. You cannot replay a trace to a lowest version from the source database and cannot either to replay traces into a SQL Server 2016 instance unless the trace was also created from a SQL Server 2016 instance.

在简历中，可以用作源数据库的最低SQL Server版本是MSSQL 2005，而目标数据库的最低版本是SQL Server2008。您不能将跟踪从源数据库重播到最低版本，也不能重播跟踪除非还从SQL Server 2016实例创建了跟踪，否则将其放入SQL Server 2016实例。

准备输入的跟踪数据 (Prepare the input Trace data)

After captured a workload into a trace file or files, next step is to prepare the input trace data. This will process the trace data and generate an intermediate file that will be used in the Replay process.

将工作负载捕获到一个或多个跟踪文件中之后，下一步是准备输入的跟踪数据。这将处理跟踪数据并生成将在重播过程中使用的中间文件。

Open a command prompt window and position yourself in the folder where DReplay.exe is installed (default location is the same path for management tools – C:\Program Files (x86)\Microsoft SQL Server\<MSSQL version>\Tools\Binn) and run it with the preprocess parameter and provide the trace file with the -i parameter and the path location for the generation of the intermediate file with the -d parameter.

打开命令提示符窗口，然后将自己置于安装DReplay.exe的文件夹中（默认位置与管理工具的路径相同-C：\ Program Files（x86）\ Microsoft SQL Server \ <MSSQL版本> \ Tools \ Binn）并使用preprocess参数运行它，并为跟踪文件提供-i参数，并为生成中间文件的路径位置提供-d参数。

NOTE: If you are running in a computer different from the one elected as Controller (where the Distributed Replay Controller service is running) you will need to provide also the Controller name with the -m parameter.

注意：如果您在与被选为控制器的计算机（运行“分布式重播控制器”的计算机）不同的计算机上运行，则还需要提供带有-m参数的控制器名称。

In the example below I have copied the trace file from the source server into the Controller server in the same folder where I will keep the intermediate file generated by DReplay:

在下面的示例中，我已将跟踪文件从源服务器复制到Controller服务器的同一文件夹中，该文件夹将保留DReplay生成的中间文件：

重播跟踪数据 (Replay the Trace data)

Now that we have the intermediate file created (from the previous step), we can now replay the trace data against the target SQL Server instance. For that and still in the command prompt (if you closed it, reopen it and go to the folder where DReplay.exe is) run DReplay.exe with the replay parameter and provide the path location where the intermediate file has been generated with the -d parameter. Also provide the –o parameter so the replay activity will be saved into a result trace file in each server that is acting as DReplay client and this will allow the analysis of those results. At last provide the DReplay clients with the –w parameter, separating the client server names with a comma:

现在，我们已经创建了中间文件（来自上一步），现在可以针对目标SQL Server实例重播跟踪数据。为此，仍然在命令提示符下（如果将其关闭，请重新打开它并转到DReplay.exe所在的文件夹），并使用replay参数运行DReplay.exe ，并提供带有-的中间文件的生成位置。 d参数。还提供–o参数，以便将重播活动保存到充当DReplay客户端的每个服务器中的结果跟踪文件中，这将允许对那些结果进行分析。最后，为DReplay客户端提供–w参数，并用逗号分隔客户端服务器名称：

注意：如果您在与被选为控制器的计算机（运行“分布式重播控制器”的计算机）不同的计算机上运行，则还需要提供带有-m参数的控制器名称。

Take attention to the Pass rate. This can show immediately the success rate of the Replay tasks and can give you an idea if you will have problems during the database migration.

注意通过率 。这样可以立即显示Replay任务的成功率，并且可以让您知道在数据库迁移期间是否会遇到问题。

查看重播结果 (Review the Replay Results)

As mentioned in the previous step we ran the Replay with –o parameter so the outcome of the operation it is saved in a result trace file named ReplayResult.trc and located in each Distributed Replay Client. You can find the file in C:\Program Files (x86)\Microsoft SQL Server\<MSSQL version>\Tools\DReplayClient\ResultDir if you used the default path during the installation. Otherwise search in the path you provided for the Distributed Replay Client installation.

如上一步所述，我们使用–o参数运行Replay，因此将其操作结果保存在名为ReplayResult.trc的结果跟踪文件中，并位于每个Distributed Replay Client中。如果在安装过程中使用了默认路径，则可以在C：\ Program Files（x86）\ Microsoft SQL Server \ <MSSQL版本> \ Tools \ DReplayClient \ ResultDir中找到该文件。否则，在您提供的“分布式重放客户端”安装路径中搜索。

Open the ReplayResult.trc and analyze it. You should search for errors and performance issues (duration column for example) and try to solve any issue that is reported. The quantity of errors should match the Pass rate value returned during the Replay phase.

打开ReplayResult.trc并进行分析。您应该搜索错误和性能问题（例如工期列），并尝试解决所报告的任何问题。错误数量应与重播阶段返回的通过率值匹配。

You have now important information to be used for your migration project. You can repeat this process how many times you want until you reach the desired results.

现在，您已经拥有用于迁移项目的重要信息。您可以重复此过程几次，直到达到所需的结果。

删除分布式重播 (Removing Distributed Replay)

Microsoft SQL Server Distributed Replay can be uninstalled by using Add or Remove Programs feature following these steps:

可以通过以下步骤使用“ 添加或删除程序”功能来卸载Microsoft SQL Server分布式重播：

Go to Control Panel and open Add or Remove Programs.

转到“控制面板”，然后打开“ 添加或删除程序” 。
Find the respective Microsoft SQL Server instance version and right-click on it and then click on Uninstall/Change:

找到相应的Microsoft SQL Server实例版本，然后右键单击它，然后单击“ 卸载/更改” ：
Chose Remove on the next pop-up window:

在下一个弹出窗口中选择“ 删除 ”：
Select the desired SQL Server instance and click on Next >:

选择所需SQL Server实例，然后单击下一步>：
Check the Distributed Replay Controller option (if you have installed the Distributed Replay Client check this feature also) and click on Next >:

选中Distributed Replay Controller选项（如果您已经安装了Distributed Replay Client，请选中此功能），然后单击Next> ：
Review the features to be removed and press on Remove button to proceed with the removal:

查看要删除的功能，然后按“ 删除”按钮以继续进行删除：
Let the process run and press Close when done:

让进程运行，完成后按“ 关闭” ：
Do the same for all clients if you also want to remove them. The only difference is that you will have only the Distributed Replay Client feature to remove::

如果还要删除所有客户端，请执行相同的操作。唯一的区别是您将只有Distributed Replay Client功能要删除：
Confirm in the servers that the Distributed Replay services were removed and the respective installation folders are empty. With that you just finished the uninstallation process.

在服务器中确认已删除“ 分布式重播”服务，并且相应的安装文件夹为空。这样，您就完成了卸载过程。