Accessing Named Instances

Question

Post reply

Accessing Named Instances

Jeff Tilkes

Right there with Babe

Points: 747
More actions
May 2, 2008 at 12:15 pm

#123712

If I have 3 named instances of sql 2005 standard 64bit on one server (2003 r2 64bit) is it possible to setup a separate IP to access each on port 1433.
example:
Server/Instance111 accessed on IP 10.1.8.101 port 1433
Server/Instance222 accessed on IP 10.1.8.102 port 1433
Server/Instance333 accessed on IP 10.1.8.103 port 1433
The reason I want to do this is that we are consolidating multiple SQL servers onto one and we want to avoid having to update every client.
The clients already know to go to the IP of the old servers on port 1433, we want to use those IPs on the new server with the multiple instances we are going to be setting up to isolate the applications.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

No, each instance needs it's own IP. Really own socket, so a separate port.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Request these IPs from your Network Admin and configure your Windows server in Advanced TCP/IP settings to accept these IPs - your network admin knows how to do that.

Once IPs are on the server, test them.

Try to use SQL Server 2005 configuration manager for each instance - Network Configuration - Protocols for MSSQL Server - TCP/IP and click IP Addresses tab Use your IPs and ports

Regards,Yelena Varsha

Jeff Tilkes Right there with Babe Points: 747 More actions · Answer 3

I have setup multiple IPs on servers before.

This is the piece I was missing:

Try to use SQL Server 2005 configuration manager for each instance - Network Configuration - Protocols for MSSQL Server - TCP/IP and click IP Addresses tab Use your IPs and ports

Thanks I will try this as soon as I have my box setup.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 4

The Ip will not be the issue, clustering uses virtual ips so you can multipe instances running on one physical node, but you are not going to be able to have more than one instance using port 1433.

Jeff Tilkes Right there with Babe Points: 747 More actions · Answer 5

I was able to setup 3 instances each on there own IP and each using port 1433.

I am concerned about the number of people who have said this cannot be done (both here and on other sites). Do you guys know of issues in setting up SQL 2005 this way?

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

Sorry, but from your original post it appeared you wanted to consolidate those 3 old IPs to one new IP with the same port. That can't be done.

If you put 3 IPs on the Windows host, meaning multi-homing, which lots of people don't like, then it works fine for 1433. Each IP/Port is a socket and each instance needs a separate socket.

Some people think multiple IPs cause issues, but I haven't seen it. Whether they're on one subnet or multiples, it's worked fine for me. It gets confusing to some admins, but it should work fine. IIS machines do this all the time, hosting lots of sites on separate IPs.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

Steve,

I am going to implement one IP - One Port 80 for multiple IIS websites using Host Headers:

http://support.microsoft.com/kb/190008

HOW TO: Use Host Header Names to Host Multiple Sites from One IP Address in IIS 5.0

Do you happen to know if it is possible in SQL Server too?

Regards,Yelena Varsha

Ashley Fawcett-Jones SSC Enthusiast Points: 148 More actions · Answer 8

If the server on which an instance of SQL Server resides is multi-homed, would you see entries for each i.p. address and port it is listening on to come up in the SQL error log? I have a SQL server cluster with each node multi-homed, however even if the i.p. and name resources are added as dependencies of SQL server, it does not listen on the other i.p. addresses it seems. Any ideas where I might be going wrong?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Ashley Fawcett-Jones (5/9/2008)
If the server on which an instance of SQL Server resides is multi-homed, would you see entries for each i.p. address and port it is listening on to come up in the SQL error log? I have a SQL server cluster with each node multi-homed, however even if the i.p. and name resources are added as dependencies of SQL server, it does not listen on the other i.p. addresses it seems. Any ideas where I might be going wrong?

You would need multiple distinct names. The name is going to be bound to a specific IP address.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ashley Fawcett-Jones SSC Enthusiast Points: 148 More actions · Answer 10

Yes, I did that. Any more ideas? Thanks for the swift response.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Ashley Fawcett-Jones (5/9/2008)
Yes, I did that. Any more ideas? Thanks for the swift response.

Hmm - I didn't have to work that hard at it. three separate Cluster groups, each with their own resources, etc... Each got their own IP's, names, and it just kind of worked.

Are you able to access the distinct instances by IP address, or does that not even work?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?