Storing Session State in a SQL Server Database

HTTP is a stateless protocol. To allow users save to state information across requests, ASP.NET provides Session storage. The session variables are stored on per-user basis. In ASP classic, you can store session variables only in the Web server's memory. However, this approach proves to be poor in terms of scalability and reliability. In ASP.NET 2.0, however, you can customize the session state store as per your requirement. This article will explore one of the scalable and reliable approaches for storing session variables—SQL Server.

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 vary 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 serves 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.

ASP.NET 2.0 and Session Storage

ASP.NET 2.0 allows you to store session variables at three distinct locations:

1. In the memory of the Web server (in process)
2. In the memory of a machine dedicated to storing session variables (state server)
3. In an SQL Server database

The first mode is the default. Modes 2 and 3 are often called "out-of-process" modes because the session store is independent of the Web site. In this article, you will restrict yourself to exploring the third mode.

Storing session variables in the SQL server has the following advantages:

• Scalability: If you are looking for a highly scalable option to store your session variables, the SQL Server option is for you. It is a much more scalable option than the others. Web farm architecture can very easily access the session variables because they are stores in an independent database.
• Reliability: Because the data is physically persisted in a database, it is is more reliable than the other options. It has the ability to survive server restarts.
• Security: SQL Server is more secure than the in-memory or state server option. You can protect your data more easily by configuring SQL Server security.

The session state mode can be configured via a <sessionState> tag of the web.config file.

Notes:

• In Web farm scenarios, the application path of the Web site in the IIS metabase should be identical in all the Web servers in the Web farm.
• Session_End event never fires for any of the out-of-process modes.

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:

Tables and Stored Procedures Created

As a developer, you possibly will never interact with the session state database directly. However, it would be nice to have a general understanding of the tables and stored procedures created when you run the ASPNET_REGSQL.EXE tool. Figure 1 shows the tables created by this tool; Figure 2 shows the list of stored procedures.

Figure 1: Tables created in the SQL Server database

Figure 2: Stored procedures created in the SQL Server database

Configuring the Web Site to Store a Session State

Once you configure your SQL server to support session state storage, the next step is to configure your Web site. The <sessionState> tag of web.config allows you to specify information about the session store. The following table lists some of the important attributes of this tag:

Example

To illustrate what you've learned up to now, you can develop a simple Web site and configure it to store a session state in a SQL Server database.

Configuring SQL Server

Open the Visual Studio.NET 2005 command prompt and issue the following command:

aspnet_regsql -ssadd -S .sqlexpress -E -sstype p

Here, you are using SQL Express as the database with integrated security turned on. Figure 3 shows a sample run of this tool with the session state type set to "persistent".

Click here for a larger image.

Figure 3: Sample run of ASPNET_REGSQL.EXE tool

Creating and configuring a Web site

Now, create a new web site and modify the web.config file to have the following markup:

<sessionState
mode="SQLServer"
sqlConnectionString="data source=.sqlexpress;
integrated security=true">
</sessionState>

Here, you added the <sessionState> tag and set its mode attribute to SQLServer. You also specified the sqlConnectionString attribute and pointed it to your SQL Server.

Creating test web forms

Add two Web forms in the Web site called Page1.aspx and Page2.aspx. The first Web form will display a list of products to choose from. The selected products are stored in a DataSet. The DataSet is then persisted across the requests by storing it in a session variable. The second Web form simply displays the selected products from the DataSet.

To create the first Web form, follow these steps:

1. Drag and drop a GridView control and SQL Data Source control onto the Web form.
2. Configure the SQL Data Source control to select the ProductID, ProductName, and UnitPrice columns from the Products table of the Northwind database. (A detailed explanation of using data source controls is out of scope of this article.)
3. Bind the GridView to the SQL Data Source by setting its DataSourceID property to the ID property of the SQL Data Source control
4. From the smart tags of GridView, check the "Allow Selection" checkbox.
5. Add a HyperLink control and set its NavigateUrl property to Page2.aspx.

The following markup is generated as a result of above operations (unwanted markup removed):

...
<asp:GridView ID="GridView1" runat="server"
              AllowPaging="True" AutoGenerateColumns="False"
              CellPadding="4" DataSourceID="SqlDataSource1"
              ForeColor="#333333" GridLines="None"
              Width="100%"
              OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
              PageSize="5">
   <FooterStyle BackColor="#990000" Font-Bold="True"
                ForeColor="White" />
   <Columns>
      <asp:BoundField DataField="ProductName"
                      HeaderText="ProductName"
                      SortExpression="ProductName" />
      <asp:BoundField DataField="UnitPrice"
                      HeaderText="UnitPrice"
                      SortExpression="UnitPrice" />
      <asp:CommandField SelectText="Add to cart"
                        ShowSelectButton="True" />
   </Columns>
</asp:GridView>
...
<asp:HyperLink ID="HyperLink1" runat="server"
               NavigateUrl="~/Page2.aspx" Font-Bold="True"
               Font-Size="Large">Show my cart</asp:HyperLink>
...
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
                   ConnectionString=
                   "<%$ ConnectionStrings:NorthwindConnectionString %>"
                   SelectCommand="SELECT [ProductName],
                   [UnitPrice] FROM [Products] 
                   ORDER BY [ProductName]"></asp:SqlDataSource>

Now, add the following code to the SelectedIndexChanged event of the GridView:

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
   DataSet ds=null;
   if (Session["myds"] == null)
   {
      ds = new DataSet();
      DataTable dt = new DataTable();
      dt.Columns.Add(new DataColumn("ProductName"));
      dt.Columns.Add(new DataColumn("Qty", typeof(System.Int32)));
      ds.Tables.Add(dt);
      Session["myds"] = ds;
   }
   else
   {
      ds = (DataSet)Session["myds"];
   }
   DataRow row=ds.Tables[0].NewRow();
   row["productname"]=GridView1.Rows[GridView1.SelectedIndex].
      Cells[0].Text;
   row["Qty"]=1;
   ds.Tables[0].Rows.Add(row);
}

Here, you create a DataSet and a DataTable. The DataTable contains two DataColumns: ProducyName and Qty. You then store the DataSet in a session variable called "myds". When the user selects a product, you create a new DataRow, assign the product details to it, and then add this row to the DataTable. Thus, the user selections are persisted across multiple requests.

To create the second Web form, follow these steps:

1. Drag and drop a GridView onto the Web form.
2. Add two bound fields.
3. Set the HeaderText property of the first bound field to "Product Name". Also, set its DataField property to "ProductName".
4. Set the HeaderText property of the second bound field to "Quantity". Also, set its DataField property to "Qty".

The following markup is created as a result of above operations (unwanted markup removed):

...
<asp:GridView ID="GridView1" runat="server"
              AutoGenerateColumns="False" CellPadding="4"
              ForeColor="#333333" GridLines="None" Width="100%">
   <FooterStyle BackColor="#1C5E55" Font-Bold="True" 
                ForeColor="White" />
   <Columns>
      <asp:BoundField DataField="productname"
                      HeaderText="Product Name" />
      <asp:BoundField DataField="qty"
                      HeaderText="Quantity" />
   </Columns>
</asp:GridView>
...

Add the following code in the Page_Load event handler of the second Web form.

protected void Page_Load(object sender, EventArgs e)
{
   GridView1.DataSource=(DataSet)Session["myds"];
   GridView1.DataBind();
}

Here, you set the DataSource property of the GridView control to your DataSet stored in session. You then bind the GridView by calling its DataBind() method.

Running the sample Web forms

To test your web forms, set Page1.aspx as the start page and run the Web site. You should see something as shown in Figure 4.

Figure 4: Sample run of Page1.aspx

Select a few products by clicking the "Add to cart" button. This will add some rows in the DataTable. Recollect that you are storing your DataSet in a session variable. Then, navigate to Page2.aspx by clicking the "Show my cart" hyperlink. Figure 5 shows Page2.aspx with the previously selected products.

Figure 5: Sample run of Page2.aspx

As you can see, Page2.aspx correctly displays the items you selected on the previous page. This indicates that your session variable was indeed stored in the SQL server database and retrieved on the second page. Also, note that you used the same syntax of storing and retrieving values in the session irrespective of the storage mode.

Disadvantages of Storing the Session State in SQL Server

Though storing the session state in SQL server makes your Web site more scalable and reliable, it has some disadvantages of its own:

• Performance: In terms of performance, a SQL Server-based session store is possibly the slowest option. Because your session variables are stored in a physical database, it takes more time to get them in and out of the database. This affects the performance of your Web site.
• Cost: Because you are storing your data in a SQL Server database, you need to have a SQL Server license. This can add to overall cost of your Web site.
• Serializable data: This method requires that all the data stored in session variables must be serializable. This may force you to mark your own classes as [Serializable] if you want to store them in a session.

Summary

ASP.NET 2.0 allows you to store the session state to a SQL Server database. The ASPNET_REGSQL.EXE tool configures the SQL Server to support this feature. Further, the <sessionState> tag configures your Web site to support this mode. Storing a session state in SQL server is a more scalable, secure, and reliable option. However, its performance will be slower as compared to the other storage options.

Download the Code

You can download the code that accompanies the article here.

About the Author

Bipin Joshi is the founder and owner of BinaryIntellect Consulting, www.binaryintellect.com, where he conducts professional training programs on .NET technologies. He is the author of the Developer's Guide to ASP.NET 2.0 and co-author of three WROX press books on .NET 1.x. He writes regularly for www.dotnetbips.com and www.binaryintellect.net, the community Web sites he founded. He also writes about life and Yoga at www.bipinjoshi.com and www.binaryintellect.info. He is a Microsoft MVP, member of ASPInsiders, MCAD, and MCT. When away from computers, he spends time in practicing, studying, and teaching Yoga.