I recently did some drive upgrades in my personal laptops and as a result I had a couple of SSD drives sitting around that I wanted to make use of. A few weeks ago I purchased a new OCZ Agility 3 240GB SATA III SSD from Newegg when it was on sale and at the same time I also purchased two MassCool USB 3 external enclosures to make use of the two 120GB OCZ Vertex 2 SSD’s that I would have sitting around. The cost for the MassCool enclosures was only $14.99 so I figured it would be a good way to reuse the SSD’s at the time. When the package from Newegg arrived, I immediately installed the SSD into one of my personal machines and then later I went about testing the performance of the USB 3 external enclosures with the older SSD’s. Initially the performance was impressive, and I posted a few tweets that attracted some attention and a few people requested that I blog my results.

To make this blog post something worth looking at, I ran a number of additional tests over the last few days using SQLIO and a varying configuration of drives with external enclosures. Specifically I tested two different USB 3 2.5” SATA enclosures, a separate eSATA 2.5” SATA external enclosure, as well as two USB 3 HDD’s that I already owned to get some performance information from each of the setups.

The external USB 3.0 HDD’s being tested are as follows:

The external enclosures being tested in these tests are as follows:

The SSD’s used with the external enclosures listed above are:

At the time that I bought the 120GB drive I paid close to $2.42 per megabyte for it, and at the time this was a good deal. When I purchased the 240GB SSD I paid right around $1.08 per megabyte, showing how much the prices have decreased nearly 3 years later. I happen to own a number of OCZ drives at home and they are one of my favorite brands personally because of the overall reliability I’ve had with them the last three years as well as the performance to cost ratio I’ve experienced. Initially I was just testing the MassCool USB 3 enclosures that I purchased, and I was incredibly happy with the performance that I had from them. However, after tweeting about the results, my friend Jose Chinchilla (Blog | Twitter) mentioned that I should also try out eSATA because it performed significantly better in his own tests. Based on this tweet, I set out to my local MicroCenter computer store to purchase a eSATA enclosure and while I was buying it, one of the sales representatives, a guy named Chris, approached me and asked what I was planning to do with the enclosure because USB 3.0 should be faster for SSD’s. The numbers he quoted didn’t match my previous testing, so he made a recommendation for a specific USB 3.0 enclosure, and I figured, for $20 it was worth testing, so I bought an extra Hornettek Panther USB 3.0 device for comparison testing along with the Vantec NexStar CX eSATA enclosure I had already selected.

For the tests, I ran a short set of tests using SQLIO that I previously blogged about on my blog post about the Powershell parser for SQLIO output. The results from the tests are below:

The two external USB 3.0 HDD drives had very similar performance characteristics, and they beat their USB 2.0 counterparts performance wise significantly. I’ve been incredibly happy with my USB 3.0 HDDs overall for the last few years and I only purchase USB 3.0 HDD drives based on my performance tests a few years ago. However, when compared with the USB 3.0 external enclosures with the SSD’s, the performance difference is quite significant. One of the problems I’ve had lately is being able to fit all of the virtual machine hard drives, VHD’s for Hyper-V when using my dual boot Hyper-V host VHD, as well as my original VMware Workstation VMDK’s for classes, and even the VirtualBox virtual disk images (VDI) from my blog series on building a completely free playground. Using the SSD’s with USB 3.0 definitely makes storing my virtual hard disk files on an external array much more feasible performance wise, and my only real limitation from testing is the size of the external device.

However, the performance of the eSATA external enclosure is incredibly better, with the side trade off that I can only have 1 of them attached to my laptops at a time, and for my personal laptop, the fact that the eSATA enclosure requires not only the eSATA port for throughput, but also the additional USB 2.0 port for power really limits what I can do as far as having multiple disks connected to the laptop. To be honest, this is something that I can live with given that I also have swapped out my CD/DVD ROM bay for a replacement New Mode US second HDD conversion. This means that I can have two 240GB OCZ SSD’s in my E5420, which is a similar configuration to the dual 256GB Dell SSD configuration in my Precision M6500 for work, while being able to move VM images between the two machines using my older 120GB SSD’s with fantastic performance.

