SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 @@MAX_CONNECTIONS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rock_query
Yak Posting Veteran

52 Posts

Posted - 07/23/2013 :  22:10:32  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/24/2013 :  01:48:52  Show Profile  Reply with Quote
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

52 Posts

Posted - 07/24/2013 :  18:00:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 07/24/2013 :  18:29:45  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/25/2013 :  04:38:14  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000