Session state: A session is defined as the period of time that a unique user interacts with a web application. Session state is a collection of objects, tied to a session are stored on a server.

Using SQL Server for ASP.Net session state: Once you start running multiple web servers for the same web site, the default asp.net session state, InProc, is no longer useful, as you cannot guarantee that each page request goes to the same server. It becomes necessary to have a central state store that every web server accesses.
SQL Server offers you centralized storage of a session state in a Web farm. It also offers the transactional capabilities that provide reliability to most relational database systems. You can use SQL Server to save a session. This process is not as efficient as InProc and StateServer mode, because you must store the information in a different process or on a different server. However, this option may be more efficient than using the aspnet_state service, depending on the actual workload and the database configuration. Once you start saving session state to a SQL database it will also persist through web server restarts and reboots.

Installing session state: Open ASP.Net 2.0 Command prompt located at Start–>Programs–>Microsoft .Net Framework SDK v2.0–>SDK Command Prompt and type the following command

• For Trusted Connection to SQL Server which will be authenticated with windows credentials

“aspnet_regsql -S MyPcServer1 -E -ssadd -sstype p”

• For Untrusted connection to SQL Server with user id and password

“aspnet_regsql -S MyPcServer1 -U sa -P sa -ssadd -sstype p”

Configuring ASP.Net Web application: To switch ASP.Net to use SQL you must update the <sessionState> element of your application’s Web.config file as follows;

• Set the mode attribute of the <sessionState> element to SQLServer.
• Set the sqlConnectionString attribute to specify the connection string to your SQL Server

For example

<sessionState

mode=”SQLServer”

sqlConnectionString=”data source=server;user id=uid;password=pwd”

cookieless=”false” timeout=”20″ />