If you are looking at really high performance external hard disk configurations for scalability, I would highly recommend looking at either eSATA with an extra SSD drive or if you need multiple devices and you have USB 3.0, consider going that route. Either will outperform your existing options significantly. For the time being, I am going to stick with one eSATA device, which is compatible with both of my laptops, and one USB 3.0 device, which only works with my M6500 at USB 3.0 speeds. My E5420 only has USB 2.0 onboard, so it makes more sense for me to stick with eSATA for the main shared drive.

Categories:
Benchmarking | Hardware | Building a Test Environment | General

In the first post in this blog series on using SQL Server 2012 Distributed Replay, Installing and Configuring SQL Server 2012 Distributed Replay, we looked at how to configure a Distributed Replay environment using multiple clients and a dedicated replay controller. In this post we’ll actually make use of the previously configured servers to perform a distributed replay using a random workload that has been generated against the AdventureWorks2008R2 database installed on our Replay SQL Server.

Collecting the Replay Trace Data

For the purposes of generating a random workload against AdventureWorks2008R2, I created a workload generator that can be found on my blog post The AdventureWorks2008R2 Books Online Random Workload Generator. I used this with 2 different PowerShell Windows from SQL2012-DRU1 and SQL2012-DRU2 to run a random workload across multiple sessions against the SQL2012-DB1 server. To capture the trace data required for performing the replay, SQL Server Profiler was used along with the TSQL_Replay template to create the capture.

For production systems, the best way to go about capturing a Replay Trace is to script the trace definition to a file, and then create the trace as a server side trace that is writing to a trace file on local disks for the server. This has a significantly lower impact that tracing directly from Profiler, which uses the rowset provider for Trace. With the replay trace running, and the workload generating events I waited for the trace to collect around 80000 rows of data and then shutdown the trace so that I could access the trace file to copy it from the SQL2012-DB1 server to the SQL2012-DRU server where the Distributed Replay Controller is installed.

Preprocessing the Trace File(s)

At the point that I went to perform the preprocessing of the trace file for replay, I realized a difference in my environment using multiple servers to build this blog series versus my original setup using a single server for learning how to use Distributed Replay. In order to preprocess the trace file for replay, you have to have the Management Tools Basic installed on the server that will be used for preprocessing the trace data. If you have been following this blog series to learn how to use Distributed Replay, you will need to run Setup on the SQL2012-DRU server to add this feature before it can be used for pre-processing the trace file. This is necessary to administer Distributed Replay.

Once the Management Tools Basic have been installed the server will have to be restarted and then it is possible to make use of the DReplay.Exe executable to administer the Distributed Replay components on the controller server. The DReplay executable has multiple options that can be discovered by using a –? from the command line as follows:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn>dreplay -?
Info DReplay    Usage:
DReplay.exe {preprocess|replay|status|cancel} [options] [-?]}

Verbs:
preprocess Apply filters and prepare trace data for intermediate file on controller.
replay     Transfer the dispatch files to the clients, launch and synchronize replay.
status     Query and display the current status of the controller.
cancel     Cancel the current operation on the controller.
-?         Display the command syntax summary.

Options:
dreplay preprocess [-m controller] -i input_trace_file -d controller_working_dir [-c config_file] [-f status_interval]
dreplay replay [-m controller] -d controller_working_dir [-o] [-s target_server] -w clients [-c config_file] [-f status_interval]
dreplay status [-m controller] [-f status_interval]
dreplay cancel [-m controller] [-q]
Run dreplay <verb> -? for detailed help on each verb.

To perform the preprocessing, you will need to do a couple of different steps. The first thing you need to do is edit any options that you want to set for the pre-processing by editing the DReplay.Exe.Preproces.config file in the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn path on the server. There are two configuration files for DReplay.Exe as highlighted below. At this time make sure that you are only editing the Preprocess.config file.

The DReplay.Exe.Preproces.config file contains a schema defined XML document that controls the configuration of the preprocessing. In general the options set for preprocessing should not need to be changed but if you want to include system sessions as a part of the replay, you can change the options in the XML, which is listed below.

<?xml version="1.0" encoding="utf-8"?>
<Options>
    <PreprocessModifiers>
        <IncSystemSession>No</IncSystemSession>
        <MaxIdleTime>-1</MaxIdleTime>
    </PreprocessModifiers>
</Options>

To preprocess the trace data, open a new command prompt window and change directories to the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn path. The trace file has been copied onto the SQL2012-DRU server as C:\DReplay\SQL2012_ReplayTrace.trc. To preprocess this file first start the “SQL Server Distributed Replay Controller” service by using NET START:

