Storing Session State in a SQL Server Database

Session State and Associated Problems

As in classic ASP, by default the session state is maintained in the Web server's memory. However, this approach poses two problems:

·         It overburdens the server, affecting the Web site's scalability

·         It cannot be used effectively in Web farm scenarios

Let me discuss these problems in a bit of detail so that you can appreciate your choice of a session store.

Session variables are created on a per-user basis. By default, they are maintained in the Web server's memory. Imagine a Web site with thousands of users. Because of the huge number of users, the number of active sessions on the Web server also will be very high. That means you are storing too much data in the Web server's memory. If the load on the server keeps of increasing, it may reach saturation and cause trouble for overall scalability of your application.

To tackle the issue of scalability mentioned above people, implement Web farms. A Web farm is a cluster of Web servers running in parallel. Each Web server in the cluster has a mirror of your Web site. The traffic of your Web site is equally distributed among the available servers, thus providing load balancing. Storing session variables in the Web server's memory can hamper the Web farm's architecture. Assume that there are three Web servers—S1, S2, and S3—connected in parallel and serving the incoming requests. A request R1 comes into the cluster and the load balancing logic decides that S2 and S3 are busy with some other task, but S1 is free to process your request. Naturally, the request gets forwarded to S1 for processing. Now, imagine that during the processing, you store a session variable in S1's memory. So far, so good. After some time, the same user gives another request, R2, that needs the session variable stored by the previous request, R1. However, this time S1 was occupied with some work and S2 and S3 are free. You would expect that as per the load-balancing rule, R2 should get forwarded to S2 or S3. But, if that happens, how will R2 get access to the session variables? After all, they are stored in the memory of the altogether-separate Web server S1. This means R2 still needs to wait for S1 to become free. This is, of course, a poor use of Web farm resources.

Configuring SQL Server to Store a Session State

Before you can actually store a session state in SQL server, you need to configure it. This configuration is done via a command line tool called ASPNET_REGSQL.EXE. You can store the session state in three possible locations within the SQL Server:

Temporary storage: In this case, the session state is stored in the "tempdb" database of SQL Server. The tool creates a database called ASPState and adds certain stored procedures for managing session to it. The tool also creates required tables in the "tempdb" database. If you restart the SQL server, the session data is not persisted.

Persistent storage: The tool creates a database called ASPState and adds stored procedures for managing a session to it. The session state is stored in the ASPState database. The advantage of this method is that the data is persisted even if you restart the SQL server.

Custom storage: Both the session state data and the stored procedures are stored in a custom database. The database name must be specified in the configuration file.

The following table lists various command line switches of the tool with respect to session store configuration:

To run the Aspnet_regsql.exe, Startà All Programsà Microsoft Visual Studio 2005/2008à Visual Studio Toolsà Visual Studio 2008 Command Prompt.

It opens command prompt.  Type the following command to create the SQL State Database tables and procedures.

Fig: For creating default Database for SQL State.

Fig: For using the Custom Data Base for maintain SQL State information

Note:  If you use custom database to store Session data, you need to set the property “allowCustomSqlDatabase=True" in Session state attribute in web.config ad specify the Custom database name in the sqlConnectionString attribute.

 Once completed the above steps, the database tables looks like this:

And, The Stored procedures created are:

Once Above steps completed, perform the following steps:

1.       Open SQL Server Management Studio.

2.       In SQL Server Management Studio, on the File menu, click Open.

3.       In the Open Query File dialog box, browse to the InstallSqlState.sql script file, and then click Open. By default, InstallSqlState.sql is located in one of the following folders:

4.       system drive\WINNT\Microsoft.NET\Framework\version\

system drive\Windows\Microsoft.NET\Framework\version\

5.       After InstallSqlState.sql opens, click Execute on the Query menu to run the script.

Required Tables and Stored Procedures will be created for  SQL Server State management.

Example:

Open Visual Studioà fileà newà websiteà

Name it as sample.

Open Web.config file to configure SQL State management:

Write this session state configuration:

<system.web>     

<sessionState mode="SQLServer" sqlConnectionString="data source=RAVIKRISHNA; user id=sa;password=sa12$;" cookieless="false" timeout="20"/>

</system.web>

Default.aspx:

protected void Page_Load(object sender, EventArgs e)

    {

        Session["Name"] = "Ravikrishna";

        Session["Mobile"] = "9948869222";

    }

Once run this website by clicking on “F5”, this session data will be stored on database.

	Mr. Ravi Krishna - Senior Software Engineer I am a Mocrosoft ASP.net Developer and MCP Certified professional. I have overall 5 years of experience in IT Industry,in that 3 years experience in Microsoft BI(SSAS,SSIS,SSRS). I have experience on various business domains like Automation and Chemical. https://sites.google.com/site/rkkumardotnet/ Read more