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 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.
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.
Learn cookies and cookieless sessions by watching a nice video by questpond.com, 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 asp.net 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"
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=188.8.131.52, 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.
<asp:GridView ID="GridView1" runat="server"
AllowPaging="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
<asp:CommandField SelectText="Add to cart"
<asp:HyperLink ID="HyperLink1" runat="server"
Font-Size="Large">I'm Done, show products</asp:HyperLink>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString1 %>"
[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("Qty", typeof(System.Int32)));
Session["sCart"] = ds;
ds = (DataSet)Session["sCart"];
DataRow row = ds.Tables.NewRow();
row["productname"] = GridView1.Rows[GridView1.SelectedIndex].
row["Qty"] = 1;
<asp:GridView ID="GridView1" runat="server"
HeaderText="Product Name" />
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.
Very interesting blog post indeed.ReplyDelete
thank you sir :) I am really blessed to see your very first comment on my blog, encouraging.Delete
i read your blog every day. I am study asp.net mvcReplyDelete
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.
Nice post ThanksReplyDelete
I am cool now about session.Good post.
Nice Post AbhimanyuReplyDelete
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.ReplyDelete
This article could serve asp.net beginners. It is pity on the part of team at www.asp.net 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.Delete
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.
@Mandeep & @Anonymous: Yes this article is for asp.net 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!Delete
Great article. Thank you for taking the time to put this quality content together.ReplyDelete
Hi, I followed all the above steps until serializing many classes, but how do I read data from the SQL storage?ReplyDelete
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
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.ReplyDelete
very nice article.cheers!!.But connection string attribute is not allowed inside sessionState of web.configReplyDelete