Lesson 4: Using Default, Named, and Multiple Instances of SQL Server 2000

3 4

SQL Server 2000 supports the installation of multiple instances (or copies) of SQL Server 2000. This capability allows you to have sets of system and user databases that are independent of each other. This capability allows you to work with earlier versions of SQL Server already installed on your computer, to test development software, and to operate instances of SQL Server 2000 independent of each other. This lesson teaches you how to install and work with default and named instances of SQL Server 2000. This lesson also covers what components are shared between instances of SQL Server.

---

After this lesson, you will be able to

• Install multiple named instances of SQL Server 2000
• Understand when the use of multiple instances is recommended
• Understand what components are unique between instances and what components are shared
• Work with default and named instances of SQL Server 2000

Estimated lesson time: 15 minutes

---

Installing Multiple Instances of SQL Server 2000

When you install SQL Server 2000, you have the option to define the installation as the default instance or as a named instance. A named instance simply means that you define a name for an instance during installation and that you access that instance using this name. You can only have one default instance, but you can have many named instances. A default instance can be an installation of SQL Server 6.5, SQL Server 7.0, or SQL Server 2000. A named instance can only be an installation of SQL Server 2000.

When you run the SQL Server 2000 Setup program, it will detect whether a default instance already exists on the computer. If a default instance is not detected, the Setup program allows you to choose to install a default or a named instance. To install a named instance, clear the Default check box. See Figure 2.11.

Figure 2.11

Installing a named instance.

If a default instance already exists, you have several choices depending upon the version of SQL Server that is installed as the default instance. Upgrading from earlier versions of SQL Server is covered in Chapter 4.

• If the default instance is a SQL Server 2000 installation, you can install a named instance of SQL Server 2000.
• If the default instance is a SQL Server 7.0 installation, you can choose to upgrade the default instance to SQL Server 2000 or install a named instance of SQL Server 2000.
• If the default instance is a SQL Server 6.5 installation, you can install SQL Server 2000 as the default instance or as a named instance. If you install it as the default instance, you can use the version switch (Vswitch.exe) utility to switch between SQL Server 6.5 and SQL Server 2000. You must install SQL Server 6.5 SP5 to any instance of SQL Server 6.5 before you install an instance of SQL Server 2000 on the same machine.

Using Multiple Instances of SQL Server 2000 Effectively and Appropriately

Using multiple instances of SQL Server 2000 increases administration overhead and causes a duplication of components. These additional instances of the SQL Server and SQL Server Agent services require additional computer resources, namely memory and processing capacity. Running multiple databases in a single instance will provide superior performance compared to running a similar number of databases in multiple instances.

Using multiple instances is appropriate when testing multiple versions of SQL Server on the same computer. It is also appropriate when testing service packs and development databases and applications. Using multiple instances is also particularly appropriate when different customers each require their own system and user databases along with full administrative control of their particular instance. Finally, when the desktop engine is embedded in applications, each application can install its own instance independent of instances installed by other applications.

Understanding Shared Components Between Instances

Table 2.6 lists the components that are shared between all instances when you install multiple instances of SQL Server. These components are shared regardless of whether the default instance is SQL Server 6.5, SQL Server 7.0, or SQL Server 2000.

Understanding Unique Components Between Instances

When you install multiple instances of SQL Server 2000 (or SQL Server 2000 and either SQL Server 6.5 and 7.0), system and user databases are unique and completely independent of each other. There is no direct connection between system or user databases in one instance and system and user databases in another instance. These databases function as if they were residing on separate SQL Server installations.

Each instance has its own SQL Server and SQL Server Agent services. For the default instance, the names of these services are MSSQLServer and SQLServerAgent. For named instances, the names of these services are MSSQL$instancename and SQLAgent$instancename. The database engine for each instance is configured completely independently of any other instance. Jobs on one instance have no knowledge of and do not interact with jobs on any other instance.

Table 2.6 Shared Components in Multiple Instances of SQL Server

Component	Description
Client management tools and utilities (and their associated registry keys)	All instances of SQL Server on a computer share the same version of all client management tools and utilities (and their associated registry keys). These tools and utilities work with all instances. The version of the tools and utilities will be the version from the first SQL Server 2000 version installed on the computer. If SQL Server 6.5 or 7.0 is already installed on the computer that you install SQL Server 2000 on, the SQL Server 6.5 or 7.0 client management tools and utilities are upgraded to SQL Server 2000 client management tools and utilities.
Books Online	All instances of SQL Server on a computer share the same version of SQL Server Books Online and that will be the one from the first version of SQL Server 2000 installed. If instances from multiple languages are installed, Books Online will be in the language of the first SQL Server 2000 version installed. If any earlier version of SQL Server was on the computer, that version of Books Online will be upgraded.
Microsoft Search service	There is only one instance of the Microsoft Search service.
Distributed Transaction Coordinator	There is only one instance of the Distributed Transaction Coordinator service.

Working with Default and Named Instances of SQL Server 2000

Each instance of SQL Server 2000 listens on a unique set of network addresses. The default instance listens on the same network address as earlier versions of SQL Server. Therefore, client applications using client connectivity components from earlier versions of SQL Server can connect to SQL Server 2000 without an upgrade of those connectivity components. However, in order to connect to named instances, client applications must use the SQL Server 2000 connectivity components, or the client connectivity components must be configured to connect to alternate addresses appropriate to the particular named instance involved. The SQL Server 2000 connectivity components allow client applications to automatically detect the network libraries and network addresses required to connect to default or named instances. The only information that must be provided by the client application is the name of the computer, and the instance name if applicable.

Lesson Summary

SQL Server 2000 supports the installation of multiple instances of SQL Server 2000. This means that multiple instances of the SQL Server and SQL Server Agent services, as well as system and user databases, are supported. This allows multiple versions of SQL Server to coexist. However, only one version of SQL Server tools and utilities is supported. SQL Server 2000 tools and utilities replace tools and utilities from earlier versions of SQL Server, even if the earlier version is not upgraded. Use multiple instances only where appropriate, such as testing new versions of SQL Server or development databases and applications.