How many users are supported on SQL Server 2012 Express?

Question

I would like to say that i have gone through some of the sql blog and MSDN BOL, but still there is some confusion in mind like.

1. The limitation of SQL Server 2012 Express is 10 GB. It is the limitation of instance based or per database based?

2. No. of connections for SQL Server 2012 Express: the number of connections is limited or unlimited.

   If I shall use Server version of OS then what can be the max concurrent user connections.

3. The limitation comes from DB level or OS level.

4. If I shall use Windows Server 2012 R2 with SQL Server 2012 Express SP1 then what will be the max concurrent connections?

5. How many databases can we keep under the hood of single SQL Server instance.

Any help or suggestion would be appreciated

Answer 1

1. The limitation is for database not for instance. Plus 10G limitation is ONLY for data file, the log file can be 10.5 G or 20G for example.

2. As per this BOL document it says that

The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that unlimited user connections are allowed

There is no LIMIT set as such for express edition. However when connecting from application connection pooling comes into picture, if enabled. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established.

3. Yes number of connections are limited by the OS you are using. Please refer to this thread which says only 20 connections are allowed on windows 7. I am not sure about other client OS but if you use server OS there is no such restriction.

4. Unlimited for practical purpose. There is no restriction as such if your OS is Windows server 2012.

5. 32767 databases is what maximum capacity specification states. But you wont be able to go far than 10 due to express limitation on memory and CPU

• Sunzi: The maximum capacity specification states: 50 instances, not databases. Databases are more or less unlimited, although, as you stated, with the express limitations on ressources you can't have much.
• Md Haidar Ali Khan: @Shanky,+1 for to the point answer. You have saved my almost of a day.