3-Node, 3-Instance SQL Cluster -- Static IP address and Port 1433 question
-
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
-
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! -
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! -
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.
.
-
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>
-
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.
-
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
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" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply