17 June, 2009

How to Store Sessions in SQL Server

We can maintain the session for asp.net application in the following three ways.
  1. Inproc : This will store the session in the memory of the web server. This is mostly used and will be lost when the application crashes. But the advantage with this is the performance. As the session is stored in the web server itself, this is very fast compared to other methods.
  2. State Server: This will store the session in the memory of a machine, which is dedicated for the purpose of storing sessions. This is efficient and scalable.
  3. SQL Server: This will store the session in SQL Server. To store a session in SQL Server, we need to configure the sql server. This is also efficient, scalable and secure implementation of storing a session.
Now Discuss about configuring the SQL server to store the state:

This can be done by executing a simple file named 'aspnet_regsql.exe'. This file is located in the .NET framework folder.

Follow the steps to configure the Database server,
  1. Open the command prompt and goto OperatingSystemDrive\WINDOWS\Microsoft.NET\Framework\v2.0.50727
    • Note: The file is available in v2 only. Though you are using Asp.net Framework v3 or v3.5 you need to goto 2.0.50727 only.
    • As There wont be any change in the sql queries to create a database tables and stored procedure with the version of asp.net framework, They haven't provided with the file in v3 and v3.5 folder.(This is my understanding, the actual reason may be different)
  2. If you just type aspnet_regsql.exe and press enter, this will open a GUI to specify the Database server and the name of Database. If you don't specify any database name the default name will be 'sqldatadb'.
  3. This will create a database with tables and stored procedures, but the database is not yet ready to store the sessions.
  4. For completing the configuration we need to run the command aspnet_regsql.exe -ssadd -sstype c -d DATABASENAME -E.(Give the Database Name if you specity any in step 2).
  5. For more details related to options for this command can be found here.

With this the database is configured to store the sessions. Though it is verymuch scalable and secure, this has performance issues when compared to 'inproc' implementation.

Note: We can run step4 mentioned above in Visual Studio Command prompt to created the Database to store sessions in one shot.

No comments:

Post a Comment