Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

3-Node, 3-Instance SQL Cluster -- Static IP address and Port 1433 question Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 1:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:08 AM
Points: 119, Visits: 928
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


Post #1436707
Posted Thursday, March 28, 2013 2:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 12,917, Visits: 32,083
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1436746
Posted Thursday, March 28, 2013 2:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 12,917, Visits: 32,083
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1436749
Posted Thursday, March 28, 2013 4:47 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
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.

.
Post #1436789
Posted Friday, March 29, 2013 8:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:08 AM
Points: 119, Visits: 928
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></baffled>
Post #1436961
Posted Monday, January 20, 2014 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 4:23 AM
Points: 1, 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.
Post #1532729
Posted Monday, January 20, 2014 10:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 6,358, Visits: 13,688
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"
Post #1532744
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse