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]
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