Connecting to Instances

After an instance is completely installed, you can verify that the installation was successful connecting to this new instance. In particular, to connect to a named instance, use the following syntax: servernameinstancename. Notice that when there are many instances in one server, from the client's point of view, each instance works as a completely different server.

There are many ways to test the connection to SQL Server. For example, you can use osql, which is a command-line utility that connects to SQL Server using ODBC. Figure A.14 shows how this command is used from the DOS prompt.

Clients that connect to a named instance of SQL Server 2000 must have installed at least the Microsoft Data Access Components (MDAC) 2.6, which are the ones installed by SQL Server 2000. Therefore, if you install SQL Server 2000's client utilities in a client machine, you will be able to connect to named instances. However, you can install MDAC 2.6 separately in a client machine without installing the client tools of SQL Server 2000. MDAC 2.6 can be downloaded directly from Microsoft's Web site (http://www.microsoft.com/data).

Another way to connect to a named instance of SQL Server 2000 is by creating an alias in the client machine using the Client Network utility, which is one of the SQL Server client tools. When creating the alias, you must specify the name of the alias (this is the one you will use to connect to the named instance), the network library, and the name of the instance—for example, SQLBYEXAMPLEAPPENDIXA. Figure A.15 shows the creation of an alias called TESTINSTANCE using the Client Network utility.

After the alias is created, you can connect to the named instance using the alias name (TESTINSTANCE), instead of servernameinstancename. Figure A.16 shows how to connect to a named instance using an alias in the Query Analyzer.

If you create an alias using the TCP/IP network library, it is recommended you set the Dynamically Determine Port option, because a SQL Server named instance, by default, chooses an available TCP port every time it is started. Therefore, you don't know the TCP port used by the named instance to accept incoming connections beforehand, unless you specify a port for the named instance in the Server Network utility. Figure A.17 shows how to configure a SQL Server 2000 named instance to use a specific TCP port (8888 in this case).

Then, you can create an alias to connect to the named instance using the port specified in the Server Network utility. This is shown in Figure A.18.

Usually, applications connect to SQL Server using either ODBC or OLE DB. Specifically, in OLE DB connection strings, the name of the server is specified using the following syntax: Data Source=computernameinstancename. The network library can also be specified in the connection string. For example, Listing A.1 shows a connection string that connects to the Northwind database located in SQLBYEXAMPLEAPPENDIXA, using integrated security (Integrated Security=SSPI) and the TCP/IP network library (Network Library=dbmssocn).

Code Listing A.1. An OLE DB Connection String Used to Connect to a Named Instance

					
"Provider=SQLOLEDB.1;Data Source=dev08instance2;Integrated Security=SSPI;
Initial Catalog=Northwind;Network Library=dbmssocn"

You can issue distributed queries to a named instance through a linked server. Specifically, when querying tables in the linked server use the following syntax:

[servernameinstancename].database.username.objectname

Listing A.2:shows how a linked server is configured when connecting to a named instance, and then it issues a query against the linked server.

Code Listing A.2. Creating a Linked Server Using Instances

					
sp_addlinkedserver @server = 'dev08instance1'
GO

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

SELECT * FROM [dev08instance1].Northwind.dbo.Shippers
GO
(1 row(s) affected)

(1 row(s) affected)
ShipperID   CompanyName                              Phone
----------- ---------------------------------------- ------------------------
1           Speedy Express                           (503) 555-9831
2           United Package                           (503) 555-3199
3           Federal Shipping                         (503) 555-9931
(3 row(s) affected)