Articles → .NET → Storing Session In Database

Storing Session In Database

1. Memory of the web server (in process).
2. Memory of the machine dedicated to store session variables (state server).
3. In SQL Server database.

Advantages Of Storing Session In Database

1. Scalability → Scalability is the web server's ability to maintain site availability, reliability and performance as the amount of web traffic increases. As the session state is stored in the database so the web server can maintain the availability, reliability and performance easily and hence the scalability increases.
2. Reliability → As the data is physically persisted in the database, it is more reliable than any other option.
3. Security → SQL server is safer as compared to the in-memory or the server state option.

Configuring SQL Server For Storing Session State

1. Temporary storage → In this case session state is stored in the tempdb database. The tool creates the database ASPState and adds certain stored procedures for maintaining session into it. The tool also creates required tables in tempdb database. If you restart the SQL server the session state is not persisted.
2. Persistent storage → In this case also the tool creates the ASPState database and create stored procedure for maintaining the state. But session state is stored in ASPState database. Data is persisted if you restart your SQL server.
3. Custom storage → Both the session state data and stored procedures are stored in custom database. The database name is specified in configuration file.

Syntax

aspnet_regsql -ssadd(or -ssremove)  -S <server_name> -E -sstype p

1. -S → specifies the ip address or name of the sql server in which you want to store session state.
2. -U → Specifies the userID to be used when connected to the web server.
3. -P(Password) → Specifies the Password to be used when connected to the web server.
4. -ssadd → Adds supports for the sql server mode session state.
5. -ssremove → Removes supports for the sql server mode session state.
6. -E → Indicates that you want to use integrated security while connecting to sql server.
(Note → If option -E is used then options -U and -P options are illegal)


7. -sstype → Type of session state support. Options are
   1. t for temporary storage.
   2. p for persistent storage.
   3. c for custom storage.
8. -d → Name of the custom database if sstype is c

aspnet_regsql -ssadd -S .sqlexpress -E -sstype p

Configuring Website To Store The Session State

1. Mode →
   1. Off → Indicates that session state is turned off.
   2. InProc → Indicates that session state is stored in the sever's memory.
   3. StateServer → Indicates that session state is stored in state server.
   4. SQLServer → Indicates that session state is stored in SQL server database.
   5. Custom → Indicates the custom mechanism for session state storage using provider model is asp.net.
2. sqlConnectionString → If mode is SQLServer then you must specify this attribute. This attribute specifies the connection string of sql server database used for storing session state. If you are using custom storage options then only you need to specify the database name in the connection string.
3. allowCustomSqlDatabase → If you want to store session state in a SQL Server database of your own, then you must set this attribute to true.

<sessionState mode="SQLServer" sqlConnectionString="data source=.sqlexpress; integrated security=true"> </sessionState>