Friday, December 18, 2009

Configuring ASP session state on SQL server

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 setting up the ASPState database and the configuration setting in your web.config.

For some reason you cannot run the sql file directly cant remember what the issue is, but you need to use ASP_RegSQl.exe from a commmand prompt. The script file and the aspregsql exe are located here C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727


1) Open a command propmt and locate the following path C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 based on your Os version and .Net version

2) Use the following statement

a. Using default ASPState database and Sql security

aspnet_regsql -S serverName -U UserName -P Password -ssadd -sstype p

b. Using default ASPState database and windows security

aspnet_regsql -S serverName -E -ssadd -sstype p

c. 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.

3) In your configuration file

a) Using default Sql security

<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" sqlconnectionstring="Data Source=Server;User ID=UserID;Password=Password;" cookieless="false">

b) Using default windows security

<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" sqlconnectionstring="Data Source=Server;Integrated-Security=SSPI;" cookieless="false">

c) 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 info in a Web Farm

Things to watch for when using SQL to store the session info in a web farm scenario

1) The machine key between the servers needs to be the same as AspState Session info is encrypted using the machine key.

2) 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

No comments: