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;"
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,
HOW TO: Configure SQL Server to Store ASP.NET Session State
Let me know if I have missed something
PingBack from http://blog.a-foton.ru/index.php/2008/10/04/how-to-configure-sql-server-to-store-session-state/
Thank you very much, you saved me a lot of reading
Awesome details, my friend. Keep up the good work
Is there anything different that needs to be done when my SQL server is clustered? I have installed the ASPState database many times without issue on non-clustered SQL server. On this one I get the following error when I try to access my website:
Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above.
If I change the config file and point it to the ASPState Db on my Staging non-clusterd SQL server the web site opens as expected
I hope the following article will help you answer your questions. This has worked for me :-)
How to use ASP.NET session state SQL Server Mode in a failover cluster
For the aspnet_reqsql command using the default database, I think you want to use the flag -sstype p "c" tells it to use a custom database and requires the -d flag, whereas the "p" tells it to persist the session state and the stored procedures in the default DB. So it should be:
aspnet_regsql -ssadd -S serverName -sstype p –E
Thanks, it works great.