Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This is partway setup, but not sure what I've been asked to achieve to 100% possible.

1 server
2 instances of SQL Server
1 internal IP for each sql instance
1 external IP for each sql instance

Both instances are installed and working for remote access from a seperate IP, so to connect via SSMS for example just type in the external IP, user, password and it connects.

SQL Instance 1

Internal IP: 192.168.0.9
Using above IP from the actual server the two instances are on, this connects as expected

SQL Instance 2

Internal IP: 192.168.0.10
Again connecting using above IP w/ correct username and password, from the SAME box the two instances are running from works fine

If you try to connect to Instance 2 from another machine on the same network, then it points to SQL instance 1 instead of 2. The only way to connect is to use the IP\InstanceName

Is it possible to have them be accessible from their own IP if they are setup on the same box, or do you HAVE to use the instance name. It works fine from the box the instances are installed on, just not from other machines on the network

share|improve this question

migrated from stackoverflow.com May 23 '11 at 6:49

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 3 down vote accepted

IP address or DNS names only resolve to server.

On the server itself, the call is routed by instance name or port number to the relevant instance

Server\Instance2
Server,1234
Server\Whatever, 1234 -- port overrides instance name

You can set up client aliases per client machine but this is a management overhead and I wouldn't recommend it.

So basically, no, you can't bind a SQL Server instance to an IP address. You need instance name or port number.

share|improve this answer

Haven't tested this myself, but this might help. The alternative approach is to leave the port the same (1433) and use a second IP address

1.First of all, add your additional IP addresses on the host machine

2.Configure SQL Server to use the new IP

SQL Server Configuration Manager is the tool to use. By using the 2008 version I can see the services & protocols for both the 2005 & 2008 instances on my machine.

By default SQL is listening on all ports and dynamically determining which to use. We need to stop this behavior i.e restrict a SQL installation to a single port on a single port so that instances can co-exist.

Under each instance open the properties of TCP/IP Set "Listen All" = No. Set the desired IP and port Disable listening on IPs other than your chosen main one. Disable dynamic ports by removing 'o' from TCP Dynamic ports for all IP's (except for 127.0.0.1 and "IPAll") Restart SQL services.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.