In the last issue, I told you about SQL Server 2000's new capability that lets you install multiple instances of SQL Server on one machine. This month, I show you how to keep track of your installed instances, and I give you a few warnings.

Who Are You?

SQL Server 2000 ships with a great new property function called ServerProperty(). It works like most of the other property functions that Microsoft introduced in SQL Server 7.0. SQL Server 2000 Books Online (BOL) lists all the function's possible parameters, but the ones relevant to this discussion are the properties InstanceName and ServerName.

The following command will return the name of your instance:

                              SELECT ServerProperty('InstanceName')                              

If you're connected to the default instance, you'll get NULL. (If the default instance isn't SQL Server 2000, the ServerProperty function is unavailable.)

Specifying the ServerName parameter in the following command will return the name of your instance, preceded by the name of your server, in the Servername \InstanceName notation.

                              SELECT ServerProperty('ServerName')                              

If you're connected to the default instance, you'll get only the server name. Other ServerProperty() parameters let you specify which licensing mode you're running under, which service pack you're using, and what the OS process identifier (PID) is. This last property can be important if you need to access one instance through the Task Manager. The process image name that the Task Manager displays is sqlservr.exe for all instances, but the column labeled PID lets you distinguish among multiple instances.

Where Are You?

Each SQL Server instance has its own path for executable program files and its own default location for data files. If you have more than a few instances, remembering the locations for each instance can be quite a chore. Fortunately, a tool in the Windows NT 4.0 Resource Kit can help you. If you know the name of the Registry key that stores the information about each instance, you can use the resource kit called reg.exe to discover the file path names. To find the program file path, you can type the following code in a command window, all on one line, replacing InstanceName with the appropriate instance:

                              REG QUERY "HKLM\Software\Microsoft\Microsoft SQL Server\InstanceName\Setup\SQLPath"                              

(Or, if you're an administrator with Registry access, you could simply look directly in the appropriate place in the Registry.) To find the default data file path, change SQLPath at the end of the key name to SQLDataRoot:

                              REG QUERY "HKLM\Software\Microsoft\Microsoft SQL Server\InstanceName\Setup\SQLDataRoot"

A similar tool in the Windows 2000 Resource Kit is called regfind.exe. Check Resource Kit documentation for that tool's syntax details.

Changes to Other Tools

To support multiple instances, Performance Monitor can display objects in an instance-aware fashion. The objects for the named instances show up as MSSQL $InstanceName:ObjectName. The full list of counters is available for objects in each instance, so you can compare the same counters in different instances to monitor their use of machine resources. Figure 1 shows a partial list of objects for a SQL Server instance named BETA2ENT.

Microsoft also made internal changes to SQL Server Profiler, but these changes won't significantly affect how you use Profiler to define traces. In your trace definitions, start by selecting which SQL Server instance to trace. You can choose the ServerName for the default instance or choose a different SQL Server instance by using the ServerName\InstanceName specification. Microsoft modified all server-side events so that they return the full name of the SQL Server instance when reporting the ServerName data value.

More Installation Details

When you have multiple instances on one machine, SQL Server 2000 uses a memory-negotiation module for load balancing to equally distribute memory resources. This memory-negotiation module determines how much memory each instance gets.

One situation in which this automatic memory negotiation can be especially valuable is when you're running multiple SQL Server instances on an active/active cluster. Although both servers in the cluster are running, each uses most of the available RAM on its own machine. But what happens if a failover is necessary? After failover, both SQL Server instances will be running on the same machine, and both will try to use all the available RAM on the machine. Two processes can't each use all the memory, so both SQL Server instances (and all the users of both instances) would suffer from greatly increased paging. To avoid this outcome, the memory-negotiation module reduces the memory each instance uses, so the failed-over instance can run alongside the original instance.

Note that this automatic memory negotiation works only between instances of SQL Server 2000. If SQL Server 7.0 is also running, no explicit negotiation between the servers takes place.

What Doesn't Work?

Microsoft modified almost every component of SQL Server so it could work well with multiple instances. But even with the huge amount of rewritten code, some functions just won't work with multiple instances.

First, SQL Server 2000 doesn't support the multiprotocol, Banyan VINES, or AppleTalk network libraries (Net-Libraries) for named instances. Those Net-Libraries will work with a default instance only. SQL Server 2000 doesn't need multiprotocol for encryption because you can use Secure Sockets Layer (SSL) encryption over any Net-Library.

Second, DB-Library doesn't work with named instances because the code that looks for your server name doesn't recognize the ServerName\InstanceName specification. Microsoft has stopped enhancing DB-Library, so you can't use the isql command-line tool for connecting to a named instance—at least not by default. But you can get around that limitation by using the Client Configuration Utility to create an alias for your named instance. The alias is a one-part server name that isql will recognize. When specifying the properties of this alias, you need to specify the TCP port number. If you've installed this instance so that SQL Server can dynamically determine its port number, you can select the check box that says Dynamically determine port, as Figure 2 shows. Alternatively, you can define an alias that communicates by using named pipes, in which case you must specify the proper pipe name for the instance.

A Word of Caution

Multiple instance support is a terrific addition to SQL Server. I have one complaint, though, which I offer to you as a warning: I don't like the fact that SQL Server 2000 can have only one set of tools. I do a lot of support and troubleshooting, and sometimes the problem I'm trying to solve turns out to be a problem with the tools. I'd like to have copies of the older tools available for comparison. Also, I write quite a bit about SQL Server, and if I wrote an article about SQL Server 7.0 now, I wouldn't have the SQL Server 7.0 Query Analyzer available for capturing screen shots. Be aware that you'll lose your SQL Server 7.0 tools when you install your first SQL Server 2000 instance, either in beta or in its final form. However, the new tools have so many wonderful new features that this limitation is a small price to pay, especially because you can use the SQL Server 2000 tools with any SQL Server 7.0 server you have.