SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


3-Node, 3-Instance SQL Cluster -- Static IP address and Port 1433 question


3-Node, 3-Instance SQL Cluster -- Static IP address and Port 1433 question

Author
Message
jpSQLDude
jpSQLDude
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 939
I searched hard and many people have a similar question, but I cannot find a clear answer. I have a 3-node Windows Server 2008 R2 cluster, with 3 Named Instances of SQL Server 2012 w/SP1. All 64-bit. I only used static IP addresses for everything, including the SQL Instances.

I thought the 3 SQL Instances would use Port 1433 because there are 3 separate named instances, with 3 separate static IP addresses.

But no: each Instance has a dynamically assigned port, and therefore won't work because we have firewalls. Shouldn't each Instance just be listening on Port 1433? Can I make each Instance listen on Port 1433?


Here are all the details for one of the Instances:

In Cluster Manager, under Services and Applications, I click on InstanceA, then underneath the Server Name there is the static IP address I assigned when installing this Instance. Good so far.

Under SQL Server Configuration Manager, under SQL Native Client 11.0 Configuration (32-bit), under Client Protocols, under TCP/IP the Default Port is 1433. Also good.

Under SQL Server Network Configuration I see my 3 Instances. I click on Protocols for InstanceA, then under TCP/IP there are a lot of IP addresses. Under the IP address that corresponds to the one under Cluster Manager, TCP Dynamic Ports = 0, and under TCP Port it is blank. Uh oh! Also, all the way at the bottom under IPAll it says TCP Dynamic Ports: 61024, and TCP Port again is blank. This is where I thought I would just see Port 1433.

Now in SQL Server Management Studio, under SQL Server Logs, there is an entry (I hid the IP address, but it is the same as in Cluster Manager):

Server is listening on [ 000.000.000.000 <ipv4> 61024].

So there is that same non-standard, dynamically-assigned port.

So again, why isn't this Instance using Port 1433? Shouln't it be?

How can I make all 3 Instances listen on Port 1433?

Thanks in advance!



BTW, I read all of these, none seems helpful:

http://support.microsoft.com/kb/823938
http://support.microsoft.com/kb/318432
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
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73361 Visits: 40967
I think you'll have to modify each instance to listen to one IP address; then change the port on that one IP address so that it's 1433.

no instance should listen to all three ip addresses in this case, I think: only ONE instance can listen to 1433 on a given IP address.

this screenshot's not 100% representative of your setup, with three static IP's, you probably have IP1, IP2,IP#,IP4 and IPALL, but it gives you the idea.

for instance 1, you'd want to set it up on IP1, and set the port on that IP to 1433.
then repeating for each instance.



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73361 Visits: 40967
whoops i didn't read your quesiton completely. ; i was assuming a non-clustered setup;, i thought we were talking single server/3 ip's / 3 instances.

my mistake i think.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1261 Visits: 599
in our clustered environment, under sql server config, we set our IP's (we have 3 per box) in as normal... and we set IPAll to show port 1433. so exactly where the above poster mentioned is where we set our TCP Port and yes we use static IPs. we leave 1433, 1434 open and 5022 on mirrored databases.

.
jpSQLDude
jpSQLDude
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 939
Bill (fluffydeadangel) (3/28/2013)
in our clustered environment, under sql server config, we set our IP's (we have 3 per box) in as normal... and we set IPAll to show port 1433. so exactly where the above poster mentioned is where we set our TCP Port and yes we use static IPs. we leave 1433, 1434 open and 5022 on mirrored databases.

When you did your installation of the Clustered SQL Instances, did they default to using Dynamic Ports, and you had to manually change them to use static ports, then set each one to 1433?

If so, I guess I just don't understand why SQL would default to dynamic ports when doing a Cluster installation? Is this the normal behavior? Again, my three Clustered Instances each has its own static IP address and it is a Named Instance, so I thought it would just install using the default port.

<baffled>w00t</baffled>
brightsideofthemoon
brightsideofthemoon
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 3
As installed, default instances of SQL Server ('MSSQLSERVER') use TCP port 1433 but named instances will use dynamic ports. These can be reconfigured using Configuration Manager.

Commonly on Active-Active clusters I'll use static ports from 1450 and upwards, assigning a port to each instance. You can use any range you choose though, and indeed I think you can use the same port for each instance as long as the IP address is different, it's just a preference of mine to use sequential ports for each instance.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55134 Visits: 17708
jpSQLDude (3/28/2013)
I searched hard and many people have a similar question, but I cannot find a clear answer. I have a 3-node Windows Server 2008 R2 cluster, with 3 Named Instances of SQL Server 2012 w/SP1. All 64-bit. I only used static IP addresses for everything, including the SQL Instances.

I thought the 3 SQL Instances would use Port 1433 because there are 3 separate named instances, with 3 separate static IP addresses.

But no: each Instance has a dynamically assigned port, and therefore won't work because we have firewalls. Shouldn't each Instance just be listening on Port 1433? Can I make each Instance listen on Port 1433?


Here are all the details for one of the Instances:

In Cluster Manager, under Services and Applications, I click on InstanceA, then underneath the Server Name there is the static IP address I assigned when installing this Instance. Good so far.

Under SQL Server Configuration Manager, under SQL Native Client 11.0 Configuration (32-bit), under Client Protocols, under TCP/IP the Default Port is 1433. Also good.

Under SQL Server Network Configuration I see my 3 Instances. I click on Protocols for InstanceA, then under TCP/IP there are a lot of IP addresses. Under the IP address that corresponds to the one under Cluster Manager, TCP Dynamic Ports = 0, and under TCP Port it is blank. Uh oh! Also, all the way at the bottom under IPAll it says TCP Dynamic Ports: 61024, and TCP Port again is blank. This is where I thought I would just see Port 1433.

Now in SQL Server Management Studio, under SQL Server Logs, there is an entry (I hid the IP address, but it is the same as in Cluster Manager):

Server is listening on [ 000.000.000.000 <ipv4> 61024].

So there is that same non-standard, dynamically-assigned port.

So again, why isn't this Instance using Port 1433? Shouln't it be?

How can I make all 3 Instances listen on Port 1433?

Thanks in advance!



BTW, I read all of these, none seems helpful:

http://support.microsoft.com/kb/823938
http://support.microsoft.com/kb/318432
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



In this scenario I generally set each named instance to listen on a unique statically assigned port above 1024. I'll then have the firewall administrator open the following ports

TCP 1433, 1434 plus any statically assigned ports i have used for named instances
UDP 1434

It's generally better to leave just the default instance listening on port 1433 as the sql browser assumes this port for the default instance.

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search