Testing SQL Server is an often-overlooked area of the software development process, because programmers primarily place testing focus on code residing in the middle tier or the client desktop, rather than the database.

However, as SQL Server databases become a more important component of applications, they cannot be left out of the developer's testing process. An important place to start, to ensure your code works the way you want it to, is with SQL Server configuration.The focus of this column is to examine issues dealing with testing SQL Server 2000 databases using SQL Server's built in language, Transact-SQL, as the primary testing tool.

Configuration: Settings and Properties

SQL Server configuration covers two areas: configuration settings and server properties. This article follows the conventions established with SQL Server 2000. While there are also configuration settings in SQL Server 6.5 and 7.0, only SQL Server 2000 has a function specifically for inspecting server properties.

Configuration settings are server-level settings that can be easily changed, and the method is the same, even though the settings are slightly different between SQL Server 7.0 and SQL Server 2000. Server properties, introduced by SQL Server 2000, are relatively permanent and do not change unless you reinstall SQL Server or add a service pack. SQL Server 2000 provides a system function for extracting server properties, whereas SQL Server 7.0 has some of the same information available in a less organized way. In this article, I will focus on techniques for extracting SQL Server 2000 configuration information.

Why is Configuration Important?

There are several good reasons for knowing an SQL Server's configuration.

First, you need to know whether the configuration on your development SQL Server matches the one on the production and test systems. That means you need to compare two SQL Server configurations to detect important differences. For example, if the development SQL Server is installed with the default case-insensitive collation and the production server is installed with case-sensitive, you may have unit-tested code that will break in production.

Second, you need to make sure that the SQL Server configuration on which you're developing is a viable one. How do you know that the configuration settings of your development SQL Server are correct, or at least in line with best practices? For example, suppose the Locks configuration setting is artificially low: you may get spurious error messages from resource-intensive queries.

Third, when you're developing code that must query an SQL Server, you may need to test how that code works under varying configuration settings and server properties. For example, you may need to test all the various editions of SQL Server (a server property), or various memory configurations (a configuration setting). Remember, we're using the term 'configuration' to cover both settings and properties. In this article, I'll take a look at ways to use Transact-SQL to extract SQL Server configuration information. Unfortunately, there is no one single place to get all that information, so you'll need more than one technique.

This article will focus on comparing configuration settings and server properties on multiple servers.

Using Enterprise Manager to Inspect Configuration

You can find a limited amount of server configuration information from the SQL Server Enterprise Manager. Just right click over a server name, choose Properties, and a tab dialog will show you quite a few, but by no means all, of an SQL Server's properties (see Figure 1.) In an effort to make SQL Server more usable and self-configuring, recent SQL Server releases are tending to hide configuration parameters, or 'knobs,' from the user, encouraging users to just take the defaults. However, that means you can't use Enterprise Manager to definitively find all of the configuration settings of an SQL Server.

Figure 1: By right-clicking over a server name in Enterprise Manager and choosing Properties, you can inspect some of the configuration settings and server properties of a given SQL Server.

There's another problem with using Enterprise Manager: it's a GUI interface, so the inspection of configuration settings and server properties is a manual process, leaving you with no real mechanism for automating the test of an SQL Server's configuration. To get a persistent record of configuration settings and server properties, you need to use Transact-SQL.

Inspecting Configuration Settings

You can use the sp_configure system stored procedure to inspect and change SQL Server configuration settings. When you run: 

EXEC sp_configure

from Query Analyzer, you may see only a limited number of settings. Make sure that the 'show advanced options' setting is 1. If it is not, you can set it with: 

EXEC
sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure

Since the default value of 'show advanced options' is 0 (off), you have to change it to see all the settings. It's a sort of "Catch 22": you have to change one of the configuration settings in order to see all the settings.

To set 'show advanced options' back, issue:

EXEC sp_configure 'show advanced options',0
RECONFIGURE
EXEC sp_configure

You can inspect the configuration settings in a more non-intrusive way by directly querying a few system tables in the master database. This can be done using the stored procedure shown in Listing 1 (adapted from code found in the sp_configure system stored procedure). The output of both is the same; a sample is shown in Figure 2.

Figure 2: A sample output of usp_GetConfigurationSettings using Query Analyzer.

Because the above stored procedure only queries the system tables, it has the advantage that it does not change any values. Also, it always reports all configuration values, unlike sp_configure. See the sidebar, 'Directly Querying the System Tables,' for a brief discussion of the disadvantages of this kind of query.

