When should we use SQL Server Session Mode ?

• SQL Server Session mode is more reliable and secure session state management.
• Its keeps data in a centralized location (database).
• We should use SQL server session mode when we need to implement Session with some more security.
• If  there happens to be  frequent server Restart we can implement SQL server.
• This is perfect mode that fits in web farm and web garden scenarios (I have explained in details later) . 

we can use SQL server Session mode when we need to share session between two different application 

Configuration for SQL Server Session Mode  

In SQL Server  Session mode, we are storing session data in a SQL Server, so we need to first provide the database connection string in web.config . sqlConnectionString attribute is used to provide the connection string in web.config.  

After setup the connection string we need to configure the SQL Server. I am explaining how to configure your your SQL server using aspnet_regsql command.

Step 1: From Command prompt, Go to your Framework Version Directory

E.g :c:\windows\microsoft.net\framework\<version>.

Step 2 : Run aspnet_regsql command with following parameters

 

 

Have a look on the parameter and there uses

Parameters	Description
-ssadd	Add support for SQLServer mode session state.
-sstype p	P is stands for Persisted. Its persist the session data on server
-S	Specify Server Name
-U	Specify User Name
-P	Specify Password

After run you will get the following message, 

that's all .

Step 3 : Open SQL Server Management Studio, Check, A new database ASPState has been created  and there should be two tables, 

• ASPStateTempApplications 
• ASPStateTempSessions 

Now, Just change the configuration string of the State Server Example and Run the same application that  I have explained in State Server.  

Just store Roll and User Name and Click on Submit button, and open ASPStateTempSessions Table from SQL Server Management Studio.. WoW... here is your session data,

Now. do the following Test that I have already explained in State Server Mode.

1. Remove The Serialize Key word from StydentInfo Class
2. Reset IIS and Click on Restore Session
3. Stop the SQL Server Services 

I think I have explained the SQL Server Session mode well.

Advantages and Disadvantages 

Advantages :

• Session data do not  affected if we restart the IIS.
• It is the most reliable and secure session management.
• It keeps data located centrally ,  It can be easily accessible from other application.
• It is  very useful in web farm and web garden scenarios.

Disadvantages : 

• Processing is very slow in nature.
• Object serialization and de-serialization creates overhead  for application.
• As the session data is handled in different server, so we have to take care of SQL server. It should be always up and running.