Installing a Named Instance

New for SQL Server 2000 is the capability to run more than one instance per server. Each instance has its own separate copy of the SQL Server installation files, its own system databases, and its own user databases. Microsoft supports 16 named instances per server.

Each server can have one default database, which is referenced by the server name as in previous installations of SQL Server. The default database can be version 6.5, 7.0, or SQL Server 2000. If the default is version 6.5, you must switch between the 6.5 database and SQL Server 2000 using the v-switch command-line utility. SQL 7.0 installations can function as the default database without switching. Named instances must be SQL Server 2000.

Each subsequent instance that is installed is given a name and is thereafter referenced by the combination of server\name. For example, let's say I installed a named instance as unleashed on the server bigserver. When connecting to that instance using query analyzer, I would select bigserver\unleashed in the connection window.

Why Install Multiple Instances of SQL Server?

Installing multiple instances of SQL Server allows complete separation of instances without the additional overhead of multiple servers. It can be more cost effective to run one large server as opposed to several less powerful ones. This also has implications for ASPs, or Application Service Providers, which can host multiple clients' databases on their servers. As in any given instance of SQL Server, all databases share server-wide settings such as memory and security. Named instances are required to provide for total security separation between clients and can even provide different settings for options such as memory allocation.