When working with configuration settings, it's important to distinguish between the configuration value and the run value. Some configuration settings, like the 'show advanced options' setting, may be changed, and you'll see the new value in the configure column, but not the run column, until you issue the RECONFIGURE command (as seen above). Other configuration settings, such as 'allow updates,' which allows changes to be made to some system tables, require RECONFIGURE WITH OVERRIDE. A third set, such as those associated with server memory settings, require you to restart the SQL Server service before they take effect.

Inspecting Server Properties

Another distinct set of SQL Server configuration information is available from the SERVERPROPERTY() function. These are not settings, because there is no command by which you can change them. You must reinstall SQL Server or install a service pack to make them change value.

You can capture these properties one value at a time, using the SERVERPROPERTY() function. For example, the following command returns the default collation for the SQL Server in question:

SELECT SERVERPROPERTY('Collation') AS 'Collation'

However, retrieving these properties one at a time is very time-consuming and error-prone. The following stored procedure, shown in Listing 2, collects all the settings into a table variable, and returns the results much like the sp_configure stored procedure does with configuration settings.

The above stored procedure uses the CAST() function to return all the results as an nvarchar(512) in order to bypass spurious warnings from SQL Server about exceeding the maximum size of a row in a table. The problem warnings stem from the fact that the SERVERPROPERTY() function returns a result with the sql_variant data type, and a sql_variant can hold up to 8000 bytes of data. But, the maximum row size in SQL Server 2000 is 8060 bytes. Even the additional column in the above table variable definition causes SQL Server to possibly use more than the maximum number of bytes and issue a warning. Since none of the information returned by SERVERPROPERTY() is ever that large, it's easier to simply cast the sql_variant result into a shorter string.

Comparing Server Configuration Settings

There are several strategies you can use to compare two server configurations.

An easy, low-tech way is to capture the output of both stored procedures to an external text file and compare the results using a text-comparison tool. For example, you could use the Query Analyzer and save the output of the stored procedure to disk, or you could use the command line tool OSQL.EXE to do the same. The following two snippets use the above stored procedures to gather output from one SQL Server.

C:\>osql -E -SSQL1 -Q"EXEC usp_GetConfigurationSettings"
-oSQL1Config.txt -w1024

C:\>osql -E -SSQL1 -Q"EXEC usp_GetServerProperties"
-oSQL1Props.txt -w1024

Although this code snippet shows the commands across several lines, you must make sure you run each command on a single line from the command window.

Another method is to capture the results of the stored procedures in tables and compare the results using Transact-SQL. For example, the following code snippet shows how to capture the configuration settings into a local SQL Server table.

IF OBJECT_ID('SQL1Configs', 'U')
  IS NOT NULL
  DROP TABLE dbo.SQL1Configs
GO
CREATE TABLE dbo.SQL1Configs
  (name sysname,
  minimum int,
  maximum int,
  config_value int,
  run_value int)
INSERT SQL1Configs
  EXEC dbo.usp_GetConfigurationSettings

The next code snippet shows the same for the output of usp_GetServerProperties.

IF OBJECT_ID('SQL1Properties', 'U')
  IS NOT NULL
  DROP TABLE dbo.SQL1Properties
GO
CREATE TABLE dbo.SQL1Properties
  (PropertyName nvarchar(30),
  PropertyValue nvarchar(512))
INSERT SQL1Properties
EXEC dbo.usp_GetServerProperties

If you transfer all the data to one server, you can query the tables and compare the results. If you set up your server as a linked server to the target server, you can directly query the local table and the remote table for differences. You can find differences by joining the tables and restricting the returning rows to just those that differ, as shown in Listing 3.

In Listing 3, both the server configuration tables have been placed in the local server's tempdb database. The query uses a full outer join because it is possible that the tables may not have the same number of rows and same values for the name column. The WHERE clause tests for differences in actual values (in the '<>' clauses) and tests for missing rows from either table by checking to see if the names of either table are NULL.

Linked servers can make the comparison much simpler. Rather than persisting the settings data in tables, you can directly query each server's system tables. Listing 4 shows a query across two linked servers.

This query assumes that both servers are SQL Server 2000 servers. Therefore, it requires only a normal join clause, because the system tables will have the same number of rows on each server. A sample output is shown in Figure 3.

Figure 3: : Sample linked server output comparing the configuration settings of two servers.

Comparing Server Properties

You can use the same techniques for comparing server properties across two servers. One hitch is that the SERVERPROPERTY() system function reports values from only the current server, so a simple linked server query using a four-part name is not possible. But you can still capture the output of server properties to external text files or to tables, using the same techniques described for comparing server configurations.

The Next Step

Once you've compared the server properties and server configurations of two servers and are satisfied that they are the same or at least close enough, the next step in testing a configuration is to determine what the correct settings are, or more importantly, what settings are suspicious. That will be the subject of my next CoDe article.