Using SQL Azure for Session State

[This article was contributed by the SQL Azure team.]

Hypertext Transfer Protocol (HTTP) is a stateless protocol; the advantage of a stateless protocol is that web servers do not need to retain information about users between requests. However, in some scenarios web site developers want to maintain state between page requests to provide consistency to the web application. To create state from a stateless protocol, ASP.NET has the concept of a session which is maintained from the user’s first request to their last request for that visit to the web site. By default, ASP.NET session is maintained in the RAM of the running web server. However, Windows Azure is a stateless platform, web role instances have no local storage; at any time the web role instance could be moved to a different server in the data center. When the web role instance is moved, the session state is lost. To have a perceived sense of state with a stateless protocol on a stateless web server, you need permanent server side storage that persists even if the web role instance is moved. In this article I will discuss how to use SQL Azure to create persistent storage for an ASP.NET session in Windows Azure.

SQL Azure is a perfect fit for maintaining session in Windows Azure, because there is already a SqlSessionStateStore; a Microsoft session state provider developed for on-premise SQL Server installations. The SQL server provider was developed for local IIS installations across multiple web servers in a web farm that wanted to maintain the user’s state across machines.

The code below is not supported by Microsoft, our support policy for session state using the SQL Azure database is stated as: ”Microsoft does not support SQL Session State Management using SQL Azure databases for ASP.net applications” in this Knowledge Base article.

Creating the Tables

If we are going to use the SqlSessionStateStoreprovider on Windows Azure against SQL Azure, we are going to need to create the appropriate tables and stored procedures. Typically this would be done with the InstallSqlState.sql script that ships with the .NET framework (or Aspnet_regsql.exe's –sstype) , however this script doesn’t work for SQL Azure, because of Transact-SQL differences. Instead we have to use a modified script (see download at the bottom of this blog post).

Here are the instructions to create the databases, stored procedures, and tables needed to store session state on SQL Azure

1. Download a modified Transact-SQL script called ASPStateInstall.sql that will create the ASPState database.
2. Execute the ASPStateInstall.sql script from SQL Server Management Studio on the master database, read more about connecting to SQL Azure with SQL Server Management Studio here.
3. Reconnect SQL Server Management Studio to the ASPState database that you just created.
4. Execute the InstallSqlState.sql script from the download from SQL Server Management Studio on the ASPState database.

Modifying the web.config

Next thing to do is modify the web.config so that Windows Azure uses SQL Azure as storage for the session state. Your web.config should look something like this:

 <sessionState
  mode="SQLServer"
  sqlConnectionString="Server=tcp:...;Trusted_Connection=False;Encrypt=True;"
  cookieless="false"
  timeout="20"
  allowCustomSqlDatabase="true"
/>

Make sure to modify the sqlConnectionString to match the SQL Azure connection string from the SQL Azure Portal for the ASPState database. If you are trying this on an on-premise installation of IIS, the same modification to the web.config will work.

Doing the Clean Up

When installing ASP.NET SQL Session State Management provider with an on-premise SQL Server the install creates a job that the SQL Server Agent executes which cleans up the old session data. SQL Azure doesn’t have the concept of a SQL Server Agent; instead we can use a Windows Azure worker role to clean-up the SQL Azure database. For more information see our SQL Server Agent blog series (Part 1, Part 2, and Part 3). The InstallSqlState.sql script that you ran to setup the database contains a DeleteExpiredSessions. Trimming the expired sessions is as easy as calling this script from the worker role. Here is what the code looks like:

 public override void Run()
{
    // This is a sample worker implementation. Replace with your logic.
    Trace.WriteLine("WorkerRole1 entry point called", "Information");

    while (true)
    {
        Thread.Sleep(60000);

        // Create a SqlConnection Class, the connection isn't established 
        // until the Open() method is called
        using (SqlConnection sqlConnection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["ASPState"].
                ConnectionString))
        {
            try
            {
                // Open the connection
                sqlConnection.Open();

                SqlCommand sqlCommand = new SqlCommand(
                    "DeleteExpiredSessions", sqlConnection);

                sqlCommand.CommandType = 
                    System.Data.CommandType.StoredProcedure;

                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                // WWB: Don't Fail On SQL Exceptions, 
                // Just Try Again After the Sleep
            }
        }
    }
}

Make sure to add the ASPState connection string to the worker role’s app.config or the worker role will never completely initialize when you deploy to Windows Azure. Here is what it will look like:

 <?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="ASPState" connectionString="Server=tcp:…;Trusted_Connection=False;Encrypt=True;"/>
  </connectionStrings>

If you cut and paste the code above, make sure to modify the connectionString attribute to match the SQL Azure connection string from the SQL Azure Portal

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.