Working with Multiple Instances

SQL Server Setup Help

Working with Multiple Instances

Although multiple instances of Microsoft® SQL Server™ 2000 can run on a single computer, there is no direct connection between instances. Each instance operates in many ways as if it is on a separate server. An application connected to one instance cannot access objects in databases created in another instance, except through distributed queries. Databases and database files cannot be shared between instances.

Named instances of SQL Server 2000 database engines have almost the same behaviors as default instances. The main difference is that you must supply both the computer name and instance name to identify a named instance. When you specify only computername, you work with the default instance. When you specify computername\instancename you work with the named instance.

Service Manager.
When you specify only computername in Service Manager, you can stop and start the default instance. When you specify computername\instancename you can stop and start the named instance. When a specific instance is started, any database created in that instance is available to any application that connects to the instance using an authorization ID that has permissions to access the database.
SQL Server Enterprise Manager.
Using SQL Server Enterprise Manager you can register each instance for which you have permissions. After an instance is registered, you can create, edit, and drop objects in the databases associated with that instance, subject to the permissions granted to you. You can also create, edit, and drop Data Transformation Services, Replication, and SQL Server Agent objects for that instance.
Applications.
In an application, when you specify computername as the server name parameter in a connection request, you are connected to the default instance on the computer. You can access any databases in the default instance that you have permissions to access. If you specify computername\instancename as the server name parameter, you are connected to the named instance. You can access any databases in that named instance that you have permissions to access. When you are connected to a specific instance, objects in databases in other instances can be accessed only through distributed queries, just as objects in databases on other servers can be accessed only through distributed queries. Applications specify the instance name in different ways:
- ADO applications specify "Server=computername\instancename" in the provider string. For more information, see Connecting to Multiple Instances of SQL Server.
- OLE DB applications specify "Server=computername\instancename" in the provider string. They can alternatively set DBPROP_INIT_DATASOURCE to computername\\instancename (the backslash must be escaped with a second backslash). For more information, see Establishing a Connection to a Data Source.
- ODBC applications specify "Server=computername\instancename" in the connection string specified on SQLDriverConnect. They can alternatively specify computername\\instancename for the ServerName parameter on SQLConnect, or connect through a data source that has computername\instancename specified for the server name. For more information, see Support for SQLDriverConnect and SQLConfigDataSource.
- SQL DMO applications can manage instances of SQL Server 2000 using the SQLServer2 object. For more information, see SQLServer2 Object.
- DB-Library and Embedded SQL for C do not support multiple instances.
Distributed queries and linked servers.
Distributed queries and linked server definitions use computername\instancename to identify named instances and computername to identify default instances. For more information, see Distributed Queries on Multiple Instances of SQL Server.
Command prompt utilities.
When you use the command prompt utilities, you can use the Server switch to specify an instance by using computername\instancename, for example:
```
osql -E -Scomputer1\instance1
sqlservr /Sinstance1
```
The isql utility does not support named instances.
SQL Server 2000 client components.
Applications using SQL Server 2000 client components can enumerate the instances available for connections:
- The OLE DB Provider for SQL Server 2000 returns instance names using ISourcesRowset::GetSourcesRowset. The names of named instances are returned as the data source name in the format computername\instancename, where computername can be either the name of a single computer or the virtual server name of a failover cluster. The names of default instances are returned as the data source name in the format computername, with no instance name.
- The SQL Server 2000 ODBC driver supports extensions to SQLBrowseConnect and SQLSetConnectAttr that allow applications to enumerate instances on a server. ODBC applications can also determine whether the computername is the name of a single computer or a virtual server name for a failover cluster. For more information, see SQLBrowseConnect.
- SQL-DMO applications can enumerate instances using the SQLServer2 object. The SQLServer2 object also presents information such as the names of the SQL Server and SQL Server Agent services for the instance, or whether the instance is running on a single computer or a failover cluster. For more information, see SQLServer2 Object.
- DB-Library and Embedded SQL for C do not support named instances.

Identifying Instances

Performance Monitor counters, Profiler events, and Windows events in the Event Viewer Application Log all identify the instance of SQL Server with which they are associated.

The string returned by the @@SERVERNAME function identifies the name of the instance in the form servername\instancename if you are connected to a named instance. If connected to a default instance @@SERVERNAME returns only servername. For more information, see @@SERVERNAME.

The SERVERPROPERTY function INSTANCENAME property reports the instance name of the instance to which you are connected. INSTANCENAME returns NULL if connected to a default instance. In addition, the SERVERNAME property returns the same format string returned by @@SERVERNAME and will have the format servername\instancename when connected to a named instance. For more information, see SERVERPROPERTY.

Although the strings reported by @@SERVERNAME and SERVERNAME use the same format, the information they report can be different, for example:

The string returned by @@SERVERNAME is affected by the actions of sp_addserver and sp_dropserver, and the string reported by SERVERNAME is not.
SERVERNAME automatically reports changes in the network name of the computer, and @@SERVERNAME does not, unless sp_dropserver and sp_addserver are used to change the name it reports.

previous page start next page