Testing Options with SQL Server 2000 and 2005

By: Jeremy Kadlec   |   Updated: 2006-08-18   |   Comments   |   Related: > Testing

Problem

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.

Solution

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:

• Multiple Instances of SQL Server
• Working with Named and Multiple Instances of SQL Server 2000
• File Locations for Default and Named Instances of SQL Server 2005

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.

Synonyms

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:

• CREATE SYNONYM (Transact-SQL)
• Using Synonyms (Database Engine)

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

Views

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:

• CREATE TRIGGER (Transact-SQL) 
• Designing DDL Triggers
• DDL Events for Use with 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.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips