How to Set Up a SQL Server Stress Test Environment in 8 Steps: Lessons From the Field

Step 6: Load Initial Data

You are almost ready to start but at this time, you will still have an empty database to run your tests against. To make your test results realistic, you need to populate your database to see how the application will react in a real life context. In other words, table scans on empty tables will not alarm us, but on a 1 GB table, it will (or at least, it should).

This can be done using Data Transformation Services (DTS) in SQL Server 2000 or SQL Server 2005 Integration Services (SSIS). SSIS has a number of extra features that will help smooth this operation. (Variable data can be easily loaded using for each loops.)

Scripts like the one in step 4 also will do the trick.

Step 7: Install Monitoring

I cover this topic in my previous article on Monitoring.

Step 8: Run Your Test Using Multiple Iterations

Now you are ready to start testing. It is important to run multiple iterations using different parameter combinations but you also need to perform multiple iterations of the same scenario to avoid accidental external impact.

Documentation is a key aspect here for effective results analysis:

• Keep track of modifications you make, in the test scenarios as well as in your database structure and code.
• Log results of each iteration separately.

How you should interpret the results will be the scope of my next article.

Conclusion

Investing in a stress test solution is important and in the long run certainly money well spent. If your customer comes to you with a question like "we want to deploy 50 additional branches by the end of next week, can your database support this?", the only way you can answer is by testing the extra 50 branches using a reliable stress test environment.

The steps I showed here are the result of building my own solution when others could not decide whether to invest in a tool. It is certainly not as professional as most third party solutions and it has it limits toward scalability but at least you have an easy to setup toolkit you can use to get started.

Special thanks to the people at Gap who made me realize how fun it is to write again.