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 |
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 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
<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
https://msdn.microsoft.com/en-us/library/ms178586.aspx
HOW TO: Configure SQL Server to Store ASP.NET Session State
https://support.microsoft.com/kb/317604
Let me know if I have missed something
- Akshay
Comments
- Anonymous
October 04, 2008
PingBack from http://blog.a-foton.ru/index.php/2008/10/04/how-to-configure-sql-server-to-store-session-state/ - Anonymous
April 12, 2009
Thank you very much, you saved me a lot of reading - Anonymous
March 25, 2010
Awesome details, my friend. Keep up the good work - Anonymous
April 06, 2010
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 - Anonymous
April 08, 2010
Hello Mike,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 clusterhttp://support.microsoft.com/kb/323262Regards,Akshay - Anonymous
December 10, 2010
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 –EThanks, it works great. - Anonymous
September 19, 2014
Thanks, its help a lot