NET START "SQL Server Distributed Replay Controller"

Then execute the following command from within the Binn path to actually preprocess the trace file and output:

dreplay preprocess -i "C:\DReplay\SQL2012_ReplayTrace.trc" -d "C:\DReplay"

This will process the trace file and output the working files for performing the Distributed Replay to the C:\DReplay path. Below is a screenshot of the full window for preprocessing the trace file.

Note: The dreplay executable can be called from any path within the server because the Binn path is a part of the Path Environmental variables. However, the executable has to be called from within the Binn folder to access the necessary .config files and .xsd schema files for the configuration. If you want to be able to run this executable from another location on the server, you will need to copy the .config and .xsd files out of the Binn folder to the folder that you want to be able to run dreplay within for it to work.

Performing the Replay

The first step in performing the replay is to start the “SQL Server Distributed Replay Client” service on each of the replay clients using NET START.

NET START "SQL Server Distributed Replay Client"

You will want to verify that each of the clients was able to successfully connect to the controller in the logs as shown in the previous post in this series. Once this has been done, your environment is almost ready for replay. For the purposes of this blog series, a SELECT only workload has been generated for replay against AdventureWorks2008R2. However, in most environments you won’t have a SELECT only workload, so you will have to plan for and prepare your replay environment using a BACKUP/RESTORE of the production database from a point within the captured workload so that the database can be replayed against without having problems associated with Primary Key constraint violations during the replay.

If you want to change any of the parameters associated with the replay operation, you can edit the DReplay.Exe.Replay.config file in the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn path. The default contents of the configuration file are shown below:

<?xml version="1.0" encoding="utf-8"?>
<Options>
    <ReplayOptions>
        <Server></Server>
        <SequencingMode>stress</SequencingMode>
        <ConnectTimeScale>100</ConnectTimeScale>
        <ThinkTimeScale>100</ThinkTimeScale>
        <HealthmonInterval>60</HealthmonInterval>
        <QueryTimeout>3600</QueryTimeout>
        <ThreadsPerClient>255</ThreadsPerClient>
        <EnableConnectionPooling>No</EnableConnectionPooling>
        <StressScaleGranularity>SPID</StressScaleGranularity>
    </ReplayOptions>
    <OutputOptions>
        <ResultTrace>
            <RecordRowCount>Yes</RecordRowCount>
            <RecordResultSet>No</RecordResultSet>
        </ResultTrace>
    </OutputOptions>
</Options>

Before performing the actual replay, make sure that the account being used to run the SQL Server Distributed Replay Client service has been granted appropriate access to the target SQL Server and database to be able to perform the replay operations. Once this has been done replay can be performed using the command line options for DReplay.Exe by providing the appropriate switches, or you can alternately provide the –c command line switch to specify the configuration file that should be used for performing the replay. If you change any of the default values listed above in the DReplay.Exe.Replay.config file, you will need to specify the –c command line switch for those to take effect. To perform a replay with the defaults, the following command line execution can be run:

dreplay replay -s "SQL2012-DB1" -d "C:\DReplay" -w "SQL2012-DRU1, SQL2012-DRU2"

Once this is executed, the Distributed Replay Controller will take read in the preprocessed replay file, and then synchronize the replay across all of the clients specified with the –w command line parameter. While the replay operation occurs, the command window for the controller will output periodic updates about the current status of the replay process.

The frequency of the status updates can be controlled using the –f command line switch to specify the number of seconds between each of the updates. Each of the status updates will provide information about each of the clients including the total number of events that have been replayed, the success rate of the replay operations per client, as well as an estimate for the total amount of time remaining to complete the replay operation. When the replay completes the total elapsed time and pass rate for the events is output.

In the next and final post in this series, we’ll look at some of the common problems with using Distributed Replay and how to resolve them, including manually configuring the Controller and add additional Client Service accounts to the environment after Setup has been completed.

Categories:
Distributed Replay | Benchmarking | Database Administration | SQL Server 2012 | SQL Server Denali

This blog post is the first in a series of posts that will cover how to install, configure, and use the new Distributed Replay Utility in SQL Server 2012. The distributed replay utility can be used to assess the impact of changes and upgrades by replaying workload activity against a test environment based on a replay trace captured from the current production SQL Server environment. Prior to SQL Server 2012, two features existed that provided replay capabilities for performance benchmarking and stress testing SQL Server based on a replay workload; SQL Server Profiler trace replay and RML Utilities. Unlike these two tools, Distributed Replay is not limited to replaying events from a single computer, which allows you to drive higher loads against the test environment to better simulate mission-critical workloads by driving load from multiple clients concurrently.

This blog post will show how to configure a Distributed Replay Controller, using a dedicated server named SQL2012-DRU, and two Distributed Replay Clients using dedicated servers named SQL2012-DRU1 and SQL2012-DRU2. For the purposes of performing an actual replay using Distributed Replay in another post, a separate server named SQL2012-DB1 with the Database Engine Services and the AdventureWorks2008R2 database installed on it will be used.

Create Replay Service Accounts in Active Directory

The first step in installing and configuring Distributed Replay is to setup the necessary Active Directory service accounts to run the Distributed Replay Client and Distributed Replay Controller under. For the purposes of service isolation, the Distributed Replay Clients and Distributed Replay Controller will make use of separate Active Directory service Accounts. For the purposes of this blog post, two different accounts will be created. The Distributed Replay Client will use the SQLskillsDemos\DRUClientSvcAcct as shown in the below screenshots.

The Distributed Replay Controller will use the SQLskillsDemos\DRUCtrlSvcAcct as shown in the below screenshots:

Install the Replay Controller

With the service accounts setup in Active Directory, we can begin the installation of our Distributed Replay Controller. To do this run SQL Server 2012 Setup on the controller and perform a SQL Server Feature Installation. On the Feature Selection page select the Distributed Replay Client feature.

On the Server Configuration page set the Service Account credentials to the previously created Active Directory account for the client; in this case SQLSKILLSDEMOS\DRUCtrlSvcAcct.

On the Distributed Replay Controller page, click the Add button and search directory services for the client service account previous created in Active Directory; in this case SQLSKILLSDEMOS\DRUClientSvcAcct.

Finish the installation and then close SQL Server Setup.

Install the Replay Clients

With the service accounts setup in Active Directory, we can begin the installation of our Distributed Replay Client machines. To do this run SQL Server 2012 Setup on each of the clients to be configured and perform a SQL Server Feature Installation. On the Feature Selection page select the Distributed Replay Client feature.

On the Server Configuration page set the Service Account credentials to the previously created Active Directory account for the client. In this case SQLSKILLSDEMOS\DRUClientSvcAcct.

On the Distributed Replay Client page, type in the name of the server that you previously installed the Distributed Replay Controller service on in the Controller Name box.

Finish the installation and then close SQL Server Setup.

Configure the Windows Firewall for the Services

In order for the Distributed Replay Clients (SQL2012-DRU1 and SQL2012-DRU2) to connect and register with the Distributed Replay Controller, in this case SQL2012-DRU, the firewall must be configured to allow inbound connections for the DReplayClient.exe application on each of the client machines, as well as for the DReplayController.exe application on the Distributed Replay Controller. To do this, firewall rules need to be added through the use of the Windows Firewall with Advanced Security snapin, available through Start | Administrative Tools | Windows Firewall with Advanced Security, or through the use of NETSH command line statement executions (detailed later in this blog post).

To add a new firewall rule for the DReplay Client using the Windows Firewall with Advanced Security snapin, Right-Click on Inbound Rules and click on the New Rule menu item. Specify Program for the Rule Type and click Next.

On the Program page, click Browse and navigate to C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient and select the DReplayClient.exe executable.

On the Action page select the option Allow the connection and then click Next.

On the Profile page, select the appropriate network profiles/locations for the environment, generally Domain would be sufficient for most corporate domains, and then click Next.

Finally provide a Name for the rule and click Finish to make the firewall changes.

To setup the firewall rule for the Distributed Replay Controller, follow the same steps but instead of selecting the DReplayClient folder and DReplayClient.exe application, select the DReplayController folder and DReplayController.exe application

These rules can also be added using NETSH from the command line using the following commands:

NETSH advfirewall firewall add rule name="Allow DReplay Client" dir=in program="C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\DReplayClient.exe" action=allow

NETSH advfirewall firewall add rule name="Allow DReplay Controller" dir=in program="C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\DReplayController.exe" action=allow

Start the Services and Verify Client Registrations

The final step in the process of installing and configuring SQL Server 2012 Distributed Replay is to start the services and verify that the Distributed Replay Clients register with the Distributed Replay Controller correctly. To do this start the “Distributed Replay Controller” service from the Services.msc snap-in or from the command line using NET START on the controller server. Then start the “Distributed Replay Client” service on from the Services.msc snap-in or from the command line using NET START on each of the client machines. As each of the client services starts, verify that the client was able to successfully register itself with the controller by reading the log file, which is located in the following path:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\Log

What you want to see in the DReplay Client Log file is:

2011-11-09 00:41:09:162 OPERATIONAL [Client Service]      Microsoft SQL Server Distributed Replay Client - 11.0.1440.19.
2011-11-09 00:41:09:162 OPERATIONAL [Client Service]      (c) Microsoft Corporation.
2011-11-09 00:41:09:162 OPERATIONAL [Client Service]      All rights reserved.
2011-11-09 00:41:09:178 OPERATIONAL [Client Service]      Current edition is: [Enterprise Evaluation Edition].
2011-11-09 00:41:09:178 OPERATIONAL [Common]              Initializing dump support.
2011-11-09 00:41:09:178 OPERATIONAL [Common]              Dump support is ready.
2011-11-09 00:41:09:193 OPERATIONAL [Client Service]      Windows service "Microsoft SQL Server Distributed Replay Client" has started under service account "SQLSKILLSDEMOS\DRUClientSvcAcct". Process ID is 2928.
2011-11-09 00:41:09:193 OPERATIONAL [Client Service]      Time Zone: Eastern Standard Time.
2011-11-09 00:41:09:193 OPERATIONAL [Client Service]      Controller name is "SQL2012-DRU".
2011-11-09 00:41:09:193 OPERATIONAL [Client Service]      Working directory is "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\WorkingDir".
2011-11-09 00:41:09:193 OPERATIONAL [Client Service]      Result directory is "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir".
2011-11-09 00:41:09:193 OPERATIONAL [Client Service]      Heartbeat Frequency(ms): 3000
2011-11-09 00:41:09:193 OPERATIONAL [Client Service]      Heartbeats Before Timeout: 3
2011-11-09 00:41:09:367 OPERATIONAL [Client Service]      Registered with controller "SQL2012-DRU".

What you don’t want to see in the DReplay Client Log file is:

2011-11-09 00:40:50:207 OPERATIONAL [Client Service]      Microsoft SQL Server Distributed Replay Client - 11.0.1440.19.
2011-11-09 00:40:50:223 OPERATIONAL [Client Service]      (c) Microsoft Corporation.
2011-11-09 00:40:50:223 OPERATIONAL [Client Service]      All rights reserved.
2011-11-09 00:40:50:223 OPERATIONAL [Client Service]      Current edition is: [Enterprise Evaluation Edition].
2011-11-09 00:40:50:223 OPERATIONAL [Common]              Initializing dump support.
2011-11-09 00:40:50:239 OPERATIONAL [Common]              Dump support is ready.
2011-11-09 00:40:50:239 OPERATIONAL [Client Service]      Windows service "Microsoft SQL Server Distributed Replay Client" has started under service account "SQLSKILLSDEMOS\DRUClientSvcAcct". Process ID is 776.
2011-11-09 00:40:50:239 OPERATIONAL [Client Service]      Time Zone: Eastern Standard Time.
2011-11-09 00:40:50:239 OPERATIONAL [Client Service]      Controller name is "SQL2012-DRU".
2011-11-09 00:40:50:239 OPERATIONAL [Client Service]      Working directory is "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\WorkingDir".
2011-11-09 00:40:50:239 OPERATIONAL [Client Service]      Result directory is "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir".
2011-11-09 00:40:50:239 OPERATIONAL [Client Service]      Heartbeat Frequency(ms): 3000
2011-11-09 00:40:50:239 OPERATIONAL [Client Service]      Heartbeats Before Timeout: 3
2011-11-09 00:40:50:476 CRITICAL     [Client Service]     [0xC8100013] Failed to register with controller SQL2012-DRU.

If the client fails to register, and the controller name is correct, you need to verify first that the firewall rules have been established for the client to allow the appropriate inbound connections from the controller service and that the firewall rules have been established for the controller to allow the appropriate inbound connections from the client services. If this all checks out, you need to go troubleshoot the permissions and setup of the services in the environment, which I’ll cover in a later post.

Categories:
Benchmarking | SQL Server 2012 | SQL Server Denali | Distributed Replay