June 7, 2006 at 7:53 am
What can i use to find out how many users connect each day to each DB from IIS and .NET client.
I tries using sql profiler but can't decide which events to use.
How would you find out???
June 7, 2006 at 8:10 am
You either have to build something in the application to persist a value each time a connection happens, or use profiler.
But what is the real number you want?
Try Security Audit --> Audit Login
June 7, 2006 at 8:18 am
Alex,
You only need to capture the Audit Login event and you can also put in a filter on the application column so that you only capture login events from those two applications.
For .NET, the filter you need is ".NET SqlClient Data Provider". Can't remember what the filter is for IIS.
June 7, 2006 at 8:57 am
thank you for responding.
June 9, 2006 at 12:06 pm
I believe SQL Profiler is your best bet. You can narrow down your criteria so that you are not tracing every single activity on the server.
June 9, 2006 at 1:13 pm
You may not accurately be able to tell from the database.
If your applications were designed to be scalable, they will take advantage of OLEDB/ODBC connection pooling. Multiple users will be using the same connection (especially on the IIS application), and/or one user instance will having multiple connections (especially from desktop and middle-tier applications).
Trying to count user instances from the database side using any resource technique on the database server will likely not give you any meaningful number (unless you are just trying to count connections). The only way I can think if is if there is a table or query that is accessed once (and exactly once) per user session, then you can count that query. But counting connections likely will not work.
It likely will have to be built into the application to count how many times it is started, and logged that to a table or logfile on the server.
Hope this helps
Mark
June 11, 2006 at 11:47 am
I've tried using profiler,but the numbers are not accurate because of all our .NET apps using connection pooling.
I've downloaded SQLSPY (Freeware).
With this app i can at least know which db is being used the most and how many users are connected to the server.
Thanks to all.
June 11, 2006 at 7:20 pm
Did you install SQLSPY on the the server or does it from a server with sql server client?
June 12, 2006 at 6:41 am
Do your users call a login proc? Pretty common for something like this to be called once per session and you could either just count the calls to it, or add a line to the proc to log the login (which is also commonly done already). Connection pooling doesn't affect this because it has to be called per user even if on the same connection.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply