Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 @@MAX_CONNECTIONS

Author  Topic 

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-07-23 : 22:10:32
The user connections option specifies the maximum number of simultaneous user connections that are allowed on an instance of SQL Server.

SQL Server allows a maximum of 32,767 user connections.



1. Just to be sure, how do you define an instance of SQL Server?
2. How many instances of SQL Server can you have one on computer?
3. How can you have 32,767 connections in one instance of SQL Server?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-24 : 01:48:52
1. Single instance will mean a separate installation of sql server edition. It will have its own separate services in machine for database, reporting, integration services etc depending on components chosen
2. maximum of 50 instances but please be aware the instances will share the memory of server so unless you've really good amount of RAM dont think of putting so much instances in the same server.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

3. Connection can be connections opened through SQL management studio, through ODBC, through application etc. So depending on total number of applications, people etc trying to connect, the instance can handle upto 32,767 connections.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-07-24 : 18:00:57
quote:
Originally posted by visakh16

1. Single instance will mean a separate installation of sql server edition. It will have its own separate services in machine for database, reporting, integration services etc depending on components chosen
2. maximum of 50 instances but please be aware the instances will share the memory of server so unless you've really good amount of RAM dont think of putting so much instances in the same server.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

3. Connection can be connections opened through SQL management studio, through ODBC, through application etc. So depending on total number of applications, people etc trying to connect, the instance can handle upto 32,767 connections.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thank you visakh16. This really helps. In your first answer, you mention "It will have its own separate services...."

Can you give some examples of the names of the services you are referring to?

Also, just to be sure, are each of those services an instance as well or a connection?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-24 : 18:29:45
SQL Server Agent, SQL Server Reporting Services etc. are (Windows) services that are running independently on your server box, but are associated with a given instance of SQL Server installation.

Those services are not connections in themselves, although they may make connections (to the database engine).

A SQL Server instance is a collection of services, the big enchilada being the Database Engine. When you run a query, you connect to the database engine. When Reporting Services wants to run a query to create a report, that again makes a connection to the database engine. The 32,767 is the maximum number of such simultaneous connections an instance of SQL Server Datbase Engine can support.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-25 : 04:38:14
quote:
Originally posted by Rock_query

quote:
Originally posted by visakh16

1. Single instance will mean a separate installation of sql server edition. It will have its own separate services in machine for database, reporting, integration services etc depending on components chosen
2. maximum of 50 instances but please be aware the instances will share the memory of server so unless you've really good amount of RAM dont think of putting so much instances in the same server.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

3. Connection can be connections opened through SQL management studio, through ODBC, through application etc. So depending on total number of applications, people etc trying to connect, the instance can handle upto 32,767 connections.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thank you visakh16. This really helps. In your first answer, you mention "It will have its own separate services...."

Can you give some examples of the names of the services you are referring to?

Also, just to be sure, are each of those services an instance as well or a connection?


The services are

SQL Server services
SQL Server Reporting Services
SQL Server Integration Services
SQL Server Analysis Services
SQL Server Agent
SQL Server Browser
SQL Server Full Text Search
SQL Server VSS Writer
SQL Server Active Directory Helper

you might also need

Distributed Transaction Coordinator (DTC)

for implementing distributed transactions as in SSIS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -