| Author |
Topic |
|
MediaPirate
Yak Posting Veteran
52 Posts |
 Posted - 2003-08-14 : 13:44:51
|
| Is there a way to limit concurent connections per users when connecting using a DSN? I have a problem with clients making 90 connections to the server while others "who are much higher traffic site" using 10 or less. I currently have the ODBC set for connection pulling, but I would really like to find a way to limit their connections to say 10 or to force drop a connection when it's not in use. Any help would be appreciated. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-14 : 14:23:09
|
| The best (well, second best) way to handle this is to set up a job that runs periodically and kills any processes that meet your criteria (10 or more connections from one machine, certain users, etc.) You can use sp_who, sp_who2, or query the master..sysprocesses table to get this data. From there, you need a little dynamic SQL to create the KILL commands and run them.A better solution, although less popular, is to set up each user with their own password (or use Windows Authentication) and watch for people who connect too often. Then you can lock out their login separately from everyone else's. As long as they abuse the system, you lock them out. If they whine about needing to get work done, mention the OTHER people who also have to get work done, and manage to do it with fewer connections. This will also be more difficult to enforce if everyone is sharing the same login and password (always a bad idea, now would be a good time to change that if you're doing it now) |
 |
|
|
MediaPirate
Yak Posting Veteran
52 Posts |
Posted - 2003-08-14 : 14:48:39
|
 This isn't even the highest, I want to restrict people for having a maximum of 10 or 15. I was hoping I could change something programmaticlly in the DSN. Maxconnect=10 or something.. :-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-14 : 15:53:43
|
| Are you having performance problems related to a lot of connections open? SQL Server can support lots of connections. If you limit the number of connections, won't people be blocked?Tara |
|
|
|
MediaPirate
Yak Posting Veteran
52 Posts |
Posted - 2003-08-14 : 16:08:29
|
| Tara- Yes, we are having performance issues. They're not great, but it mostly applies to those who are trying to connect using E.M. I've migrated most of these people so they're using the internal NIC from their website. However, I've noticed that when there's a lot of connections more people get the time 'no items' error when connecting with E.M. I looking for a way to throtle those who have more than 20 connections. I don't want to block access, just limit to them to a specific number. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-14 : 16:58:50
|
| Why are people using multiple connections to Enterprise Manager? Enterprise Manager does not do this. If people are having problems with Enterprise Manager, why don't they just use Query Analyzer? Enterprise Manager is a resource hog, which is probably the problem.You will not be able to throttle this number except by doing something like Rob mentioned. You can limit only the total number of connections and not per user inside SQL Server (done by modifying the license number). What you probably should do though is educate people.Tara |
|
|
|
MediaPirate
Yak Posting Veteran
52 Posts |
Posted - 2003-08-14 : 18:28:37
|
| Tara- If this were a SQL server for internal use only then I'd smack the user around a bit. However, these are customers using databases for their websites they host with us. I would drop the support for EM all together but that's not going to help retain business. I myself prefer QA. There's about 350 users on this SQL server and most of them have between 1-15 connections, but theres a few who have more than 50 and I know it's something to do with their programming, I was hoping to find a way to fix it on the server. Thank you! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-14 : 18:33:49
|
| If you can't find a way to fix it, then you're gonna need to upgrade the hardware to support the multiple connections. RAM upgrade for sure, processor upgrade maybe. You could charge the customer more for using so many connections that way they pay for the upgrade!Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-08-14 : 19:31:03
|
| I think this solution would work if you had less users, but here it is anyway, in case you can find a use for it.If you had a SQL server instance for each user, I think that you can configure the max connections for that instance. Needless to say this will use a LOT of RAM, esp. for 350+ customers.I suspect that the problem you have is poor application design / implimentation on your customer's websites.I just thought of another way that might help your situation. If you have a router / firewall maybe you can set it up to only allow X number of connections from a particular IP. That would be easy to setup, but a bear to maintain methinks. You could set it up in such a way so that ALL outside users are only allowed X number of connections to your servers from a single IP.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
Limiting connections per login