SQL Server Reference Guide

You may have heard the terms “SQL Server” and “Instance” used interchangeably. Technically, this isn’t accurate, although most of the time the meaning is clear from the context. But the fact that SQL Server has Instances is important — so in this overview, I’ll explain what these terms mean.

In the earliest versions of SQL Server version, you could only run one copy of SQL Server on a single computer at a time. There are times, however, when it’s useful to have more than one copy running. To explain that, we need to delve a bit further into how SQL Server works in general.

SQL Server is composed of three main parts: an engine, which is the software started by a few Windows services that performs lookups, sorts, and other actions; meta-data such as the master and msdb system databases; and user databases where data is stored. The master database contains the information that the engine reads when it starts up. It includes such things as security settings, file locations, sort orders, and database locations. The msdb database contains the information used by the SQL Server Agent program and information about maintenance plans. Yet another system database, called model, is the "template" from which other databases are created. Finally, the tempdb database is the "scratch" area that the engine software uses. This format holds true for all versions of SQL Server, although other control mechanisms are also implemented as Dynamic Link Libraries, or DLL’s.

This means that a single installation of SQL Server has only one set of certain data, such as server-level security credentials, scheduling information, temporary files and other meta-data.

Beginning with SQL Server 2000, you can run multiple copies of the software, using what Microsoft calls Instances. Instances share a few files between them, mostly dealing with client tools. This allows you to have two different system administrators (sa accounts) and other server-level security on the same hardware. So if you have different security needs, say running more than one company with different administrators, you can install multiple copies of SQL Server on the same hardware.

Another advantage is that since some of the files that run the Instance are duplicated, you can apply service packs separately to each Instance. That way you can host several applications on the same hardware that require different service pack levels.

Instances also allow you to work with multiple versions and even editions of the product. You can install Instances at any time, even after SQL Server is installed and running for some time. So for Instance (no pun intended), you can install SQL Server 2005 Express Edition, SQL Server 2005 Enterprise Edition, SQL Server 2008 Standard Edition and SQL Server 2008 R2 Developer Edition, all on the same hardware.

The Default Instance

When you install SQL Server on a system, one copy of the software can be (but doesn’t have to be) designated as the Default Instance. It doesn’t have a special network name; it works by using the name of the computer. So in any client tools or programs, when you want to talk to a Default SQL Server Instance you just enter the name of the computer it runs on, like BUCKTEST for the BUCKTEST Windows Server. The network information for the Default Instance for named pipes connections is \\.\pipe\sql\query. The Default Instance TCP/IP port is 1433. This works for SQL Server 2000, although in later versions you can change this port — but more about that later.

SQL Server is made up of various files and services. The names of the Default Instances services are MSSQLServer for the engine and SQLServerAgent for the SQL Server Agent. If you have older SQL client applications that only use the computer name, then you can still use those against the Default Instance.

If you want to use multiple versions of SQL Server, it gets a little more complicated. SQL Server 2000 can sit on the same server as SQL Server version 6.5 or SQL Server version 7.0, but you will have to install the 6.5 or 7.0 version first as the Default Instance.

For SQL Server 2005 and higher, you can install SQL Server 2000 as a Default Instance, and then install SQL Server 2005 as a named Instance, as I'll describe in a moment.

You can only have one version of SQL Server as the Default Instance; that is, you can’t have two Default Instances. That kind of stands to reason, since only the computer name is used to identify the Instance.

Named Instances

It's a fairly simple process to install and use named Instances. After your initial installation of SQL Server, just put the CD back in the drive and repeat the installation, this time choosing the option to install a new Instance when prompted. Enter the name of the Instance and all the service account information, and you're all set. The installation documentation that comes with SQL Server explains these choices in greater detail.

Installing the software again gives you another Instance, called a Named Instance. With Named Instances, you get another copy of the SQL Server Engine and the SQL Server Agent services. For named Instances, the names of the SQL services become MSSQL$NameOfInstance and SQLAgent$NameOfInstance. That way, you can start the various named Instances separate from each other. You can use different accounts to start the various services, which allows you to control security better. For instance, you can set up a directory that only certain accounts can access, and then assign one of those accounts to start an Instance. The administrator of that Instance can then send the backup files there, but nowhere else.

Another use for Instances is to separate versions and releases. Perhaps you have one set of software that runs on SQL Server 2000, another that requires 2005 with no service packs (which is a bad thing), and still another that uses SQL Server 2008 with the latest service pack or a certain hotfix. You can run all of these on the same server with different levels of service packs.

Still another use for Instances is the ability to have different server settings on each Instance. One Instance can use Windows security only, and another can used mixed security. One can have ANSI NULLS as the default connection parameter; the other can enforce a different standard.

