SQL Server Named Instances

Microsoft SQL Server 2000 introduced a new way to let multiple applications share a database server. In addition to setting up separate databases in SQL Server, system managers can run more than one instance of SQL Server on the same machine, a technique known as instancing. Instances not only provide for distinct data files; they also create a completely new set of system databases, system settings, and users, including separate systems administrator (sa) accounts.

By default, SQL Server creates a default instance when you install it. Each machine can have only one default instance. If you're upgrading from SQL Server 7.0 or SQL Server 6.5, you can keep your installed database as the default instance, which lets you run SQL Server 2000 side-by-side with your existing database during a migration. This side-by-side installation of multiple instances of SQL Server 2000 or SQL Server 7.0 lets you administer separate data environments for different Web applications the same way you create separate Web sites for each application.

As the ability to run side-by-side implies, SQL Server named instances aren't just in-memory configurations. An instance is another copy of SQL Server 2000 that runs as a separate process and a separate service. By running the SQL Server setup a second time, you create a new instance, and you can enter separate settings for the type of authentication and even a different sa account password. Each additional instance of SQL Server has a name, which is then included in everything associated with the instance. For example, say you create a second instance named InstanceTwo. After you complete the installation and select the Services manager from the Administration Tools menu, two services will be defined and running: the MSSQLSERVER service and the MSSQL$InstanceTwo service.

Instance names are also part of the machine name component of the connection string. To define a Data Source Name (DSN) for an ODBC connection, define an ADO connection string, or just gain access through SQL Server Enterprise Manager, add a backslash and the instance name to the machine name. For example, if the application is hosted on Server1, the access to InstanceTwo becomes Server1InstanceTwo.

Instancing is a great way to mirror multiple sites on your Web farm with multiple independent databases in your SQL Server cluster. Although using multiple instances consumes more system resources than single instances, multiple instances have several advantages in a Web-based architecture. You can limit exposure to your data when a privileged account from one instance is compromised. If you plan to host Web sites for multiple customers—internal or external—in a shared environment, SQL Server named instances can provide more control over database access partitioning and improve reliability of the applications that use those databases. For more information about SQL Server named instances, see "Working with Named and Multiple Instances of SQL Server 2000" (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_runsetup_2xmb.asp).