Database Reference
In-Depth Information
might have different data or even different structures. This will cause the playback mechanism to generate errors
instead of useful information. This means, to start with, you must have a database that is in Full Recovery mode so
that you can take regular full backups as well as log backups in order to restore to a specific point in time when your
testing will start.
Once you establish the ability to restore to the appropriate time, you will need to configure your query capture
mechanism—a server-side trace definition generated by Profiler, in this case. The playback mechanism will define
exactly which events you'll need to capture. You'll want to set up your capture process so that it impacts your system
as little as possible.
Next, you'll have to deal with the large amounts of data captured by the trace. Depending on how big your
system is, you may have a large number of transactions over a short period of time. All that data has to be stored and
managed, and there will be many files.
You can set up this process on a single machine; however, to really see the benefits, you'll want to set up multiple
machines to support the playback capabilities of the Distributed Replay tool. This means you'll need to have these
machines available to you as part of your testing process. Unfortunately, with all editions except Enterprise, you can
have only a single client, so take that into account as you set up your test environment.
When you have all these various parts in place, you can begin testing. Of course, this leads to a new question:
What exactly are you doing with your database testing?
A Repeatable Process
As explained in Chapter 1, performance tuning your system is an iterative process that you may have to go through on
multiple occasions to get your performance to where you need it to be and keep it there. Since businesses change over
time, so will your data distribution, your applications, your data structures, and all the code supporting it. Because of
all this, one of the most important things you can do for testing is to create a process that you can run over and
over again.
The primary reason you need to create a repeatable testing process is because you can't always be sure that the
methods outlined in the preceding chapters of this topic will work well in every situation. This doubt means you need
to be able to validate that the changes you have made have resulted in a positive improvement in performance.
If not, you need to be able to remove any changes you've made, make a new set of changes, and then repeat the tests,
repeating this process iteratively. You may find that you'll need to repeat the entire tuning cycle until you've met your
goals for this round.
Because of the iterative nature of this process, you absolutely need to concentrate on automating it as much as
possible. This is where the Distributed Replay tool comes into the picture.
Distributed Replay
The Distributed Replay tool consists of three pieces of architecture.
•
Distributed Replay Controller
: This service manages the processes of the Distributed Replay
system.
•
Distributed Replay Administrator
: This is an interface to allow you to control the Distributed
Replay Controller and the Distributed Replace process.
•
Distributed Replay Client
: This is an interface that runs on one or more machines (up to 16) to
make all the calls to your database server.
You can install all three components onto one machine; however, the ideal approach is to have the controller on
one machine and then have one or more client machines that are completely separated from the controller so that
each of these machines is handling only some of the transactions you'll be making against the test machine. Only for
the purposes of illustration, I have all the components running on a single instance.
