Session Mode - SQL Server

In previous post we see what Session is, how to use it and what are all Modes of storing Session’s.

Now, we will see How to use SQL Server to store ASP.Net Session.

If you want to store Session state in the most reliable way possible, then you can store Session state in a Microsoft SQL Server database. Because you can set up failover SQL Server clusters, Session state stored in SQL Server should be able to survive just anything, including a major nuclear war.

You must complete the following two steps to enable SQL Server Session state:

• Configure your database to support SQL Server Session state.
• Configure your application to use SQL Server Session state.

You can use the aspnet_regsql tool to add the necessary tables and stored procedures to your database to support SQL Server Session state. The aspnet_regsql tool is located in the following path:

\WINDOWS\Microsoft.NET\Framework\[version]\aspnet_regsql.exe

Note: If you open the SDK Command Prompt, you don't need to navigate to the Microsoft.NET folder to use the aspnet_regsql tool.

Executing the following command enables SQL Server Session state for a database server named YourServer.

aspnet_regsql -C "Data Source=YourServer;Integrated Security=True" -ssadd

When you execute this command, a new database is created on your database server named ASPState. The ASPState database contains all the stored procedures used by Session state. However, by default, Session state information is stored in the TempDB database. When your database server restarts, the TempDB database is cleared automatically.

If you want to use SQL Server Session state with a failover cluster of SQL Servers, then you can't store Session state in the TempDB database. Also, if you want Session state to survive database restarts, then you can't store the state information in the TempDB database.

If you execute the following command, then Session state is stored in the ASPState database instead of the TempDB database:

aspnet_regsql -C "Data Source=YourServer;Integrated Security=True" -ssadd -sstype p

Notice that this command includes a -sstype p switch. The p stands for persistent. Session state that is stored in the ASPState database is called persistent Session state because it survives database server restarts.

Finally, you can store Session state in a custom database. The following command stores Session state in a database named MySessionDB:

aspnet_regsql -C "Data Source=YourServer;Integrated Security=True" -ssadd -sstype c –d MySessionDB

Executing this command creates a new database named MySessionDB that contains both the tables and stored procedures for storing Session state. Notice that the -sstype switch has the value c for custom. The command also includes a -d switch that enables you to specify the name of the new database.

If you want to remove the Session state tables and stored procedures from a server, then you can execute the following command:

aspnet_regsql -C "Data Source=YourServer;Integrated Security=True" -ssremove

Executing this command removes the ASPState database. It does not remove a custom Session state database. You must remove a custom database manually.

After you configure your database server to support Session state, you must configure your ASP.NET application to connect to your database. You can use the web configuration file to connect to a database named YourServer.

version="1.0"?>

<configuration>

<system.web>

mode="SQLServer"

sqlConnectionString="Data Source=YourServer;Integrated Security=True"

sqlCommandTimeout="30" />

<machineKey

decryption="AES"

validation="SHA1"

decryptionKey="306C1FA852AB3B0115150DD8BA30821CDFD125538A0C606DACA53DBB3C3E0AD2"

validationKey="61A8E04A146AFFAB81B6AD19654F99EA7370807F18F5002725DAB98B8EFD19C7113

37E26948E26D1D174B159973EA0BE8CC9CAA6AAF513BF84E44B2247792265" />

system.web>

The sessionState element includes three attributes. The mode attribute is set to the value SQLServer to enable SQL Server Session state.

The second attribute, sqlConnectionString, contains the connection string to the Session state database.

Finally, the sqlCommandTimeout specifies the maximum amount of time in seconds before a command that retrieves or stores Session state times out.

Notice that the configuration file includes a machineKey element. If your Session state database is located on a different machine than your ASP.NET application, then you are required to include a machineKey element that contains explicit encryption and validation keys.

Warning: Don't use the web configuration file without modifying the values of both the decryptionKey and validationKey attributes.

If you select the option to store Session state in a custom database when executing the aspnet_regsql tool, then you need to specify the name of the custom database in your configuration file. You can use the web configuration file.

version="1.0"?>

<configuration>

<system.web>

mode="SQLServer"

sqlConnectionString="Data Source=YourServer;

Integrated Security=True;database=MySessionDB"

sqlCommandTimeout="30"

allowCustomSqlDatabase="true"/>

<machineKey

decryption="AES"

validation="SHA1"

decryptionKey="306C1FA852AB3B0115150DD8BA30821CDFD125538A0C606DACA53DBB3C3E0AD2"

validationKey="61A8E04A146AFFAB81B6AD19654F99EA7370807F18F5002725DAB98B8EFD19C7113

37E26948E26D1D174B159973EA0BE8CC9CAA6AAF513BF84E44B2247792265" />

system.web>

The sessionState element in the configuration file includes an allowCustomSqlDatabase attribute. Furthermore, the sqlConnectionString attribute contains the name of the custom database.

Enabling SQL Server session state has no effect on how you write your application code. You can initially build your application using in-process Session state and, when you have the need, you can switch to SQL Server Session state.

Thanks,

Paresh Bhole