June 8, 2009 at 7:48 am
Is there anyway to enforce the number of client connections? For example, I purchase 20 user CAL's and only every want 20 user connections to the server? I have never seen this functionality in SQL Server however we do have some other database technologies that provide that through and ini file or settings file.
June 8, 2009 at 10:49 am
you could probably do this through some kind of logon trigger whereby you count the number of current logins and then either roll back the logon if its 20 or more, otherwise let the logon occur. Other than that i would say your best chance is via your application (assuming its an inhouse app you can change) and you can manage logins through there.
June 8, 2009 at 11:54 am
I suppose my biggest question would be why you'd want to... Granted this is coming from someone used to dealing with a poorly written vb6 app, but at any point in time one of my clients could have 1-4 connections open at the same time as various processes complete.
From another viewpoint, how many times have you had more than one query open in SSMS?
Currently I'm working in 5 different tabs in SSMS. That's 5 connections plus the connection to tempdb and msdb that Management studio required to display the current connections.
You didn't define which type of CALS you have, if they are user CALS 1 user might have 10 connections but they only count as using 1 CAL. If they are device CALS it's a bit different 'cause you need one for each device connecting to your server, but my machine running SSMS is currently using 7 connections but would again only count as 1 CAL.
1 CAL does NOT equal 1 Connection.
-Luke.
June 8, 2009 at 12:01 pm
Just realized I never actually gave you the answer... Again I'm not sure why you'd want to set it as low as 20, but in SSMS it's in the server properties on the connections tab. The default 0 is unlimited.
-Luke.
June 8, 2009 at 12:08 pm
there is a setting that controls this under sp_configure 'user connections'
However it would be highly unusual to set this to a fixed value. By default it is a dynamic value and SQL allocates more connections as required up to a max of 32,767.
SQL users a certain amount of memory per connection, if you fix the no. of connections the memory will be allocated for that amount of connections whether they are used or not, so you could make things worse.
My advice is don't bother.
---------------------------------------------------------------------
June 8, 2009 at 12:32 pm
I guess where this came from was a user group wanting to purchase a server license with a limited number user CAL's (20). Personally I think this is much too little an amount but they did not want to foot the bill for processor based licenseing. That being said one of the other systems we use has a Progress database backend and the number of connections is limited by your license file you are provided so this ensures no violation. I asked if there was any way to mimic this behavior to prove that they indeed need more CAL's than they want to purchase.
Thanks for the feedback everyone, much appreciated as always.
June 8, 2009 at 12:48 pm
Again it all depends on what the user group will be using this for, but along with the not every connection = a CAL bit, think about how the front end is accessing the db. If this is for a web interface or something, you could perhaps play with the connection pooling options to force a 20 user limit.
-Luke.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply