Connections

Question

Connections

Sioban Krzywicki

One Orange Chip

Points: 27770
More actions
April 27, 2018 at 9:12 am

#344863

There are 32,767 allowable connections per SQL Server instance.
If I have an Always On Availability Cluster with 3 nodes, does this increase the number of allowable connections or is the entire cluster considered a single instance where connections are concerned? Like if I set up the cluster so all writes go to one node and all reads to another, is that 32,767 available connections or is it 65,534 connections?
If I have an Always On read only instance outside the availability cluster, does that instance get its own pool of available connections?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Viewing 4 posts - 1 through 3 (of 3 total)

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

crow1969 Hall of Fame Points: 3001 More actions · Answer 1

An Always On group is composed of several instances that don't directly share any resources. I think you would be able to have more than 32,767 connections. If you have that many connections, you may have to examine how connections are created, released, and or reused. If you have a different connection string per page on a website, you will get multiple connection pools from an IIS server, which can multiply the number of connections you are seeing, but 32,000 connections is very (nearly astronomically) high.

jcarranza 23978 Ten Centuries Points: 1380 More actions · Answer 2

crow1969 - Monday, April 30, 2018 8:24 AM
An Always On group is composed of several instances that don't directly share any resources. I think you would be able to have more than 32,767 connections. If you have that many connections, you may have to examine how connections are created, released, and or reused. If you have a different connection string per page on a website, you will get multiple connection pools from an IIS server, which can multiply the number of connections you are seeing, but 32,000 connections is very (nearly astronomically) high.

So if you are pointing applications to the Listener and that points to the Active Server then you have 32,767 connections.
If you have readable secondaries then the Active should have 32,767, the secondary 32,767 and all other ones should have 32767..

So if depends.

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

Each instance is limited to the max number of connections. If you connect to a readable replica, that has it's own set of connections, as noted above. However, if the secondary replicas are not readable, then you only have the number for one instance.