SQL Load Testing

Shai Raiten — Thu, 24 Jul 2008 16:06:58 GMT

SQL Load Testing

Introduction - SQL Load Test

This tool generates a Visual Studio 2005/2008 Unit Test from a SQL Server Profiler trace. The tool extracts all the SQL statements and stored procedure calls from the trace and turns them into a single Visual Studio Unit Test, which can then be configured as a Visual Studio Load Test.
The tool does not interact with the database itself when it generates the test code and so it can be used “offline”.

Requirements:

SQL Server 2005 SDK and the SQL Server 2005 Management Tools are installed.
Visual Studio 2005/2008 Team Edition for Software Testers.

Demo - Create SQL Load Test

Download SQL Load Test and copy the packaged assemblies to a convenient location.

For this Demo I used "The Beer House" CMS & e-commerce site.
I installed SQL Server and "The Beer House" on my computer.

First let's create new SQL profiler trace file (Template)

The trace passed to the parser must contain the following columns, each required event class must include these columns:

EventClass
TextData
DatabaseName - if this is omitted then the trace is assumed to be all for the same database
SPID

After you finish configure the trace file press "Run" to start recording.

Open the "The Beer House" site and start perform the actions you like to record.

(Login etc..).

When you done with the test scenario stop profiler recording and save the trace file.

Copy the trace file into the same folder where the packaged assemblies are.

Now when you have the trace file ready use the command line tool called DbLoadTestGen to generate Visual Studio Unit Test.
Run it using a command with the following syntax:

DbLoadTestGen.exe <Scenario Name> <Trace File Name> [<Configuration File Name>]

1. Scenario Name. A simple name for the scenario. This must be a valid C# identifier as it is used in the generated code. Avoid using the name of a type or operation in the services under test.

2. Trace file name. The path to the file containing the SQL trace to be processed.

3.Configuration file name. This is optional and it is the path to the file containing the configuration information. If the file is not present a default configuration is used.
For more information read "Database Load Test Tool Usage Notes.doc"

When the code generation is done you will see two new files:

SQLTEST.cs - Unit Test
SQLTEST.stubs - Connection String

The stubs file will need to be renamed to a .cs file.
*** The connection string will need to be put into the stubs file.

Now we almost ready to run our SQL Load Test.

Just open Visual Studio Tester Edition and create new Test project, Add "SQLTEST.cs" and "SQLTEST.stubs.cs" into the project.

Edit "SQLTEST.stubs.cs" and add the connection string and save.

Before you create the Load test let's us check that everything is working properly.

From the "Test View" run the SQLTEST unit test and watch the profiler...

Working!!!
Now when everything is working you can add SQLTEST unit test into a new Load Test.

Enjoy.

Shai Raiten's Blog : Performance, CodePlex

SQL Load Testing

SQL Load Testing

Introduction - SQL Load Test

Requirements:

Demo - Create SQL Load Test