While I no longer do .NET development nowadays, I find it hard not to use my previous background as a reference when talking about databases. One specific discussion that came up today was how ASP.NET manages session state.

In order to configure an ASP.NET application to do session state management, you need to configure the section in the web.config file of the web application. There are four modes that are available for use

• OFF- this means that session state is turned off for the web application
• InProc – this means that sessions are kept in memory on the web server. This is the default behavior. You can configure the cookieless attribute if you want the sessionID value in the URL.
• StateServer – this means that you are storing session state in a separate process called the ASP.NET state service
• SQLServer – well, you guessed it, it stores session state in a SQL Server database
• Custom – you can write your own custom mechanism or any other that is readily available as a session storage mechanism

What we as DBAs are concerned about is the use of the SQLServer option as a session state storage mechanism for ASP.NET. As a DBA or a developer, you can create the session state database by using the aspnet_regsql.exe utility that comes with the .NET Framework. Running this utility, by default will create the MembershipProvider database for ASP.NET security outside of Windows authentication. But that’s not what we want. We want to create the database to store the ASP.NET session state. To do that, we need to run the utility from the command-line, passing the correct parameters.

aspnet_regsql.exe -S YourSQLServerInstance -E -ssadd

This will create the database named ASPState to store the stored procedures and functions needed for session state management. But there’s a catch to this. the default will be to create the stored procedures and functions in the ASPState database, however, the tables that will store the data will be in the tempdb database. Now, as a SQL Server DBA, we all know that anything stored in the tempdb database will eventually be flushed out when the SQL Server service restarts. If you have active sessions on your ASP.NET web application, you will lose these anytime your SQL Server service is restarted. What you need to do is explicitly pass another parameter on the aspnet_regsql.exe utility to store everything in the ASPState database.

aspnet_regsql.exe -S YourSQLServerInstance -E -ssadd -sstype p

The -sstype parameter explicitly tells the utility to create the tables in the ASPState database instead of the tempdb database (the P value stands for persisted) ill survive service restarts so you need to make use of the ASPState_Job_DeleteExpiredSessions job specifically created to do this. The default behavior of this job is to run every minute which would probably be not a good idea especially as it logs all of the job execution information in the msdb database.

So, the next time you need to work with ASP.NET applications storing session state information in a SQL Server database, check this out with your web developers