FreeTDS Development Group

[martin dempsey <mjd AT digitaleveryware.com>]

> The second SQL Server would have to be running on a second port (other
> than port 1433), you need to find out what port that is and specify that
> for your server in the interfaces or freetds.conf file.

Thanks, I repeatedly modified my interfaces file and tried all the
servers open ports and I'm happy to report that I could succesfully
use all four different instances of sql server 2000 running on the
same Windows 2000 box (using freetds-0.51 w/protocol 4.2)!  

The latest version of Microsofts MDAC understands how to the find the
port number of the correct instance given the name of the virtual
server. I've included a detailed description of the method it uses
below. According to the description below, the port may change each
time the machine is booted. Is finding the correct port from an
instance name something freetds should/will support or is it just more
proprietary Microsoft strangeness?


----- description from microsofts web site ------------------
----- http://msdn.microsoft.com/msdnmag/issues/0800/sql2000/sql2000.asp

SQL Server now supports multiple instances of the database engine
running concurrently on the same computer, each having its own set of
system and user databases. Applications can connect to each database
engine instance on a computer in much the same way they connect to
database engines running on different computers. Each system will have
a default instance, identified solely by the name of the computer. All
other named instances are identified by an instance name specified
during installation. Named instances are identified by the computer
name and the instance name in the format computer_name\instance_name.

---------------------------------------------------------------------

---- description of mechanism at sqlmag.com --------------------------
---- http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8686

As I mentioned, to connect to a named instance, you must specify both
the server name and the instance name. I could use the osql
command-line utility to connect to my SHILOH instance with the command

osql /Usa /Sogion\shiloh

You'll need the SQL Server 2000 Microsoft Data Access Components
(MDAC) stack on the client to get multi-instance connectivity from
your client machines. You can't use the stack that comes with SQL
Server 7.0 or 6.5.

...

Determining which TCP port to use for a named instance is more
problematic. Microsoft has reserved port number 1433 but couldn't
reserve additional port numbers. During setup, you can choose any port
number, but be aware that other vendors' services might conflict with
the port number you choose. To avoid problems, the setup program lets
you specify 0 for the port number, which means that every time the SQL
Server instance starts, it finds an unused port number to use. You can
use the Server Network Utility to see which port SQL Server
chose. Select the instance name, then click Properties for TCP/IP.

Letting multiple instances of SQL Server run on one machine requires
another change to the server connectivity components. A new listener
service runs on UDP port 1434 but doesn't have a service name on the
machine. This listener service exposes which SQL Server instances are
installed, their network addresses, and so on. The listener service is
a component of every SQL Server instance?at server startup, the
service looks to see whether any other instance is listening on UDP
port 1434. If no other instance is on that port, the listener service
on the newly started SQL Server instance starts listening and becomes
the listener service for the machine.

When a SQL Server 2000 tool tries to connect to a machine, the client
pings the machine to ask for information about all the instances
installed, their addresses, and other information such as whether they
are clustered. For each instance, the packet reports the server
NetLibs and the network addresses the instance is listening on. After
the client computer receives this packet, it chooses a NetLib that is
enabled on both the application computer and on the desired instance
of SQL Server and makes a connection to the address listed for that
NetLib in the packet.
----------------------------------------------------------------------