You also get new registry keys associated with the database engine and the SQL Server and SQL Server Agent services, and more network connection addresses so that applications can connect to specific Instances. It is in this network area that SQL Server 2000 and the later versions act a little differently.

In SQL Server 2000, there is only one copy of English Query and Analysis Services installed, regardless of the number of Instances. English query is gone in SQL Server 2005 and higher, and Analysis Services works a bit differently. We'll cover that in another set of tutorials.

In all versions of SQL Server 2000 and higher, the named Instances can use Named Pipes, TCP/IP, and in 2000, NWLink IPX/SPX protocols. The Named Pipes address for a named Instance becomes \\Computername\Pipe\MSSQL$NameOfInstance\Sql\Query. In SQL Server 2000, the TCP/IP port for a named Instance moves to 1434, and SQL Server 2000 directs the client to the proper Instance using SQL Server Resolution Protocol (SSRP).

In SQL Server 2005, Microsoft introduced a new service called the SQL Browser. This service listens on UDP port 1434 and directs the connection to the proper dynamically chosen TCP/IP port.

Even if you have multiple named Instances, you only have one SQL Server program group (per version) and one copy of the utility represented by each icon in the program group.

That brings up an interesting point - the utilities in the program group are created from the first version of SQL Server 2000 installed on the computer. This becomes a bit more interesting if you install different languages of SQL on the server — the utilities will all be from the first language installed. Don’t worry; the Service Manager will start or stop any of the Instances installed on a single box.

Each named Instance has a different location for program files and data files that is different from that of the other Instances of SQL Server. In SQL Server 2000, the directories look like this:

\Program Files\Microsoft SQL Server\MSSQL$NameOfInstance\Binn (Executable files) 

and:

\Program Files\Microsoft SQL Server\MSSQL$NameOfInstance\Data (Data files) 

Those common tools for all Instances in SQL Server 2005 are located in the

\Program Files\Microsoft SQL Server\90\Tools directory.

In SQL Server 2005 and higher, the files have a different structure for the Default Instance:

\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL (Executable files) 

and:

\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data (Data files) 

In SQL Server 2005 and higher, each Instance's folder will have a different number. In the Default Instance, as described above, it's 1, and then more are added for each Instance from there. This same structure holds true for SQL Server 2008 and later versions, although the 90 part of the files changes to 10 — the internal numbering system for SQL Server at Microsoft.

How do you identify the named Instances using a client? The named Instances are identified by the network name of the computer plus the Instance name, like this:

NameOfComputer\NameOfInstance

So my test system Instance called NewServer on the BUCKTEST computer becomes:

BUCKTEST\NEWSERVER

Note that some applications won’t like that naming convention, but you can use the SQL Client tools installed on a local machine and create a SQL "alias" for it.

Here are a few more practicalities: The maximum number of Instances supported in SQL Server 2000 and 2005 is 16 for the standard editions and lower, and 50 for the Enterprise Editions in 2005 and even more in later versions and higher edition numbers. Check the http://microsoft.com/sql site for the latest numbers on newer versions. There is usually a link there under the product information area called “Editions” that has a chart that will show you the correct information for what you need.

You can install multiple Instances, but they will consume the resources of your machine quickly. Don’t worry about them behaving well together, as each Instance will move around in memory to accommodate each other, but you will see a performance hit if two or more of them are doing heavy work at one time. At the very least, you should separate out the various databases from each other so that you don’t get a lot of disk contention. The most important thing to remember is that you are dividing a single server's resources among a fairly hefty software package, so be sure and take that into account.

You can run only one Instance of SQL Server on each virtual server of a SQL Server failover cluster, although you can install up to 16 virtual servers on a failover cluster. The Instance can be either a Default Instance or a named Instance. Virtualizing with technologies like Hyper-V or VMWare don’t affect the numbers of Instances available — that’s still a function of version and edition.

Just to confuse things a little, a named Instance is not the same thing as having multiple Instances. What that means is that you can install a single named Instance or you can have multiple named Instances. It’s possible NOT to have a default instance, in other words, although I don’t see this practiced a lot.

Well, that wraps up our discussion of Instances for this week. If you haven’t tried this yet, fire up your test server and pop in that SQL Server CD — install another Instance and experiment.

Oh — one final word on this. You don't have to license the Instances separately. That is, if you have SQL Server installed, you can add as many Instances as you want without paying extra license fees. Always check with your local Microsoft office if you’re confused about the licensing. They have specialists there that will be happy to help you out, and they aren’t going to audit you or anything if you’re just asking questions.

InformIT Articles and Sample Chapters

Deac Lancaster covers Instances from another angle in his book excerpt called SQL Building Blocks and Server Settings.

Books and eBooks

Eric Johnson has a great introduction to SQL Server, including Instances, in SQL Server Fundamentals for the Accidental DBA (Video Training).

Online Resources

Here’s a little more information on connecting to a Named Instance.