none
SQL 2005 sessions RRS feed

  • Question

  • Hi all,

    I have a .net 2.0 windows forms application (done with VS 2008 Pro), where I connect to SQL Server 2005 (Express edition for now) database using ADO.NET.

    I set-up DataSet(s) and configured ONE, permament MyConnectionString to access my SQL2005 database from within my app, where I persisted security info - in final release I'm planning not to persist login details directly, of course.

    I have one SQL2005 user/login set-up in the database, with permissions to access data - MyConnectionString is set to these login details/credentials.

     

    The app is now distributed along few users in my company, to run tests with real life data.

    Each worstation uses the same SQL2005 login details (SQL user/login), then - this is single-instance-application.

     

    In the SQL database, I take benefits of CURSORs (use @@FETCH_STATUS) and use CONTEXT_INFO too.
    I'm careful and don't use CURSORs nested one in another.

    -----

     

    1/. Is it safe to use one SQL login / user for more than one workstation (physical user) ?

     

    2/. What does a session mean at all ?

     

    3/. Does SQL server create a session (? or anything) for each workstation, even with the same login details ?

     

    4/. Is @@FETCH_STATUS and CONTEXT_INFO session-safe ?

     

    5/. How do I need to configure MyConnectionString / AdvancedProperties in VisualStudio properly:

    ? MARS

    ? Transaction Binding

    ? Pooling

    ? Context Connection

     

    Wednesday, August 13, 2008 7:39 AM

Answers

  • Hi

     

    Hard to answer detaily all of your questions here, you can dive a lot on each one. But I will give you the basics and feel free to ask more details

     

    1 - Yes, It is safe to use same login/user for many users. The only constraints would be licensing (your server is not XP Pro or Vista, right?; SQL Server Express doesn't have specific restriction on this), and performance (can your server handle the load for the number of users you have, without getting slow response to them?)

     

    2 - It basically means: a permanent connection between the client and the server, which you keep "open" so the results of a command (particularly, error messages) are still available for the next commands. You may say "the space between the open and the close calls"

     

    3 - Yes, there is a session with each workstation. Note that most of the session information lives on the client, even if the server has information for all, it is light enough to scale

     

    4 - Yes, they are variables per session; and they don't mix with other sessions.

     

    5 - You probably are fine with the default configuration:

    * MARS - keep it disabled, unless you need Multiple Asynchronous Results

    * Transaction Pooling - disabled, unless you have very specific transaction requirements

    * Pooling - Enabled (by default), but for your WinForm application (one connection per client computer) should not make a difference. The major difference is when you have multiple connections in the same client computer, for example a web server that may have multiple sessions against a server.

     

    There are topics on MSDN about the ConnectionString structure, you should read them if you need more data on the matter.

     

    Bruno

    Tuesday, August 19, 2008 12:41 AM

All replies

  • Hi

     

    Hard to answer detaily all of your questions here, you can dive a lot on each one. But I will give you the basics and feel free to ask more details

     

    1 - Yes, It is safe to use same login/user for many users. The only constraints would be licensing (your server is not XP Pro or Vista, right?; SQL Server Express doesn't have specific restriction on this), and performance (can your server handle the load for the number of users you have, without getting slow response to them?)

     

    2 - It basically means: a permanent connection between the client and the server, which you keep "open" so the results of a command (particularly, error messages) are still available for the next commands. You may say "the space between the open and the close calls"

     

    3 - Yes, there is a session with each workstation. Note that most of the session information lives on the client, even if the server has information for all, it is light enough to scale

     

    4 - Yes, they are variables per session; and they don't mix with other sessions.

     

    5 - You probably are fine with the default configuration:

    * MARS - keep it disabled, unless you need Multiple Asynchronous Results

    * Transaction Pooling - disabled, unless you have very specific transaction requirements

    * Pooling - Enabled (by default), but for your WinForm application (one connection per client computer) should not make a difference. The major difference is when you have multiple connections in the same client computer, for example a web server that may have multiple sessions against a server.

     

    There are topics on MSDN about the ConnectionString structure, you should read them if you need more data on the matter.

     

    Bruno

    Tuesday, August 19, 2008 12:41 AM
  • Thanks for your clear answers !
    Wednesday, August 20, 2008 12:12 PM