Using SQL Azure for Session State - SQL Azure Team Blog - Site Home - MSDN Blogs

Using SQL Azure for Session State

Using SQL Azure for Session State

Rate This
  • Comments 23

Since I wrote this post in 2010, Microsoft has come out with an offically supported version of ASP.NET sessions that run on SQL Azure.  The ASP.NET Universal Providers extend Session, Membership, Roles and Profile support to SQL Compact Edition and SQL Azure.  This is the recommend approach.

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 SqlSessionStateStore provider 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.

Attachment: AzureSession.zip
  • What are your thoughts on calling the stored procedure on session end in global.asax on the condition that it has not already been called in the last 60 minutes.  The only reason I ask is that I don't want to pay for a continously running worker role for calling a proc once an hour that takes seconds to run.  I realize the proc would be called by all web roles each hour, but I imagine it is fast after it just ran seconds prior.

  • Michael,

    Threading.Sleep parameter is in milliseconds, so 60,000 milliseconds is every minute. I don't think this will work, from MSDN:

    The Session_OnEnd event is supported only when the session state Mode property is set to InProc, which is the default. If the session state Mode is StateServer or SQLServer, then the Session_OnEnd event in the Global.asax file is ignored. If the session state Mode is set to Custom, then support for the Session_OnEnd event is determined by the custom session-state store provider.

  • I see the 60 seconds now.  The exact amount of sleep time wasn't really my point.  But thanks for the information on session end.

    My goal is to best utilize processing resources.  I am only assuming at this point that the cleanup procedure is fast running, and that whatever interval it is run at the worker role would have a low percentage of average cpu usage.  Can you quantify it?

    What do you think of setting up a timer on Application_Start?

    I'm coming from the perspective of someone with a single dedicated server now, but wants to go to the cloud now so that it can scale instantly when the slashdot effect hits.  In the meantime an owner of that site doesn't want to pay twice the hosting fees.

    Thanks.

  • Michael:

    I know what you are trying to do, have seen this many times before in IIS/SQL Server environments; an attempt to run a timed event off the web server.  None of the solutions are very good, most will work sorta.    Since most of the options don't guarantee that the session timeout on time you have to judge how important to you is it that the session timeout exactly at the right time.  For example, is it just inconvenient that the session times out late, is it a security risk, or do you lose money?  Disclaimer, none of these are recommended:

    -Starting a thread, i.e. a Timer on Application_Start is risky because of error handling.  If the thread fails via an exception, then it will die and the sessions will not time out.

    -Another option is to just run the clean up when the home page is visited.  However, if no one visits the homepage then the session will not time out.  

    -You could clean up on every page (Page_Load of the master), however that would be overkill, and cause a very slight performance lag.

    -You could clean up right before every reference to the Session object, also overkill.

    The performance and of DeleteExpiredSessions should be fast if run often.  You could add a covered index to make it even faster.  

    Or, you could just not use Sessions; something I personal try to avoid.

  • I'm not familiar with the session state data model.  I didn't realize an expired session could still be used.  I figured that a session record had a last used column and that any stored proc that loaded a session or its data made sure it was not an expired record before using it.  I assumed the DeleteExpiredSessions procedure was just a cleanup operation to keep the database footprint as small as possible.

    In my case I don't use session for anything too important.  I generally reload data for a given page from either a cache or database as appropriate using routing and querystring parameters in the url.

    I also have other windows services installed on my web server that need to move somewhere.  Maybe I'll encapsulate each of those tasks in separate classes, then use a single azure worker role to schedule each of them to have the time they require to process work.  I'll look into making them robust enough that they could be called from an event in a web site initially, and then moved into an azure worker role when a site grows beyond one web role.  I thought I saw an article on multiple tasks in a single azure worker role on msdn somewhere, but I can't seem to find it right now.

  • Hi Wayne

    Since the application is running on Azure it would be more decent to grab the sqlConnectionString from the ServiceConfiguration.cscfg instead of hard coding the value in the packaged web.config.

    What do you think is the best approach to do this?

    Djon.

  • Djon:

    Your right putting it in the .csfg is probably better than the web.config.  You can find out more information about this here: msdn.microsoft.com/.../ee405486.aspx (about mid page).  

  • It seems not really straightforward to change the connectionString in code or to inherit and override the default classes used for session handling, in the way shown by Brian Hitney (www.structuretoobig.com/.../Azure-Miniseries-3-ServiceConfig-vs-webconfig.aspx).

    What we turned out doing is creating a quick workaround. It's basically changing the configuration setting in your web.config file while starting the Azure application.

    You do so by adding some simple code to the WebRole.cs file. Extend the OnStart method with the following code will cause the web.config file to be updated with the value of 'SessionDBConnectionString' from your ServiceConfiguration. Be aware any time you make changes to the web.config file; your application will be restarted and the OnStart method executed again!

    // Change web.config setting fetching the SQL Server connectionstring for sessions from the Azure config

               var myConfiguration = (Configuration)WebConfigurationManager.OpenWebConfiguration("~");

               var section = (SessionStateSection)myConfiguration.GetSection("system.web/sessionState");

               if (ConfigHelper.SessionDBConnectionString != section.SqlConnectionString)

               {

                   System.Diagnostics.Trace.TraceInformation("Changing session database connection string");

                   section.SqlConnectionString = RoleEnvironment.GetConfigurationSettingValue("SessionDBConnectionString");

                   myConfiguration.Save();

               }

               else

               {

                   System.Diagnostics.Trace.TraceInformation("Session database connection string already up to date");

               }

  • @michael:

    If you don't have a need for a separate worker role to manage the cleanup you have a few options.

    1 - As Wayne mentioned,  punch that code on the home page. But if you have high traffic, wrap a counter around it so you don't ping it every millisecond.

    If you don't have traffic...no it won't clean up. But if you don't have any traffic, you have other concerns.

    2 - Take that code and start a thread...but not from the Application Start, rather from the Run() method exposed on the WebRole. A few extra lines of code can manage a health check on the thread to restart it when/if it goes down

    I think the code on MSDN you were looking for was this..and yes, it is a great primer and illustration of multithreading...and by therefore by proxy - inserting WorkerRole type functions into a WebRole (assuming you have the resources available on that VM).

    blogs.msdn.com/.../reliable-azure-processes-part-4-scaling-down.aspx

    3 - Don't start "events" from your WebRole...you should look into using Azure queue's

  • I found some problems with the ported code as reported here:

    social.msdn.microsoft.com/.../cd2f0b6f-31b8-4982-ad32-79cae92211af

    where I was seeing casting issues from DBNull to Byte[]

    I believe your port erroneously drops the "READTEXT" blocks, and does not replace them with anything.  I posted a version that works for me on that thread.  Perhaps you can review my changes and update your source for others incase they run in to the same issue

  • I am running into the same issue as Peter McEvoy.

    Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

    Stack Trace:

    [InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'.]

      System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +1545

      System.Web.SessionState.SqlSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +86

      System.Web.SessionState.SessionStateModule.GetSessionStateItem() +178

      System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) +1076

      System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +115

      System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +371

  • As to the worker role comment I raised earlier... I am now using a worker role to do the cleanup, but it is not dedicated.  Within my worker role I have a series of WorkerRoleTask that each get instantiated from a custom configuration section in my WorkerRole.  Via configuration I can have all my tasks run in a single worker role, or break out processor intensive tasks into separate worker roles without any code changes.  Once I have the code refined I'll post a link to a code sample and article.

  • Michael: You can get Peter's changes here:

    social.msdn.microsoft.com/.../cd2f0b6f-31b8-4982-ad32-79cae92211af

    That is a cool concept for your WorkerRole, something I have been playing with in the last couple of days.  Spinning new threads for each sub-role.

  • I am using the new script now.  It seemed to work under normal testing in my one free month pass account.  Now http://www.nexul.com/ is running on a pay production account and I am doing some final tweaks and testing before opening to the public.  I started encountering performance issues.  On random occasions the page takes a long time to load. I wasn't sure why, until I received the following error.  Does anyone have any ideas?  Or is Microsoft getting closer to having an official Sql Session State Script they will support?

    [SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]

      System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +404

      System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412

      System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1363

      System.Data.SqlClient.SqlDataReader.CloseInternal(Boolean closeReader) +308

      System.Data.SqlClient.SqlDataReader.Close() +293

      System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +792

      System.Web.SessionState.SqlSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +86

      System.Web.SessionState.SessionStateModule.GetSessionStateItem() +178

      System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) +1076

      System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +115

      System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +371

  • Michael:  if you are going to see support you would get it from the Windows Azure team (they are in charge of the Context for Windows Azure). I know they have some alternative solutions you might want to research there.  The other options is to optimize your queries by checking the procedure cache (I wrote a blog post about this).  You might also want to reduce your session calls.

Page 1 of 2 (23 items) 12
Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post