Session State Management using SQLServer

To configure session state management in a web application we have to use the sessionState section of the web.config file. sessionState can have following mode.

1.Off - Indicates that the session state is turned off.
2. InProc - session kept as live objects in web server (aspnet_wp.exe). Use "cookieless" configuration in web.config to add the sessionId onto the URL.
3. StateServer - session serialized and stored in memory in a separate process (aspnet_state.exe). State Server can run on another machine.
4. SQLServer - session serialized and stored in SQL server.
5. Custom - Indicates that you will have a custom mechanism of session storage using a provider model of ASP.NET.

Now to maintain session state with SQL server,first, we have to configure SQL server.

Actually we need special database to maintain session state with the SQL server and .Net Framework provides us sql scripts for this purpose. Following is the list of these sql scripts.

-> InstallSqlState.sql-- Contains scripts to set up database for state management. Its creates database "ASPSTATE" that contains stored procedures that create tables in tempdb. The tables(ASPStateTempSessions and ASPStateTempApplications) in tempdb are where session state is actually stored. Thus, when the SQL Server is shutdown, all session state is lost. It also creates a job called State_Job_DeleteExpiredSessions to delete expired sessions from tempdb. Recall that ASP.NET does not keep session resources alive indefinitely. To support this feature when a SQL Server is used to maintain state, the SQL Server Agent must be running so that the expired session deletion job runs as needed.

-> UninstallSqlState.sql-- Contains scripts for droping the database (ASPSTATE) and all supporting objects (e.g., the job to delete expired sessions). But before running this script, stop the Web server service to avoid any error Or use the SQL Server Enterprise Manager and find the processes accessing the ASPState database and delete them.

-> InstallPersistSqlState.sql-- contains scripts to set up database for persistent state management. It causes the session state data to be stored in permanent tables in ASPState instead of temporary tables in tempdb.

-> UninstallPersistSqlState.sql-- Contains scripts for droping the database (ASPSTATE) and all supporting objects (e.g., the job to delete expired sessions).

-> InstallSqlStateTemplate.sql / UninstallSqlStateTemplate.sql--
These are templates files for installing the ASP.NET session state SQL objects
on a database other than the default 'ASPState'.
To create your own script files based on the template:
1. Create your own script files by coping the two template files.
2. Decide a name for your database (e.g. MyASPStateDB)
3. In your own script files, replace all occurences of "DatabaseNamePlaceHolder"
by your database name.
4. Install and uninstall ASP.NET session state SQL objects using your own
script files.

You can run these file either in SQL Query Analyzer or using command line tool aspnet_regsql.exe.

Now use the sessionState section of the web.config file to configure an ASP.NET Web application to use a SQL Server for session state management.

< sessionState
mode="SQLServer"
stateConnectionString="tcpip=localhost"
sqlConnectionString="data source=localhost;user id=sa;password=kkak"
cookieless="false"
timeout="20" />

In case you have setup custom database for the session state management using InstallSqlStateTemplate.sql then add database name in the sqlConnectionString. Like...

< sessionState
mode="SQLServer"
stateConnectionString="tcpip=localhost"
sqlConnectionString="data source=localhost; database=MyASPStateDB;user id=sa;password=kkak"
cookieless="false"
timeout="20" />

Note: The session state timeout interval is specified by using the timeout parameter.By default ASP .NET uses cookies to identify which requests belong to a particular session.If cookies are not available, a session can be tracked by adding a session identifier to the URL. To disable cookies, set sessionState cookieless="true".

Reference:
http://www.dbazine.com/sql/sql-articles/cook9