FreeTDS Development Group

[Brian Bruns <camber AT ais.org>]

Wow, MS has chosen a bit of a convoluted scheme to support multiple
instances on a host.

Now, if someone wanted to provide some network traces of udp traffic on
port 1434, I'm sure we could support this.

Brian

On Thu, 18 Oct 2001, martin dempsey wrote:

> 
> > 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.
> ----------------------------------------------------------------------