SQL Testing Options with SQL Server 2000 and 2005

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.

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.

*** 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.