How To configure SQL server to store session state

Posted by Pranav Ajgaonkar on Jul 29, 2011 in ASP.NET and SQL | 0 comments

I was trying to implement storing ASP.NET session in SQL server today

and faced a few problems during configuration.

I had to go through many articles that talk about what is SQL server,

what's the need to store session in SQL etc. Common, I know them,

I just need a few tips or guidelines how to store sessions - in short!
You might get abundant articles that talk about Storing ASP.NET Session in SQL Server.

So what’s new in this blog? To be frank nothing’s new,

I am just trying to share my experience so that if someone just wants to implement it and knows

the concept already. 

Initially some years back I thought storing sessions in SQL is a tedious job and requires

a lot of administration; however I was proved wrong.

Before you can actually store a session state in SQL server using ASP.NET 2.0,

you need to configure it.

This configuration is done via a command line tool called ASPNET_REGSQL.EXE.

However there are stored procedures to create/remove

the database [InstallSqlState.sql and UninstallSqlState.sql located at

system drive\Windows\Microsoft.NET\Framework\version]

The following table lists the command line switches of the aspnet-regsql.exe

that will be normally used in session store configuration: [FYI: aspnet_regsql -?

displays many more command switches]

switches]

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 (Stores session data in the SQL Server tempdb database) p for persistent storage (Stores session data in the ASPState database) c for custom storage (Stores session data in a custom database)
-d <database>	The name of the custom database to use if -sstype switch is “c”

The steps are as follows:

Step1:I like to go for custom storage (I don’t want to use the default ASPStatedatabase). I would be naming my database as SQLSessionTable

I would use either of the following statements/commands to create the table in SQL

1. C:\Program Files\Microsoft Visual Studio 8\VC>

aspnet_regsql -ssadd -d SQLSessionTable -S serverName –sstype c -U domain\user -P Password

2. C:\Program Files\Microsoft Visual Studio 8\VC>

aspnet_regsql -ssadd -d SQLSessionTable -S serverName -sstype c –E

If I wanted to use the default ASPState database, I would skip the -d option. The command would be as follows:

aspnet_regsql -ssadd -S serverName -sstype c –E

Step2: The next step would be to configure your web.config file to reflect the sessionState information

<sessionState

mode=“SQLServer“

allowCustomSqlDatabase=“true“

sqlConnectionString=“Data Source=serverName;Initial Catalog=SQLSessionTable; Integrated Security=True;“

cookieless=“false“

timeout=“20” />

In case I had used the default database (ASPState), I would exclude the entries highlighted above. Now proceed with coding your ASP.NET application to support out-of process session state.

A few useful articles,

Session-State Modes

http://msdn.microsoft.com/en-us/library/ms178586.aspx

HOW TO: Configure SQL Server to Store ASP.NET Session State

http://support.microsoft.com/kb/317604