Skip to content

Personal tools
You are here: Home » SQL Server » SQL Server Articles Archive » ASP.NET State Management Using SQL Server
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3554

ASP.NET State Management Using SQL Server

by Eric Charran

One of the primary challenges during Internet development how best to manage state. Because the HTTP protocol is a stateless protocol, each time the user elects an option on the page that causes a POST operation (a server post or postback is a round trip between the client and the server), the page is destroyed and recreated, then sent back to the browser.

Many Internet developers and technology framework providers have found methods to introduce state into Internet development. Methodologies such as application state, session state, view state, and others, including hidden fields, request strings, and cookies are among the most popular. Each of these methodologies introduces a way to include information that the code can use to understand where a Web user is in a given process or workflow.

What is Session State?

The session state object has existed since classic ASP. Its basic architecture consists of a server confined in volatile in-memory cache where developers and applications can store session-specific data to be used throughout the application. When a page is destroyed, developers can rely on session state to store information that can be referenced by the same or next page, as long as the user’s session (their visit to the same Web server) is active. Session state times out after 20 minutes of inactivity, and all information stored there is lost.

Session state exists for each visitor to the application in IIS, and is also bound to the machine that the visitor hits when visiting the application. This observation becomes pertinent when considering scale-out options for applications such as Web farms. Session state has a high server affinity; if the number of Web servers hosting the application increases beyond a single server, there is no native guarantee that the Web user will reach the exact same Web server between Web requests to retrieve the exact same session information. As a result, native, out-of-the-box implementation of session state in a Web farm will not suffice.

Architecture of Scaling-Out with Session State

The central concept of scaling–out for an Internet application with a large audience is to implement a Web farm. The Web farm, with multiple servers to serve application requests, often is chosen rather than scaling-up a single Web server. However, scaling-out requires modification of how the infrastructure supports session state.

As a result, one alternative architecture is to modify the existing environment by adding a state server. The responsibility of the state server is to consolidate all sessions on a single machine with ample memory to service the entire Web farm. Similar to a single Web server environment, all sessions are housed within RAM on the state server, and the Web farm contains knowledge of where to store and access items from session state.

Another approach is to store all state-related information within SQL Server. This allows for a more robust state management capability that has more persistence than a memory-based state server. This methodology will allow for state to be maintained even across server reboots, because the data for the session persists into SQL Server (with some configuration). State in the database is kept in binary fields (i.e., varbinary and mage column data types). Similarly, if the Web application is designed to work with SQL Server, the application can take advantage of failover clustering.

Session State in the Database

Microsoft has provided several resources, as well as SQL Scripts found at;EN-US;311209 that provide instructions and DDK for generating the session state database (depending on which version of the .NET Framework you are using). One aspect of maintaining session state within SQL Server of which you should be aware is that despite the creation of a separate database from the aforementioned resource within the .NET Framework 1.1, or the link above (which creates an ASPState database), the ASPState database creates tables that stores session state in TempDB.

There are some ramifications regarding placing session state within the TempDB database that should be considered during the set up of the session state server. The TempDB database is vital for general SQL Server operations, which include allocation of space for creating objects and other central SQL Server basic operations. With the added contention of storing session state from a Web farm within the same database, operational issues could arise that affect the whole Web farm. If the SQL Server is not a dedicated state server, these issues could become contentious and may need to be managed. Fortunately, the SQL script that creates the database also creates a SQL Agent job to cleanse sessions from TempDB and effectively from the Web farm. Care must also be taken to ensure that the ASP worker process account (commonly ASPNET) has access to the database in the appropriate manner as well.


Eric Charran is currently working as a Technical Architect and Web Developer, in Horsham, PA. Eric holds Microsoft Certifications in SQL Server and has significant experience in planning, designing, and modeling n-tier applications using SQL Server 2000, as well as architecting and implementing .NET framework based applications, solutions and Web services. Eric’s other professional skills encompass Database Administration, Data Warehousing and Application Architecture, Modeling and Design, as well as Data Warehouse Transformation and Population using DTS. Eric is also skilled in developing and implementing ASP.NET and Windows forms applications, using technologies such as COM+, the .NET framework, VB.NET and other .NET languages.

Contributors : Eric Charran
Last modified 2005-04-12 06:21 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone