Multiple Instances

SQL Server users have long desired the capability to run multiple installations of SQL Server on one computer. In SQL Server 2000, that capability has become a reality with a feature called multiple instances. This functionality provides benefits across a wide spectrum of SQL Server sites but will be most beneficial in three main areas: application hosting, server consolidation, and technical support.

In the evolving arena of application hosting, one site can provide machine resources and administration services for multiple companies. Small companies that don't have the time or resources to manage and maintain their SQL Server implementations might hire another company to host their SQL Server applications. In such a scenario, each company needs full administrative privileges over its data. However, if the host stores each company's data in a separate database on one SQL Server instance, any SQL Server systems administrator (sa) can access any database. Installing each company's data on a separate SQL Server instance gives each company full sa privileges for its own data without access to any other company's data. Also, by separately tuning each server for memory, CPU, or other resource utilization, the hosting company could charge each hosted company based on use of CPU and disk space resources.

A second area in which multiple instances provide great benefit is server consolidation. Instead of having 10 machines to run 10 applications, a company can run all applications on one machine. With separate SQL Server instances, each application can still have its own administrator and its own users and permissions. However, the company will have fewer boxes to administer and will need fewer OS licenses. For example, one Windows NT Server, Enterprise Edition (or Windows 2000 Advanced Server) with 10 SQL Server instances costs less than 10 server boxes. Microsoft hasn't determined the licensing requirements for multiple instances, but you won't pay more for your OS license, no matter how many SQL Server instances are running on it.

An added benefit of running multiple instances on one big computer is that companies protect their investment in a more powerful computer, which leads to greater reliability. For example, an 8-way Compaq ProLiant with 4GB of RAM, capable of running 10 concurrent instances, might cost $100,000. With that kind of investment, you'd probably set up this machine in a climate-controlled data center instead of next to someone's desk. You'd meticulously maintain the hardware and software and let only trained engineers work on them. Thus, you greatly enhance reliability and availability.

The third benefit of multiple instances is for testing and support centers. Because the separate instances can be different releases of SQL Server or the same release with different service packs installed, you can use one box for reproducing problem reports or testing bug fixes. You can verify which releases can reproduce the problems and which releases can't. Support center personnel can similarly use multiple instances to make sure they have the same configuration the customer has installed.

Be aware that using named instances carries a price: Each named instance requires extra overhead. However, the benefits can far outweigh the cost.

Named and Default Instances

When you have multiple SQL Server instances on one machine, only one instance will be the default instance. You access this SQL Server instance the same way you've always accessed SQL Server—by supplying the machine name as the name of the SQL Server instance. For example, to access the only SQL Server instance installed on my machine, which is called OGION, I just type OGION into the SQL Server text box in the Connect to SQL Server dialog box. Any other instances I might install on the same machine will have an additional name, which I need to specify with the machine name. If I install a second SQL Server instance of SQL Server 2000 with the name SHILOH, for example, I need to enter OGIONSHILOH in the initial text box to connect through Query Analyzer.

If you install SQL Server 7.0 on a machine that already has SQL 6.5 or 6.0, you don't necessarily lose your previous installation. You can install SQL Server 7.0 on the machine with an existing version, but you can run only one of the two releases at any time. A utility accessible from the Start menu lets you switch between the two. If you're using this version-switch capability to switch between SQL Server 7.0 and SQL Server 6.5, both versions are part of one instance, which can be your default instance. When installing SQL Server 2000 on a machine that has version-switching, you can either install SQL Server 2000 as a named instance or upgrade SQL Server 7.0 to SQL Server 2000. In the latter case, you can version-switch between SQL Server 2000 and 6.5. Note that if you have a SQL Server 6.0 or 4.2 installation on your machine, you can't install SQL Server 2000.

Your default instance can be SQL Server 2000, 7.0, or 6.5. Only SQL Server 2000 supports named instances, so earlier releases can be only the default instance. Microsoft didn't build in a hard limit for the maximum number of named instances you can install, so technically no limit exists. However, the SQL Server developers have tested the product with up to only 10 instances on one machine and have decided that 16 instances is the maximum that Microsoft will officially support.

Instance names must be fewer than 32 Unicode characters long because the service name prefixes the string 'SQLAgent$' to the name of each instance. At least one NT tool limits service names to 40 Unicode characters, so for SQL Server 2000 to be compatible with all tools, you're limited to 32 characters for each instance name. Also, the instance name must start with a letter and can't be either Default or MSSQLServer.

Each instance has a separate directory for storing the server executables, and you can place each instance's data files wherever you choose. Each instance also has its own SQL Server Agent service. The service names for the default instance are MSSQLServer and SQLServerAgent. For an instance named SHILOH, the services would be named MSSQL$SHILOH and SQLAGENT$SHILOH.

If your default instance is version-switching with SQL Server 6.5, you'll have the tools for SQL Server 6.5 available. However, as of the current beta release, you'll have only one installation of the tools for all instances of SQL Server 7.0 and SQL Server 2000. This limitation means you'll have only SQL Server 2000 Query Analyzer, Enterprise Manager, and SQL Server Profiler. And, regardless of how many instances of SQL Server 2000 you've installed, you'll have only one Microsoft 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).

Connectivity

As I mentioned, to connect to a named instance, you must specify both the server name and the instance name. I could use the osql command-line utility to connect to my SHILOH instance with the command

osql /Usa /Sogionshiloh

You'll need the SQL Server 2000 Microsoft Data Access Components (MDAC) stack on the client to get multi-instance connectivity from your client machines. You can't use the stack that comes with SQL Server 7.0 or 6.5.

When you install SQL Server 2000, the default network libraries (NetLibs) for the server are Named Pipes, TCP/IP, and Multiprotocol (for a default instance only—Multiprotocol doesn't support named instances). The default instance listens on a pipe named \.pipesql query and on TCP port number 1433. A named instance listens on a pipe named \.pipeMSSQL$sqlquery, which for my SHILOH instance would be \.pipeMSSQL$shilohsqlquery.

Determining which TCP port to use for a named instance is more problematic. Microsoft has reserved port number 1433 but couldn't reserve additional port numbers. During setup, you can choose any port number, but be aware that other vendors' services might conflict with the port number you choose. To avoid problems, the setup program lets you specify 0 for the port number, which means that every time the SQL Server instance starts, it finds an unused port number to use. You can use the Server Network Utility to see which port SQL Server chose. Select the instance name, then click Properties for TCP/IP.

Letting multiple instances of SQL Server run on one machine requires another change to the server connectivity components. A new listener service runs on UDP port 1434 but doesn't have a service name on the machine. This listener service exposes which SQL Server instances are installed, their network addresses, and so on. The listener service is a component of every SQL Server instance—at server startup, the service looks to see whether any other instance is listening on UDP port 1434. If no other instance is on that port, the listener service on the newly started SQL Server instance starts listening and becomes the listener service for the machine.

When a SQL Server 2000 tool tries to connect to a machine, the client pings the machine to ask for information about all the instances installed, their addresses, and other information such as whether they are clustered. For each instance, the packet reports the server NetLibs and the network addresses the instance is listening on. After the client computer receives this packet, it chooses a NetLib that is enabled on both the application computer and on the desired instance of SQL Server and makes a connection to the address listed for that NetLib in the packet.

The ability to run multiple SQL Server instances concurrently will provide benefits across a wide spectrum of applications. I enjoy having the new versions available so that I can beta-test the new features while my previous version is available for my writing projects. In an upcoming article, I'll show you some new tools to help you keep track of the instances you have installed.