Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2003
    Posts
    11

    Unanswered: Limit current Users

    Hello all I am trying to write an app that limits the concurrent users. I have not done this before in SQL as most of my programming has been stateless.

    How do I connect to the DB at startup?

    How do I count the current users?

    thanks

    tal

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Refer to this Winnet Mag article.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jun 2003
    Posts
    11
    Thanks for the link. I Cannot use a Windows 2000 solution as I am not sure of the networks that my app will be running on. I was hoping to count the people logged into my SQL server.

    tal

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Then use SP_WHO2 from query analyzer
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Jun 2003
    Posts
    11
    I know about sp_Who, my problem is that I have always just connected to the database when I wanted to make a call in the following fasion:

    Open connection
    get data
    Close connection

    How can I open the connection for the entire application?

    I would like to open a connection similar to the one that is opened when you launch Query analyzer.


    thanks again

    Tal

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Keep the connection open all the times to the database may cause performance issues rather re-connecting from application code.

    I don't know what is the reason behind to do so, but once you apply connection to the server it will not get disconnected until you specify disconnect method from the code. SQL Server doesn't disconnect the client connection deliberately.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Jun 2003
    Posts
    11
    Originally posted by Satya
    Keep the connection open all the times to the database may cause performance issues rather re-connecting from application code.

    I don't know what is the reason behind to do so, but once you apply connection to the server it will not get disconnected until you specify disconnect method from the code. SQL Server doesn't disconnect the client connection deliberately.

    I am trying to only allow a licensed number of clients... I also want the connection to die if there is a hard close of the client.

    How much of a hit is there if I keep the connection open at all times?

    thanks again

    tal

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    BTW what is the licensing mode currently on the server?

    By any chance you cannot assign SQL to close the connection if the client disappers from the network. Until unless you restart the services the connection will be shown as sleeping status.

    To know the performance bottlenecks you better deploy PERFMON and capture the counters.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Tal, SQL Server & NT handle license complience rather well. Could you explain your situation a little bit more?

    Have you thought of writing a stored procedure to check counts and idle connections? You could then schedule the sp to run at regular intervals. if you were out of complience on licenses or found idle connections you could send an e-mail or remidy the problem.
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Jun 2003
    Posts
    11
    O.k. Maybe I was not specific enoughso here is the scenario:

    I work for a small company that sells a piece of software that is backended on a SQL server. Currently, the software is sold on a per client basis. We would like to make the software licensed on a total concurrent user basis.

    Currently, I open connection, do data stuff, close connection.

    This obviousely would not work as the connections are only open for a few seconds at a time.

    My plan is Open a connection
    Count Currently open connections

    return false if the Licenses are all filled up
    Message the user that the licensed copies are in use
    Close the Connection

    -OR-

    Return true if there is a connection available
    Leave the connection open until application close


    Will this Scenario Work?

    tal

  11. #11
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Tal, IMNSHO, I think you are on the right track with your last post. I think keeping a connection open for a short period of time COULD be a good thing.

    I would hold the connection open for as long as needed and then close the connection. This allows more users to use a smaller number of licenses, just don't close a connection before your work is done!
    Paul Young
    (Knowledge is power! Get some!)

  12. #12
    Join Date
    Jun 2003
    Posts
    11
    I want to plug up the licenses, We sell them for a few hundred a piece. what are the drawbacks to leaving an ADO connection open for 30 minutes?

    tal

  13. #13
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    To my knowledge, none. If you need to be connected for 30 min. that's what you need to do. You do take a memory hit for each connection but an idle connection isn't going to hurt your server.
    Paul Young
    (Knowledge is power! Get some!)

  14. #14
    Join Date
    Jun 2003
    Posts
    11
    Cool Thats what I thought....
    thanks

    tal

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •