What is session state?

A session is defined as the period of time that a unique user interacts with a Web application. Session state is a collection of objects, tied to a session are stored on a server.

Why use SQL?

Once you start running multiple web servers for the same web site, the default asp.net session state, InProc, is no longer useful, as you cannot guarantee that each page request goes to the same server. It becomes necessary to have a central state store that every web server accesses.

SQL Server offers you centralized storage of a session state in a Web farm. It also offers the transactional capabilities that provide reliability to most relational database systems. You can use SQL Server to save a session. This process is not as efficient as InProc and StateServer mode, because you must store the information in a different process or on a different server. However, this option may be more efficient than using the aspnet_state service, depending on the actual workload and the database configuration. Once you start saving session state to a SQL database it will also persist through web server restarts and reboots.

For reliability you should consider storing session state for a web farm on a SQL cluster.

Creating the database

• Start Query Analyzer, connected to the server you want to use for state storage.
• Open and execute InstallSqlState.sql script file. By default, InstallSqlState.sql is located in one of the following folders; system drive\ Windows\ Microsoft.NET\ Framework\version\
• If you are using trusted connections to connect to your server, you must change ownership of the state database to sa after creation. In Query Analyzer run use ASPState
exec sp_changedbowner 'sa','true'
• If you are using SQL authentication create a user and password for session state to use. At a minimum this user should havepermissions to execute the stored procedures in the ASPState database. You will have to manually set these, or if you're feeling dangerous, give the state user dbo rights to ASPState.