Introduction
This is a brief to the point note on how to setup ASP session state to use SQL server to store session information.
There are 2 steps for setting up the ASPState
database and the configuration setting in your web.config.
For some reason, you cannot run the SQL file directly. I can't remember what the issue is, but you need to use ASP_RegSQl.exe from a command prompt. The script file and the aspregsql EXE are located at C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727.
Steps
- Open a command prompt and locate the following path: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 based on your OS version and .NET version
- Use the following statement:
- Using default ASPState database and SQL security
aspnet_regsql -S serverName -U UserName -P Password -ssadd -sstype p
- Using default
ASPState
database and windows security
aspnet_regsql -S serverName -E -ssadd -sstype p
- Using custom database and SQL security
aspnet_regsql -d TableName -S serverName -U UserName -P Password
-ssadd -sstype c
t
- Stores session data in the SQL Server tempdb database. This is the default. If you store session data in the tempdb database, the session data is lost if SQL Server is restarted. p
- Stores session data in the ASPState
database instead of in the tempdb database.c
- Stores session data in a custom database. If you specify the c
option, you must also include the name of the custom database using the -d
option.
- In your configuration file:
- Using default SQL security:
<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true"
sqlconnectionstring="Data Source=Server;User ID=UserID;Password=Password;"
cookieless="false">
- Using default windows security:
<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true"
sqlconnectionstring="Data Source=Server;Integrated-Security=SSPI;"
cookieless="false">
- Custom database name:
<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true"
sqlconnectionstring="Data Source=Server;Initial Catalog=tablename;
User ID=UserID;Password=Password;" cookieless="false">
Using SQL Server to Store ASP Session Information in a Web Farm
Things to watch for when using SQL to store the session information in a web farm scenario:
- The machine key between the servers' needs to be the same as
AspState Session
information is encrypted using the machine key. - The application path to your websites on all machines needs to be consistent as well.
Other Issues
Since the session information needs to be converted from memory to a more persist-able form such as text. Out of memory session stores serialize and deserialize the session, you need to ensure that all complex/custom objects that you wish to store in a session are serializable. If you come across any such object that you cannot serialize by using the serialization attribute, you can always serialize it programatically.
CodeProject