Friday Tip 04, Behind ASP.NET SqlServer Sessions

ASP.NET developers have been using sessions for state management since ASP.NET released and on a daily bases in their applications. However, most developers use sessions as its default mode, moreover others don’t know about these modes at all. Today, I’ll recap the different types of session states and discuss in detail one important type “Sqlserver sessions” that would be necessary to know about when working on big applications that require web farm deployment.

ASP.NET Session State Modes

1. Inproc (default)
2. Out Of Proc / State Server
3. SqlServer

session states can be configured from web.config under sessionstate node as follows:

<system.web>
<sessionState mode="InProc">
</sessionState>
</system.web>

There is more.

Sqlserver Session Mode

Sqlserver mode for session although is not the fastest way to store sessions is sometimes important when it comes to big applications that implement web farms since the INPROC mode won’t be approperiate, why? Imagine that you have a web farm composed of 2 servers A and B. A user had made a request to your web site and was directed to Server A, then that user made another request which was handled through Server B. In that case, the INPROC mode is not appropriate since that user session will be held on one server while his request on another which can’t access his session info. Hence the need for shared place to hold sessions.

Installing Sqlserver Session DataBase

ASP.NET is shipped with script that allows you to install the sessions database. You can find this script in this directory Drive:\WINDOWS\Microsoft.NET\Framework\v2.0.50727.

Moreover, ASP.NET 2 is shipped with a command line tool that will allow you to install / uninstall that database with different options. Using this tool is more recommended by Microsoft than using the scripts which exist for backward compatibility.

To launch this toll:

From Start menu, open your Visual Studio 2005/ 2008 > Visual Studio tools > Visual Studio Command Prompt

The command window will appear. The command line tool is called Aspnet_Regsql so just write Aspnet_Reqsql -? and you will get the help for that tool to know all parameters you need to pass.

Note: This tool has many functionalities than just installing the ASP.NET session database. You can also use it to install membership, enable and disable SQL cache dependancy, provide the required parameters and click enter to install the database (the parameters include server, login credentials and the operation you want, Add / Remove the database).

Persist VS Temporary

You may have noticed that there are two SQL scripts for session database in the .netframe work directory named InstallPersistSqlState, InstallSqlState.sql. So what is the difference ?! Sqlserver sessions can be temporary or persistent. The temporary sessions are stored in the TempDB rather than in Aspstate database. On the other hand, the persistant sessions exist in the AspState database. The Temp one is faster when writing and reading sessions because operations are not logged but the session data lost if the sqlserver goes down for any reason while the persistant one keeps its data.

The Aspnet_regsql tool gives you the option to install whatever type you want by passing the appropriate value to the sstype parameter.

Now, What’s Behind ?

When you finish installing the database, it creates 2 tables, punch of stored procedures and one SQL job.

Tables

For the first, while the above 2 tables have no significant relation between each other, but that’s not true. Each ASP.NET application has an application Id and each application has multiple sessions but how this is maintained since no relation exist. If you noticed, the sessionsId in AspstateTempsessions is a 32 bytes of which the first 24 are for sessionId and the last 8 are the ApplicationId. However, the value of the AppId in the AspstateTempApplications table is in Decimal Format where in the AspstateTempSessions stored in HexaDecimal. You can try it yourself, copy the AppId of the AspStateTempApplications Table and Convert It to Hexadecimal and compare it with the last 8 characters of the SessionId in the AspstateTempSessions table.

Note: This is a bit of useful information because you can use it to share sessions among multiple ASP.NET applications. You will simply make sure to use one static AppId for all appplications.

Stored Procedures

I won’t go to detail every single stored procedure However, I can highlight the important ones. It’s obvious to have insert and update procedures to insert and update existing sessions. For the delete, the DeleteExpiredSessions stored procedure is used and I’ll discuss it when discussing the job below. Another procedure that you will need to know about is the TempGetAppID which you will need to edit if you want to unify your applications IDs.

JOB

Only one SQL job is created with the ASPSATE database. This job is responsible for deleting all expired sessions from the database.

This job consists of only one step that calls the stored procedure DeleteExpiredSessions to clear the database from expired sessions. This stored procedure is simple, it just checks the current DateTime and deletes all session rows that have its expires field exceeded that DateTime.

CREATE PROCEDURE [dbo].[DeleteExpiredSessions]
AS
DECLARE @now datetime
SET @now = GETUTCDATE()

DELETE [ASPState].dbo.ASPStateTempSessions
WHERE Expires < @now

RETURN 0

Note: When using sqlserver mode for sessions all objects stored in sessions have to be serializable (that can be serialized and deserialized).

Although the need for developers to interact with the SessionState database is not actually a case but it’s powerful to know how it works and looks like for further complicated needs that business cases may put developers in.

Hope it was clear enough and added a little bit to your knowledge. This post is on Codeproject.