configure single SQL server 2005 server to listen on 2 different IP addresses

Question

configure single SQL server 2005 server to listen on 2 different IP addresses

dallas13

SSCertifiable

Points: 7491
More actions
June 12, 2014 at 10:37 am

#396904

I asked my windows team to configure 2 different IP addresses for one windows 2003 server. And I have it.
I have only 1 SQL Server called 'dev\sql2005' installed on that server.
Can I connect to this instance using different IP addresses? IF yes then how to configure that?
If no then why not?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 1

No.

The IP address for the SQL Server Instance is the IP address of the server. The physical server will only have one IP address. If you have multiple SQL Instances on the server, the only difference is the port of the sql server instance.

Edited:

Although how many NIC's are on the server?

Steve

dallas13 SSCertifiable Points: 7491 More actions · Answer 2

Thanks alot Steve.

We requested our Network team to create one more DNS entry and hence we have total 2 I\P for that box.

In future we will have 3 instances on one server and default instance can be connected using box IP address but we want to create 2 additional IPs for other 2 instances.

I also came across this and tried case -1 but not working in my case,

http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 3

What are you looking to gain by doing it this way? Are each IP on a different NIC?

For the additional instance, the connection would just be:

myservername

myservername\sql2

myservername\sql3

or

myservername,portforinstance1

myservername,portforinstance2

myservername,portforinstance3

or

myservername,port1

myservername\sql2,port2

myservername\sql3,port3

or the above replacing "myservername" with the one IP address.

I guess I'm trying to learn the benefits of what you're trying to achieve.

Steve

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

Are each IP on a different NIC? --->I am not very sure as not much knowledge but what I know is we will have 3 different IP addresses for the same box.

For the additional instance, the connection would just be:

myservername

myservername\sql2

myservername\sql3

-- My server name is dev-001\sql2 -- it has dash and slash so from .NET point of view its not good so they want different IP address for each instance. they dont want to use Alias because they have to create it on each box at their end.

or

myservername,portforinstance1

myservername,portforinstance2

myservername,portforinstance3

or

myservername,port1

myservername\sql2,port2

myservername\sql3,port3

or the above replacing "myservername" with the one IP address.

I guess I'm trying to learn the benefits of what you're trying to achieve.

So I am not sure if someone has done this or its doable but We need to configure 3 different IPs for all 3 existing instances.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 5

I'm in a .net shop and there are never issues with the named instances for connections. The connections are usually in the web.confg or appconfg files but who knows what their trying to do.

"dev-001\sql2" that looks like a named instance, rather than a servername. Was that registered in DNS? I didn't think DNS allowed a "\" in the name?

If it's the instance name,

You can still do it with just one IP address and not need the "-" in your name or the "\" for the named instance, just use the IP and port.

10.11.12.172,3258 (whatever fixed port you assign to the default instance)

10.11.12.172,3359 (whatever fixed port you assign to the named instance1)

10.11.12.172,3482 (whatever fixed port you assign to the named instance2)

You can always have another DNS name pointing to "dev-001", have them call it something else.

dallas13 SSCertifiable Points: 7491 More actions · Answer 6

Okay So that works in config file.

Does this same format also works inside ur codes that u write using VB,C#,ADO.NET etc ??

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 7

Yes, it would work in the code. They are establishing the sql connection inside the code and provide the server name parameter to the connection string. There shouldn't be any issues doing it in code.

dallas13 SSCertifiable Points: 7491 More actions · Answer 8

Okay Thanks a lot Steve, IF you know any link that demonstrate the same using some example or if u can show me some example then I really appreciate it. And Thanks a lot for all ur help.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 9

You're welcome.

The developers should know how to do this, or figure it out.

Here's the class that they would use in their code:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection(v=vs.110).aspx

Good luck