ASP.NET Session States in SQL Server Mode (Session State Story)

A session is defined as the period of time that a unique user interacts with a Web application. Active Server Pages (ASP) developers who wish to retain data for unique user sessions can use an intrinsic feature known as session state.

Programmatically, session state is nothing more than memory in the shape of a dictionary or hash table, e.g. key-value pairs, which can be set and read for the duration of a user's session.

Classical ASP Session State Problems

ASP developers know session state as a great feature, but one that is somewhat limited. These limitations include:
  • Process dependent: ASP session state exists in the process that hosts ASP; thus the actions that affect the process also affect session state. When the process is recycled or fails, session state is lost.
  • Server farm limitations: As users move from server to server in a Web server farm, their session state does not follow them. ASP session state is machine specific. Each ASP server provides its own session state, and unless the user returns to the same server, the session state is inaccessible. While network IP level routing solutions can solve such problems, by ensuring that client IPs are routed to the originating server, some ISPs choose to use a proxy load-balancing solution for their clients. Most infamous of these is AOL. Solutions such as AOL's prevent network level routing of requests to servers because the IP addresses for the requestor cannot be guaranteed to be unique.
  • Cookie dependent: Clients that don't accept HTTP cookies can't take advantage of session state. Some clients believe that cookies compromise security and/or privacy and thus disable them, which disables session state on the server.

These are several of the problem sets that were taken into consideration in the design of ASP.NET session state.

ASP.NET 1.0 Session State

ASP.NET session state solves all of the above problems associated with classic ASP session state:
  • Process independent: ASP.NET session state is able to run in a separate process from the ASP.NET host process. If session state is in a separate process, the ASP.NET process can come and go while the session state process remains available. Of course, you can still use session state in process similar to classic ASP, too.
  • Support for server farm configurations: By moving to an out-of-process model, ASP.NET also solves the server farm problem. The new out-of-process model allows all servers in the farm to share a session state process. You can implement this by changing the ASP.NET configuration to point to a common server.
  • Cookie independent: Although solutions to the problem of cookieless state management do exist for classic ASP, they're not trivial to implement. ASP.NET, on the other hand, reduces the complexities of cookieless session state to a simple configuration setting.

So, with the release of ASP.NET we got following important session state options: ‘in-process mode’, ‘out-of-process mode’, ‘Cookieless’ and ‘SQL Server mode’. Let’s look at them.

In-Process Mode

In-process mode simply means using ASP.NET session state in a similar manner to classic ASP session state. That is, session state is managed in process and if the process is re-cycled, state is lost. If we call SessionState.aspx, set a session state value, and stop and start the ASP.NET process (iisreset), the value set before the process was cycled will be lost. In-process mode is the default setting for ASP.NET.

Out-of-process Mode

Included with the .NET SDK is a Windows NT service: ASPState. This Windows service is what ASP.NET uses for out-of-process session state management. To use this state manager, you first need to start the service.

Cookieless State

We can configure for ASP.NET session state is cookieless session state. Essentially this feature allows sites whose clients choose not to use cookies to take advantage of ASP.NET session state. This is done by modifying the URL with an ID that uniquely identifies the session:


Learn cookies and cookieless sessions by watching a nice video by, here

SQL Server Mode

The SQL Server mode option is similar to that of the Windows NT Service, except that the information persists to SQL Server rather than being stored in memory.

To use SQL Server as our session state store, we first must create the necessary tables and stored procedures that ASP.NET will look for on the identified SQL Server. The .NET SDK provides us with a SQL script file that we will execute on SQL Server to setup the database tables and stored procedures and then will use the database credentials in ASP.NET Applications to start using SQL Server to manage the session states.

Why SQL Server Mode?

Once you start running multiple web servers for the same web site, the default session state that is ‘InProc’ will no longer be useful because you cannot guarantee that each page request goes to the same server. It becomes necessary to have a central state store that every web server accesses. SQL Server has a feature that offers you centralized storage of a session state in a Web farm. You can use SQL Server to save a session.

SQL Server Mode Advantages

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.

Now, this step-by-step article demonstrates how to configure Microsoft SQL Server for ASP.NET SQL Server mode session state management.

Job 1: Configuring SQL Server to use ASP.NET’s SQL Server Session State

Step 1: Find the sql script file installed by .NET SDK and execute it on SQL Server to setup database.

Step 2: Double click above file to install it on SQL Server, after installation you will get following database tables and stored procedures.

Now, we done with database setup, let’s create a demo web application and will create shopping cart like application and will let the user to add the products to cart and at the end will show the product list to user. Think, if are developing e-commerce website that is using multiple servers, then how will you manage the sessions, because session directly depends on server and your website using multiple servers, in this case you will lose all the sessions/products that user selected when jump to another server. No worries we are using centralized server that is SQL Server to manage our sessions. Go ahead and setup a website.

