среда, 5 января 2011 г.

How To configure SQL server to store session state

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]
Command line switch
Species the IP address or the name of SQL server in which you want to store the session state
Specifies the user ID to be used when connecting to the SQL Server
Specifies the password to be used when connecting to the SQL Server
Indicates that you want to use integrated security when connecting to the SQL Server
Adds support for the SQLServer mode session state
Removes support for the SQLServer mode session state
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)
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 ASPState database). 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
sqlConnectionString="Data Source=serverName;Initial Catalog=SQLSessionTable;Integrated Security=True;"
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
HOW TO: Configure SQL Server to Store ASP.NET Session State

Let me know if I have missed something

Комментариев нет: