Multiple Instances of SQL Server

SQL Server Setup Help

SQL Server Setup Help

Multiple Instances of SQL Server

Microsoft® SQL Server™ 2000 supports multiple instances of the SQL Server database engine running concurrently on the same computer. Each instance of the SQL Server database engine has its own set of system and user databases that are not shared between instances. Applications can connect to each SQL Server database engine instance on a computer in much the same way they connect to SQL Server database engines running on different computers.

There are two types of instances of SQL Server:

Default Instances

The default instance of the SQL Server 2000 database engine operates the same way as the database engines in earlier versions of SQL Server. The default instance is identified solely by the name of the computer on which the instance is running, it does not have a separate instance name. When applications specify only the computer name in their requests to connect to SQL Server, the SQL Server client components attempt to connect to the default instance of the database engine on that computer. This preserves compatibility with existing SQL Server applications.

There can only be one default instance on any computer, the default instance can be any version of SQL Server.

Named Instances

All instances of the database engine other than the default instance are identified by an instance name specified during installation of the instance. Applications must provide both the computer name and the instance name of any named instance to which they are attempting to connect. The computer name and instance name are specified in the format computer_name\instance_name.

There can be multiple named instances running on a computer, but only the SQL Server 2000 database engine can operate as a named instance. The database engines from earlier versions of SQL Server cannot operate as a named instance.

Instances apply primarily to the database engine and its supporting components, not to the client tools. When you install multiple instances, each instance gets a unique set of:

  • System and user databases.

  • The SQL Server and SQL Server Agent services. For default instances, the names of the services remain MSSQLServer and SQLServerAgent. For named instances, the names of the services are changed to MSSQL$instancename and SQLAgent$instancename, allowing them to be started and stopped independently of the other instances on the server. The database engines for the different instances are started and stopped using the associated SQL Server service. The SQL Server Agent services manage scheduled events for the associated instances of the database engine.

  • The registry keys associated with the database engine and the SQL Server and SQL Server Agent services.

  • Network connection addresses so that applications can connect to specific instances.
Shared Components

The following components are shared between all of the instances running on the same computer:

  • There is only one SQL Server 2000 program group (Microsoft SQL Server) on the computer, and only one copy of the utility represented by each icon in the program group. There is only one copy of SQL Server Books Online.

    The versions of the utilities in the program group are from the first version of SQL Server 2000 installed on the computer. For example, if you install the French version of SQL Server 2000 as a default instance and then the U.S. English version of SQL Server 2000 as a named instance, there is one SQL Server 2000 program group. All of the utility icons and the SQL Server Books Online icon in the program group start the French versions of the tools.

    All of the SQL Server 2000 utilities work with multiple instances. You can start and stop each of the instances from a single copy of the SQL Server 2000 Service Manager. You can use a single copy of the SQL Server 2000 SQL Server Enterprise Manager to control objects in all instances on the computer, and use a single copy of the SQL Server 2000 Server Network Manager to manage the network addresses with which all of the instances on the computer communicate.

  • There is only one copy of the MSSearchService that manages full-text searches against all of the instances of SQL Server on the computer.

  • There is only one copy each of the English Query and Microsoft SQL Server 2000 Analysis Services servers.

  • The registry keys associated with the client software are not duplicated between instances.

  • There is only one copy of the SQL Server development libraries (include and .lib files) and sample applications.
Default Instances

Configurations that can operate as a default instance include:

  • A default instance of SQL Server 2000.

  • An installation of SQL Server version 7.0 operates as a default instance.

  • An installation of SQL Server version 6.5 operates as a default instance.

  • A default instance of SQL Server 2000 that can be version switched with an installation of SQL Server version 6.5 using the SQL Server 2000 vswitch utility.

  • An installation of SQL Server version 7.0 that can be version switched with an installation of SQL Server version 6.5 using the SQL Server version 7.0 vswitch utility.

    Note  You must apply SQL Server 6.5 Service Pack 5 to any instance of SQL Server 6.5 before installing instances of SQL Server 2000 on the same computer.

Switching Between Versions of SQL Server

You cannot version switch between an installation of SQL Server version 7.0 and a default instance of SQL Server 2000.

You can have any number of named instances of SQL Server 2000 in addition to the default instance. You are not required to run a default instance on a computer before you can run named instances. You can run named instances on a computer that has no default instance. SQL Server version 6.5 and SQL Server 7.0 cannot operate as named instances, only as default instances.

Microsoft does not support more than 16 instances on a single computer or failover cluster.

If you run SQL Server version 6.5 as a default instance and run one or more named instances of SQL Server 2000 on a single computer, the computer has two SQL Server program groups instead of one SQL Server program group:

  • A SQL Server 2000 program group executes the SQL Server 2000 tools.

  • A SQL Server version 6.5 program group runs the SQL Server 6.5 tools.

If you are running SQL Server version 7.0 with SQL Server 2000, the icons in the SQL Server 7.0 program group will execute the SQL Server 2000 tools.

Note  You must apply SQL Server 6.5 Service Pack 5 to any instance of SQL Server 6.5 before installing instances of SQL Server 2000 on the same computer.

Multiple Instances of SQL Server on a Failover Cluster

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. The virtual server looks like a single computer to applications connecting to that instance of SQL Server. When applications connect to the virtual server, they use the same convention as when connecting to any instance of SQL Server; they specify the virtual server name of the cluster and the optional instance name (only needed for named instances): virtualservername\instancename. For more information about clustering, see Failover Clustering Architecture.