1

If I install SQL Server Express 2017 on Windows Server 2016 Essentials, what is the maximum number of users that will be able to connect concurrently across the network to the SQL Server Express instance?

Is is unlimited or does the operating system's limits of 25 users / 50 devices then kick-in?

Also, how does the o/s count concurrent users? e.g. if we have 2 users on different computers connecting to the SQL Server service using the same SQL Server credentials, will the o/s count them as 1 user on 2 devices (hence we can, in theory, get 50 users connecting to the database), or will they be counted as 2 users on 2 devices?

Note that I am not a DB administrator nor a network specialist, just a developer trying to install my first server and trying to understand whether the o/s connection limitations per user only apply to built-in operating system services (like NTFS file sharing) or whether they also affect connections to 3rd party application services like SQL Server Express.

e.g. if I install SQL Server Express on a Windows 2016 server that has 5 user CAL only, will the limit on concurrent connections be the 32K (approximately) set by SQL Server Express or will it be the 5 connections limit set by Windows Server?

Thanks.

  • The limit is basically how many concurrent requests you can deal with given your hardware setup (it's express edition so only so many cores/memory can be used) and the way your application works. – Sean Gallardy Nov 28 '18 at 23:56
  • 2
    Possible duplicate of Max number of user connections – George.Palacios Nov 29 '18 at 9:03
  • @SeanGallardy thank you. I updated the question to clarify that I am trying to learn whether the o/s CAL also dictates connection limitations to SQL Server Express. – Tech-On-the_Beach Nov 29 '18 at 14:57
  • @George.Palacios I did find and read many similar posts asking about license and connections to SQL Server and Windows Servers individually. However, non unambiguously explained the effect of the Windows Server o/s CAL licenses on connections to SQL Serer Express. – Tech-On-the_Beach Nov 29 '18 at 15:04
1

@@MAX_CONNECTIONS

The @@MAX_CONNECTIONS function will according to docs:

Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.

So the above will give us some value the SQL Server is or was limiting the number of connection to.

On most servers this will likely return the default value of 32767.

Another two ways to access the value of the setting are as follows:

SELECT value FROM sys.configurations
WHERE name = 'user connections'

Or

USE master;  
GO  
EXEC sp_configure 'show advanced option', '1';  
GO
RECONFIGURE
GO
EXEC sp_configure @configname = 'user connections';
GO
EXEC sp_configure 'show advanced option', '0';  
GO
RECONFIGURE
GO

Why would I want to change this option

As I would suggest is the case with most advanced options, if you're asking the question it's unlikely you need to. The use cases are likely far between, but in general this setting can be used to limit the impact users are having on a server in a very high level sense.

  • thank you. Are you saying that the operating system's CAL limitation will not affect how many people will be able to concurrently connect to SQL Server Express instance? e.g. I install SQL Server Express on a Windows 2016 server that has 5 CAL only, will the limit on concurrent connections be 32K (approximately) or will it be 5 connections? – Tech-On-the_Beach Nov 29 '18 at 14:02
  • @Tech-On-the_Beach Correct - you will not be limited to 5 users. – George.Palacios Nov 29 '18 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.