Job 2: Setup Web Application

At very first, let’s modify our existing web.config file to use SQL Server Mode Sessions. To do this add a ‘connectionstring’ that will point ‘tempdb’ database.

    <add name="tempdbConnectionString1" connectionString="Data Source=ITORIAN-PC1;Initial Catalog=tempdb;Integrated Security=True"
      providerName="System.Data.SqlClient" />

And then, modify the <sessionState> section so that it looks like

    <sessionState mode="SQLServer" customProvider="DefaultSessionProvider">
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="tempdbConnectionString1" />

You can notice the ‘mode’ attribute in above code that is using ‘SQLServer’. Once you done, let’s setup website pages.

Case Study: We will create two pages in our website, one will show the product list and another will show the selected products, will call those pages by name ‘Products.aspx’ and ‘Cart.aspx’. I’ll be using Northwind database in this project.

Products.aspx Code

    <asp:GridView ID="GridView1" runat="server"
        AllowPaging="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
        Width="48%" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
            <asp:BoundField DataField="ProductName"
                SortExpression="ProductName" />
            <asp:BoundField DataField="UnitPrice"
                SortExpression="UnitPrice" />
            <asp:CommandField SelectText="Add to cart"
                ShowSelectButton="True" />

    <asp:HyperLink ID="HyperLink1" runat="server"
        NavigateUrl="~/Cart.aspx" Font-Bold="True"
        Font-Size="Large">I'm Done, show products</asp:HyperLink>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString1 %>"
        SelectCommand="SELECT [ProductName],
            [UnitPrice] FROM [Products]
            ORDER BY [ProductName]"></asp:SqlDataSource>


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

Cart.aspx Code

    <asp:GridView ID="GridView1" runat="server"
        AutoGenerateColumns="False" Width="48%">
            <asp:BoundField DataField="productname"
                HeaderText="Product Name" />
            <asp:BoundField DataField="qty"
                HeaderText="Quantity" />

Cart.aspx.cs Code

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

Now, you looked at some practical uses of the title.

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.

Most of the theory resources in this article is taken from MSDN.

I hope you like it. Thanks.


  1. Replies
    1. thank you sir :) I am really blessed to see your very first comment on my blog, encouraging.

  2. i read your blog every day. I am study mvc

    1. Hi Abhimanyu,

      What a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this.

      I've an editable grid.

      A field is DropDown type, and I need show this datasource grouped, but I don't know how to do it. ASP .Net

      Also I'll need enable search filter

      Very useful post !everyone should learn and use it during their learning path.


  3. Thank you.Abhimanyu.
    I am cool now about session.Good post.

  4. In this day and age of in-memory distributed caching (memcached, redis, etc) - why in the world would anyone use SQL Server to persist state? If there is anything one learns in building a highly scaleable website, it's that the database tier is the one area you need to protect most as its the choke point for performance. For any reader on this article, take a look at persisting session into in-memory distributed caches across multiple servers. Look at sharedcache or NCache or others depending on your requirements, but don't kill your database with key-value pair look-ups.

    1. This article could serve beginners. It is pity on the part of team at who are approving such outdated articles. It's 2012, for god sake please raise your level of infrastructure selection. The author has recommended sql server for session persistence without thinking how much this will kill performance of the site.

      The distributed caching option that you recommended are all open source that require significant investment of time and resources to get them to work why not go for app fabric caching. It works like charm right out of box without much configuration or tweaking.

    2. @Mandeep & @Anonymous: Yes this article is for beginners only and I've not recommended it. This article is only for those ppl who wish to learn 'Session States in SQL Server Mode'...that's it. AppFabric is really good for distributed caching...agree!

  5. Great article. Thank you for taking the time to put this quality content together.

  6. Hi, I followed all the above steps until serializing many classes, but how do I read data from the SQL storage?
    The session IDs in the created rows in ASPStateTempSessions table are not the same as the IDs created when I debug. And I re-launced my application many times, but only 2 entries are created in the table, and at different times (times when my computer wasnt even turned on !). Please help me

  7. Hi, I followed all the above steps. but I am not able to read session data from tables. I re-launched my application many times, but only one row is created in the ASPStateTempSessions table , and at a very different time. Please help.

  8. very nice article.cheers!!.But connection string attribute is not allowed inside sessionState of web.config


Post a Comment

Popular posts from this blog

Migrating database from ASP.NET Identity to ASP.NET Core Identity

Customize User's Profile in ASP.NET Identity System

Lambda two tables and three tables inner join code samples