Successful Deployment Strategies - Professional SQL Server 2005 Performance Tuning - page 529

There are many ways to load test the hardware. Some involve the use of enterprise-class load-testing
applications such as Mercury Interactive, and others use simpler methods such as the SQL Server Pro-
filer playback feature. Whatever may be the method, what is important is to replicate a true production
scenario so that any bottlenecks or performance limitations are clearly visible and can be rectified prior
to deployment.
Load testing an enterprise application involves many tiers such as web servers, application servers, and
database servers. Prior to beginning load testing, it is essential that all software is installed using the con-
figuration intended to be used in a production scenario. Often enterprise load-testing software requires
that specific load-testing scripts be created to replicate actual user interactions. Take, for example, a call
center application that is going to use SQL Server 2005 as a database and is going to be used daily by
10,000 users on average. What might be some of the load-testing scripts that need to be created? Typ-
ically, call center users constantly query the database for information pertaining to customers, orders,
or products. They would also perform operations that update customer information such as addresses,
names, and telephone numbers as well as enter new orders and activities. All these operations can be
recorded as scripts and then replayed while simulating thousands of users. What is important here is to
get the correct mix of transactions. This information is usually gathered by either monitoring user interac-
tion or through business analysts who have a sound understanding of the different ways the application
is used.
SQL Server 2005 also provides some limited ways of performing load testing, but these are usually lim-
ited to the database servers. One such method involves the use of the query playback feature. This is
especially useful when an application that has already been deployed is being moved to a new set of
servers, including new database servers. You can capture queries using SQL Server Profiler on the exist-
ing hardware and then replay them back in the same order on the new hardware. This would give a
sense of how well the new hardware manages to withstand the load as well as show any performance
gains that were achieved.
Best Practice
Load testing is essential to knowing the limits of your production system.
Managing Change
Change management is one aspect of deployment that most people tend to forget. A production envi-
ronment is usually a finely tuned environment that is very sensitive to change. In most real-life scenarios
database administrators are reluctant to make changes to production environments unless those changes,
have been thoroughly tested. The reality, however, is that changes do happen and sometimes these
changes go unnoticed until problems start creeping up on some parts of the application. This is the rea-
son why changes have to be managed and duly recorded. Changes in a database environment can be
of various sorts: Database schema changes, stored procedure code changes, database hardware changes
and so on. What is important here is to ensure that whatever the change may be, there is always a plan in
place to back out of the changes if things don't go as expected. For code-related changes, it is important to
ensure that version control is in place. This can easily be accomplished through the use of software such
as Microsoft Visual Source Safe. Visual Source Safe allows users to maintain multiple versions of the same
code and also promotes collaboration and sharing through the process of checking out and checking in
changes. Another great tool to use is the Visual Studio Team Edition for Database Professionals. This