How to store ASP.NET session state on SQL Server

ASP.NET Session State enables you to choose where you want to store website session data. There are four possible modes: InProc (default mode), StateServer, SQLServer and Custom mode. By default, InProc mode stores sessions in application process. On high traffic websites with thousands of visitors, session data will quickly overburden web server, slow down application and decrease performances. In the other hand, using of SQLServer mode is more scalable option. SQLServer mode uses SQL Server database to store sessions. Because sessions are stored in database and outside of ASP.NET process, web server memory remains free and application executes fast even on high traffic websites.

Another problem when default InProc mode is used, is that sessions are deleted every time when application restarts or stops. This could happen very often, see Restart and Stop of ASP.NET Application tutorial. Sessions stored in SQL Server database remains intact during web application restarts. Also, depending of configuration, sessions could survive SQL Server restarts too, or even Windows restarts. This fact makes SQL Server most reliable option for storing ASP.NET sessions.

How to install Session State schema on SQL Server using InstallSqlState.sql or InstallPersistSqlState.sql scripts

To use SQL Server as storage for sessions, first step is to create appropriate database schema on SQL Server. One of the ways to do this is to execute InstallSqlState.sql or InstallPersistSqlState.sql T-SQL scripts against desired SQL Server. Both script files are located in .Net framework version folder. For example, for .Net 4.0, the address could be:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallSqlState.sql

Please notice that you can't use script of ASP.NET 1.0 for use on ASP.NET 2.0 application.

Main difference between InstalSqlState.sql and InstallPersistSqlState.sql is in which database tables are created. For example when InstallSqlState.sql is executed, SQL Server will create new database named AspState and all needed stored procedures in it. But, two required tables: ASPStateTempApplications and ASPStateTempSessions tables will be located in tempdb database.

In the other hand, if we use InstallPersistSqlState.sql, after script is executed, AspState database will contain all procedures and tables in it.

So, only difference during installation is location where ASPStateTempApplications and ASPStateTempSessions tables will be created. That is the cause of main difference in run time: if two required tables are stored in AspState database, i.e. InstallPersistSqlState.sql is used, sessions will not be lost even if SQL Server machine is restarted. But, if tables are in TempDB database (InstallSqlState.sql is used), sessions will be lost when SQL Server restarts.

Installing Session State schema using aspnet_regsql.exe tool

ASPNET_REGSQL.EXE is a command line program located in .Net Framework version number folder. For example, for .Net Framework 4.0, path could be:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe

You don't need to navigate to this folder. Faster way is to open Visual Studio Command Prompt which allows direct access to VS tools.

ASPNET_REGSQL.EXE supports several parameters:

Some example configuration, which uses custom database named "YourCustomDatabaseName" for session storage and uses windows authentication to connect to SQL Server, would be:

aspnet_regsql -ssadd -sstype c -d YourCustomDatabaseName -E

If you run aspnet_regsql without parameters, it will show wizard as Windows application. If you prefer graphical user interface, you can use this wizard to specify each parameter and avoid using of command line arguments.

Installing Session State with SQLServer mode on shared web hosting

By default, Session state that uses SQLServer mode requires SQL Server Agent. SQL Server Agent runs job to delete data on session expiration. In most cases, Agent is not allowed by hosting providers in shared web hosting scenario. Also, on shared web hosting you are probably not allowed to create new database in code. So, only option to use SQLServer mode on shared web hosting, is to remove parts in SQL code that create job and create new database.

Like in previous example, we'll use command line tool aspnet_regsql.exe. When -sqlexportonly parameter is used, aspnet_regsql.exe will not execute SQL. It will just create .sql script of given name.

For example, to create T-SQL script named "SessionScript", which creates custom database "SessionDb", we'll use:

aspnet_regsql -ssadd -sstype c -d SessionDb -sqlexportonly c:\SessionScript.sql

Generated scripts include part for creating database. But, on shared hosting we'll create database using web hosting control panel or use some existing predefined databases. Because of that, we need to remove this part from script. T-SQL code which should be removed is located on top of the script and in our case will look like this:

USE master
GO

/* Create and populate the session state database */

IF DB_ID(N'SessionDb') IS NULL BEGIN
 DECLARE @cmd nvarchar(500)
 SET @cmd = N'CREATE DATABASE [SessionDb]'
 EXEC(@cmd)
END

Since shared hosting providers mostly don't allow SQL Server Agent too, you need to remove parts that create job for deleting of sessions. After that, script can be executed against database in shared hosting scenario.

But, we have another problem. Since we'll not use SQL Server Agent for deleting sessions, they will remain in database. We need other mechanism to delete expired session.

Expired sessions are deleted when DeletedExpiredSessions stored procedure is executed. By default, this procedure is called by Agent. As an alternative, you can call this procedure using line:

Exec [dbo].[DeleteExpiredSessions]

Possible solution is to add this line to the beginning of procedure TempGetAppID. In this case expired sessions will be deleted any time session data are requested. On high traffic web sites, this could be too often and possibly overburden SQL Server. Also, you probably don't need to check expired session so often.

Another option is to create scheduled task which will execute in regular time intervals and call DeleteExpiredSessions procedure. Scheduled task can be in form of ASP.NET page, web service, console application etc. For example, you can execute .aspx page every minute. Since this page is executed every 60 seconds, it will not delete sessions exactly on session timeout. It could be anywhere between 20 and 21 minutes. But, this option is much more scalable and 1 minute tolerance is usually good enough. You can execute task more or less frequently if needed. More about scheduled jobs in ASP.NET see in Scheduled tasks in ASP.NET tutorial.

Session State in web farm or web garden scenario

If SQLServer mode is used, Session state will work if website is hosted on multiple servers (web farm) or multiple processors (web garden). In case of multiple servers, you need to do two additional steps:

- Have same application path on all web servers.

- Use same machine key on all web servers. Machine key is used for encryption/decryption of session cookies. If machine keys are different, one server can't decrypt session cookie saved by other servers, so sessions could not be read.

Web.config changes to store sessions on SQL Server

After running selected T-SQL script or using aspnet_regsql.exe tool, required database schema is installed. Next step is to edit <sessionState> element in web.config file. <sessionState> element will tell ASP.NET that we'll use SQLServer mode and will specify database connection string. Default mode is InProc (inside of ASP.NET process). To store session state on SQL Server, ASP.NET requires to change mode attribute to "SQLServer" and to specify sqlConnectionString attribute to your SQL Server. For example:

<sessionState
      mode="SQLServer"
      sqlConnectionString="data source=127.0.0.1;user id=username;password=password"
    />

That is all you need to do. If you start web application, session data should be visible in ASPStateTempSessions table. You can read or change data using SQL Server Management Studio or directly using ASP.NET code.

On shared hosting, you need to set allowCustomSqlDatabase parameter to true and add database name in sqlConnectionString parameter. Possible configuration for sessions stored in "SessionDb" database could look like this:

<sessionState
      mode="SQLServer"
      allowCustomSqlDatabase="true"
      sqlConnectionString="Database=SessionDb;data source=127.0.0.1;user id=username;password=password"
    />

ASP.NET Session State on SQL Server Express

On SQL Server Express, you need to enable SQL Server Agent extended stored procedures (Agent XPs). You can do it using following SQL code:

Advantages of using SQL Server as Session State storage

- SQL Server is most reliable option. Sessions stored in SQL Server's database will survive ASP.NET restarting and recycling, or SQL Server restarts. There is an option to use SQL cluster to avoid "single point of failure" which makes this option even more reliable.
 - SQL Server is more scalable option, when compared to State Server and especially to InProc

Sql Server mode disadvantages

- Sessions using SQL Server database are usually little slower than State Server. This disadvantage is small (about 15-20%) and often ignored.
- SQLServer mode requires most work during implementation. InProc works by default, and State Server needs less configuration than SQLServer.
- If you don't use SQL Server for your website already, there is an additional cost for obtaining a new license. SQL Server is not free. In this case, you may consider Custom mode, and store Session variables to database you already have (e.g. MySQL, Oracle etc.).
- All objects stored in session variables must be serializable. With InProc mode, it is possible to store any .Net type in Session. But, SQLServer mode requires serialization/deserialization of object. Process of serialization and deserialization takes some time which could decrease performances. Often is faster to create several primitive types (strings, integers etc.) which don't need serialization, than to serialize/deserialize one complex class. If possible, store only primitive types in Session to avoid serialization and get fastest execution.
- Since T-SQL script creates database with strict name "AspState", this mode usually is not available on shared hosting. Also, if you have multiple web applications on same dedicated server, they will all use same database for sessions. However, these problems could be solved. To see how to specify database name in sqlConnectionString, read Microsoft's support fix: Using one SQL database for all applications for SQL Server session state may cause a bottleneck

How to uninstall Session State from SQL Server

Notice that in the same folder where InstallSqlState.sql is located, is also UninstallSqlState.sql file. As its name implies, it can be used to uninstall session state schema. If you try to run it on live website, script will return an error "Cannot drop the database 'ASPState' because it is currently in use". So, you must change session state mode, or sqlConnectionString of <sessionState> element, or completely stop ASP.NET process before uninstalling AspState database.

Conclusion

ASP.NET supports four modes to specify where Session state is stored. That are InProc, SessionState, SQLServer and Custom. SqlServer is often recognized as most reliable and scalable option. Reliability comes with cost, because SQL Server is also slowest mode (about 20% slower).

You can experience different errors and problems when try to enable sessions on SQL Server. In many cases, error is related to insufficient rights. Database user should be enabled to select, insert and update data in tables and right to execute stored procedures.

Although ASP.NET Session State is improved a lot, especially when compared to sessions in classic ASP, in many cases it is not best option for state management. Session variables are deleted when session expires, which could be a problem. There are other methods, like Profile properties or cookies that could be used to keep data between sessions. More about different options about how you can save visitor's data, you can find in ASP.NET Session State Alternatives tutorial.

Happy coding!

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |