How many users can be configured(MAX) for SQL Server 2008 R2

Question

How many users can be configured(MAX) for SQL Server 2008 R2

parthi-1705

SSCrazy Eights

Points: 9277
More actions
January 28, 2011 at 2:38 am

#246021

Hi,
Maximum no of user connections is 32767(@@MAX_CONNECTIONS)
In that it is said as:
The actual number of user connections allowed also depends on the version of SQL Server that is installed and the limitations of your applications and hardware.
so how many Maximum no of users can be connected to SQL Server 2008 R2
To the max how many users it can be configured based on H/w , RAM ..etc
Thanks
Parthi
Thanks
Parthi

Viewing 9 posts - 1 through 8 (of 8 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734587 More actions · Answer 1

That is the maximum number. They are saying that the performance of your workload, based on hardware, will determine this.

There is no rule that says xx users can be connected on a box with yy ram or zz CPUs. One user can overload a server, or 10,000 can run fine. It depends on what they are doing.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

Hi,

It says that Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server is 32767.

At a single point of time say @ excatly 12:00:00:123 PM today if 100,000 users access SQL server it will access or not

say all of them are viewing their results exactly at same whether 100,000 users will be able to access at 12:00:00:123 PM

or only 32767 users will allowed @ 12:00:00:123 PM.Beyond 32767 (remaining)they can be able to view only at 12:00:00:378 PM or later

i heard some time back that beyond 32767 users are not allowed.to get clarify i posted here.

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

Thanks

Parthi

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The maximum allowed number of concurrent connections is 32767. So no, 100 000 users will not all be able to connect at the same time.

That said, unless you have some seriously powerful hardware, you're going to be having problems far below 32767 concurrent connections.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734587 More actions · Answer 4

As Gail says, that's the maximum number of connections, period.

I would doubt that you would have 100,000 users all connecting at the same time to the same database. If so, you haven't architected your application well. Connections to a website do not necessarily mean connections to a database. If this is an application, you are not going to be able to support 100,000 users on one SQL Server.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Steve Jones - SSC Editor (1/28/2011)
Connections to a website do not necessarily mean connections to a database.

Especially if there's connection pooling.

Seriously, the largest number of concurrent connections I've seen to a single SQL Server was about 5000. If you're expecting 100 000, I'm curious to know what you're writing, the next Facebook?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

GilaMonster (1/28/2011)
Steve Jones - SSC Editor (1/28/2011)
Connections to a website do not necessarily mean connections to a database.
Especially if there's connection pooling.
Seriously, the largest number of concurrent connections I've seen to a single SQL Server was about 5000. If you're expecting 100 000, I'm curious to know what you're writing, the next Facebook?

😎 Cool 😎 i just want to know what will happen if that much of users try to access at a time.

In case of google or bing or...some other main sites how the users will be maintained in case of they are using SQL server.I kept in mind of google and asked as 100,000 users ...

Thanks

Parthi

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Google doesn't use SQL Server. They have a custom distributed database system.

The one big internet site that uses SQL Server is MySpace. They have a couple hundred SQL Server instances, if not more. Partitioning, replication, service broker, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 8

My company have recently reached the limit of connections on a couple of occasions during peak operating capacity and we've now looking at scaling out again (4th time).

There are only a few physical servers on the market that will handle that kind of capacity so unless you scale out you will likely hit issues well below the 32767 max integer value.