Using SQL as Session Cache in Azure

Question

I am currently looking for the best way to use session caching in an Azure Webservice. Because multiple Instances are used, standard asp.net Session state does not work.

I found some documents that this can be solved by using an external session state provider which allows using a shared cache (Redis cache) or SQL as an external provider.

But while I found a lot of documentations how to configure the Redis cache, I did not find a single example how to configure an Azure-SQL-Database for caching.

If this is still supported in Azure: Can you provide an example?

Answer 1

You can do this by:

 <sessionState mode="SQLServer"
sqlConnectionString="Server=tcp:[serverName].database.windows.net;Database=myDataBase;User ID=[LoginForDb]@[serverName];Password=[password];Trusted_Connection=False;Encrypt=True;"
cookieless="false" timeout="20" allowCustomSqlDatabase="true" />

A nicer solution is to use table storage, because you can specify the lifetime of an object in table storage.

more info: managing-session-state-in-windows-azure-what-are-the-options

Answer 2

This approach may avoid needing to know how to manually create the AspNetSessionState database objects:

1) Install NuGet package https://www.nuget.org/packages/Microsoft.AspNet.Providers/2.0.0:

ASP.NET Universal Providers add provider support in ASP.NET 4 for all editions of SQL Server 2005 and later and to SQL Azure. If you use these providers to develop your application, the application will be ready for cloud environments like Azure...

It has a dependency on EntityFramework but I guess we live with that. It adds this to your web.config <system.web> section:

<sessionState mode="InProc" customProvider="DefaultSessionProvider">
  <providers>
    <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" />
  </providers>
</sessionState>

2) But for the release config you will want:

    <sessionState mode="Custom" customProvider="DefaultSessionProvider" xdt:Transform="Replace">
  <providers>
    <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider" connectionStringName="YourSessionConnectionStringName" />
  </providers>
</sessionState>

where the xdt:Transform="Replace" is required if you use config transforms and must be removed if not.

and then the matching ConnectionString definition

<connectionStrings>
<add name="YourSessionConnectionStringName" connectionString="server=SessionServer;database=SessionDb;uid=SessionUser;password=SessionUserPassword;" />
</connectionStrings>