Multiple Instances | Inside Microsoft SQL Server 2000

SQL Server 2000 allows you to install multiple instances of SQL Server on a single computer. One instance of SQL Server can be referred to as the default instance, and all others must have names and are referred to as named instances. Each instance is installed separately and has its own default collation, its own system administrator password, and its own set of databases and valid users.

The ability to install multiple instances on a single machine provides benefits in several different areas. First is the area of application hosting. One site can provide machine resources and administration services for multiple companies. These might be small companies that don't have the time or resources (human or machine) to manage and maintain their own SQL Servers. They might want to hire another company to "host" their SQL Server private or public applications. Each company will need full administrative privileges over its own data. However, if each company's data were stored in a separate database on a single SQL Server, any SQL Server system administrator (sa) would be able to access any of the databases. Installing each company's data on a separate SQL Server allows each company to have full sa privileges for its own data but no access at all to any other company's data. In fact, each company might be totally unaware that other companies are using the same application server.

A second area in which multiple instances provide great benefits is server consolidation. Instead of having 10 separate machines to run 10 separate applications within a company, all the applications can be combined on one machine. With separate instances, each application can still have its own administrator and its own set of users and permissions. For the company, this means fewer boxes to administer and fewer operating system licenses required. For example, one Windows NT 4 Server, Enterprise Edition (or Windows 2000 Advanced Server) with 10 SQL Server instances costs less than 10 separate server boxes.

An added benefit to running multiple instances on one big computer is that you are more likely to take care of a more powerful computer, which will lead to greater reliability. For example, if you decide to purchase an eight-way Compaq ProLiant with 4 GB of RAM, it might cost you about $100,000. With that kind of investment, you'll probably want to set it up in its own climate-controlled data center instead of next to someone's desk. You'll meticulously maintain both the hardware and the software and only let trained engineers get near it. This means that reliability and availability will be greatly enhanced.

The third area in which multiple instances are beneficial is testing and support. Because the separate instances can be different versions of SQL Server or even the same version with different service packs installed, you can use one box for reproducing problem reports or testing bug fixes. You can verify which versions the problems can be reproduced on and which versions they can't. A support center can similarly use multiple instances to make sure that it can use the same exact version that the customer has installed.

Installing Named Instances

When you install multiple SQL Server instances on a single machine, only one instance is the "default" instance. This is the SQL Server that is accessed in the same way that SQL Servers in previous versions were accessed—by supplying the machine name as the name of the SQL Server. For example, to use the Query Analyzer to access the SQL Server on my machine called KALESSIN, I just type KALESSIN in the SQL Server text box in the Connect To SQL Server dialog box. Any other instances on the same machine will have an additional name, which must be specified along with the machine name. I can install a second SQL Server instance with its own name—for example, SQL2000. Then I must enter KALESSIN\SQL2000 in the initial text box to connect using Query Analyzer.

Your default instance can be SQL Server 6.5, SQL Server 7, or SQL Server 2000. In fact, if you're using the version switch capability (discussed shortly) to switch between SQL Server 6.5 and SQL Server 7 or between SQL Server 6.5 and SQL Server 2000, your default instance can be this switched instance. Your default instance cannot be SQL Server 6 even if it is being version-switched with a later version. If you have SQL Server 6 or an older SQL Server 4.2 installation on your machine, you cannot install SQL Server 2000 at all.

Named instances can only be SQL Server 2000. Microsoft documents that you can have a maximum of 16 instances on a single machine, but this is only the supported limit. There is nothing hardcoded into the system to prevent you from installing additional instances, as long as you have the resources on the machine to support them.

Each instance has its own separate directory for storing the server executables, and each instance's data files can be placed wherever you choose. During installation, you can specify the desired path for the program files and the data files. Each instance also has its own SQL Server Agent service. The service names for the default instance do not change—they are MSSQLServer and SQLServerAgent. For an instance named SQL2000, the services are named MSSQL$SQL2000 and SQLAGENT$SQL2000.

However, only one installation of the tools is used for all instances of SQL Server 7 and SQL Server 2000. However, you will still have your SQL Server 7 Books Online, as well as your SQL Server 7 Server Network Utility available. In addition, there is only one Search Service, one Distributed Transaction Coordinator (DTC) service, one copy of English Query, one copy of the development libraries, and one copy of Microsoft SQL Server 2000 Analysis Services (formerly OLAP Services).

Named Instance Server Connectivity

I mentioned earlier in this chapter that the default Net-Libraries for SQL Server 2000 are Named Pipes, Shared Memory, and TCP/IP. Of the additional protocols that can be added, Multiprotocol, Banyan VINES, and AppleTalk are not supported for named instances. The default instance listens on a pipe named \\.\pipe\sql\query and on TCP port number 1433. A named instance listens on a pipe named \\.\pipe\MSSQL$<instance name>\sql\query, so for my SQL2000 instance, it is \\.\pipe\MSSQL$SQL2000\sql\query.

Determining which TCP port to use for a named instance is a little more problematic. Microsoft reserved port number 1433 but was unable to reserve additional ports. During setup, you can choose any port number, but you should be aware that other services from other vendors might conflict with the port number you choose. To avoid problems, the setup program allows you to specify 0 for the port number, which means that every time the SQL Server starts, it finds an unused port number to use. You can see what port has been chosen by using the Server Network Utility, selecting the name of the instance, and then displaying the properties for TCP/IP.

Allowing multiple instances of SQL Server to run on a single machine requires a Listener service to determine what instances are actually running on a machine. The new Listener service runs on UDP port 1434 but doesn't have an actual service name on the machine. This service exposes which SQL Server instances are installed, their network addresses, and so forth, for use by SQL Server client tools. At server startup, a component of every SQL Server instance checks whether anyone is listening on UDP port 1434. If no other instance is listening, it starts listening and becomes the listener service for the machine.

When a client asks to connect to a machine, the tool being used communicates with the Listener service server, asking for information about all the instances installed, their addresses, whether they are a cluster, and so forth. For each instance, the Listener service tells the client computer which server Net-Libraries and network addresses the instance is listening on. After the client computer receives this report, it chooses a Net-Library that is enabled on both the application computer and on the instance of SQL Server, and it makes a connection to the address listed for that Net-Library in the packet.

CAUTION
Because you can only have one set of client tools installed on a machine, as soon as you install an instance of SQL Server 2000, you lose any SQL Server 7 tools you had on your machine, except for Books Online and the Server Network Utility. If one of the reasons you're using multiple instances is to have multiple versions available for testing purposes, you will only be able to test the SQL Servers. If you're doing troubleshooting and think the problem might be with the tools themselves, you're out of luck. You can't test using a different version of the tools on the same machine.