Storing Session State in a SQL Server Database

Introduction

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:

Command line switch Description
-S <server> Species the IP address or the name of SQL server in which you want to store the session state
-U Specifies the user ID to be used when connecting to the SQL Server
-P Specifies the password to be used when connecting to the SQL Server
-E Indicates that you want to use integrated security when connecting to the SQL Server
-ssadd Adds support for the SQLServer mode session state
-ssremove Removes support for the SQLServer mode session state
-sstype Type of session state support. This option can be:

t for temporary storage
p for persistent storage
c for custom storage

-d <database> The name of the custom database to use if -sstype switch is “c”

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

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read