Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Testing Options with SQL Server 2000 and 2005

By:   |   Last Updated: 2006-08-18   |   Comments   |   Related Tips: More > Testing

Testing database applications for many organizations is a challenging task that is difficult to setup, automate and validate.  Luckily, more time is getting allocated into development projects to conduct more rigorous testing.  In this tip we will outline some of the realities to perform SQL Server 2000 and 2005 testing and opportunities to streamline the process.

SQL Server 2000 and 2005 offer a number of opportunities to perform application testing to include the following items:

People, Process and Technology

The reality is that people, processes and technology are needed to conduct proper testing.  Taking any one item out of the equation will not result in the expected outcome.  So be sure to impress upon your team that it is necessary to have an equal amount all of three components for success.  It is difficult for a great piece of technology to solve a difficult problem without highly talented people and a comprehensive process.

SQL Server Instances

Both SQL Server 2000 and 2005 support a single default instance and 15 named instances on the same server.  What this means is that with per CPU licensing up to 16 instances can exist on a single server without incurring any additional licensing costs.  If your testing is encapsulated at a SQL Server instance level, then up to 16 versions of the code can exist on 1 server.

With SQL Server 2005, additional instance options are available for other portions of the application, i.e. Analysis Services.

For more information visit:

SQL Server 2005 Express Edition

Another option for testing is leveraging the SQL Server 2005 Express Edition which is a free copy of the database engine and Reporting Services with limitations on the number of CPUs, memory usage, etc.  This version offers a fully featured GUI and all of the bells and whistles.  This means that this edition gives you the opportunity to perform functional testing of the application on multiple independent machines at a very reasonable cost. 

Virtual Machines

If the testing incorporates SQL Server and other components installed in Windows, then multiple SQL Server instances may be helpful, but they are not the solution because DLL's may need to be redeployed for each test or build, limiting the capabilities for parallel testing.  Leveraging the Express edition may be an option if many machines are available, but this too can quickly become unwieldy.  One recommendation is to leverage Microsoft Virtual Server or VMWare as a means to have multiple virtual instances of Windows with SQL Server, IIS, your application, etc. all residing on 1 physical server.  This results in the opportunity to have many independent and fully featured instances of Windows and with multiple code bases.


If you are familiar with other database platforms, then synonyms should be no stranger.  With SQL Server 2005, Microsoft introduced synonyms as a means to have a single name of an object reference a different underlying object in another schema or database or even on another SQL Server. 

Check out this information as a synonyms primer:

*** NOTE *** - Synonyms are not available in SQL Server 2000.


Since synonyms are not available in SQL Server 2000, one potentially viable option is to leverage VIEWS as a means to have a single name that can reference another object.

DDL Triggers

With many scripts underlying objects need to be created, altered or dropped.  Depending on how the code is written and executed, it may be difficult to determine if these commands were successful or not.  One way to fill this gap in SQL Server 2005, is by leveraging the new DDL triggers.  These can be created to notify the team as the code completes and the objects are created, altered or dropped.

Check out these tips to setup DDL triggers:

Next Steps

  • Evaluate your current SQL Server testing plans and determine gaps that need to be filled.
  • Discuss options to test your SQL Server applications with your team members
  • See if you can incorporate their feedback to determine the best means to test SQL Server applications in your environment.
  • On your next project, consider leveraging some of these options to streamline the testing process and improve the application.
  • Stay tuned for additional tips on automating testing with native and third party SQL Server tools.
  • Share your testing ideas with the community by sending an email to [email protected].  Your ideas may be featured in an upcoming tip.

Last Updated: 2006-08-18

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools