One of the most common questions in developing ASP.NET applications on Windows Azure is how to manage session state. The intention of this article is to discuss several options to manage session state for ASP.NET applications in Windows Azure.
Session state is usually used to store and retrieve values for a user across ASP.NET pages in a web application. There are four available modes to store session values in ASP.NET:
You can get more information about ASP.NET session state here.
The In-Proc option, which uses an individual web server’s memory, does not work well in Windows Azure. This may be applicable for those of you who host your application in a multi-instance web-farm environment; Windows Azure load balancer uses round-robin allocation across multi-instances.
For example: you have three instances (A, B, and C) of a Web Role. The first time a page is requested, the load balancer will allocate instance A to handle your request. However, there’s no guarantee that instance A will always handle subsequent requests. Similarly,the value that you set in instance A’s memory can’t be accessed by other instances.
The following picture illustrates how session state works in multi-instances behind the load balancer.
Figure 1 – WAPTK BuildingASP.NETApps.pptx Slide 10
Table Storage Provider is a subset of the Windows Azure ASP.NET Providers written by the Windows Azure team. The Table Storage Session Provider is,in fact, a custom provider that is compiled into a class library (.dll file), enabling developers to store session state inside Windows Azure Table Storage.
The way it actually works is to store each session as a record in Table Storage. Each record will have an expired column that describe the expired time of each session if there’s no interaction from the user.
The advantage of Table Storage Session Provider is its relatively low cost: $0.14 per GB per month for storage capacity and $0.01 per 10,000 storage transactions. Nonetheless, according to my own experience, one of the notable disadvantages of Table Storage Session Provider is that it may not perform as fast as the other options discussed below.
The following code snippet should be applied in web.config when using Table Storage Session Provider.
<sessionState mode="Custom" customProvider="TableStorageSessionStateProvider"> <providers> <clear/> <add name="TableStorageSessionStateProvider" type="Microsoft.Samples.ServiceHosting.AspProviders.TableStorageSessionStateProvider" /> </providers> </sessionState>
You can get more detail on using Table Storage Session Provider step-by-step here.
As SQL Azure is essentially a subset of SQL Server, SQL Azure can also be used as storage for session state. With just a few modifications, SQL Azure Session Provider can be derived from SQL Server Session Provider.
You will need to apply the following code snippet in web.config when using SQL Azure Session Provider:
<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" />
For the detail on how to use SQL Azure Session Provider, you can either:
The advantage of using SQL Azure as session provider is that it’s cost effective, especially when you have an existing SQL Azure database. Although it performs better than Table Storage Session Provider in most cases, it requires you to clean the expired session manually by calling the DeleteExpiredSessions stored procedure. Another drawback of using SQL Azure as session provider is that Microsoft does not provide any official support for this.
Windows Azure Caching is probably the most preferable option available today. It provides a high-performance, in-memory, distributed caching service. The Windows Azure session state provider is an out-of-process storage mechanism for ASP.NET applications. As we all know, accessing RAM is very much faster than accessing disk, so Windows Azure Caching obviously provides the highest performance access of all the available options.
Windows Azure Caching also comes with a .NET API that enables developers to easily interact with the Caching Service. You should apply the following code snippet in web.config when using Cache Session Provider:
<sessionState mode="Custom" customProvider="AzureCacheSessionStoreProvider"> <providers> <add name="AzureCacheSessionStoreProvider" type="Microsoft.Web.DistributedCache.DistributedCacheSessionStateStoreProvider, Microsoft.Web.DistributedCache" cacheName="default" useBlobMode="true" dataCacheClientName="default" /> </providers> </sessionState>
A step-by-step tutorial for using Caching Service as session provider can be found here.
Other than providing high performance access, another advantage about Windows Azure Caching is that it’s officially supported by Microsoft. Despite its advantages, the charge of Windows Azure Caching is relatively high, starting from $45 per month for 128 MB, all the way up to $325 per month for 4 GB.
I haven’t discussed all the available options for managing session state in Windows Azure, but the three I have discussed are the most popular options out there, and the ones that most people are considering using.
Windows Azure Caching remains the recommended option, despite its cons but developers and architects shouldn’t be afraid to decide on a different option, if it’s more suitable for them in a given scenario.
This post was also published at A Cloud Place blog.
For each time a session (with properties including expiry time) is created on a session table. For the subsequent request, it will be check against the table to see if it exists. For the scenario we need delete the record which expiry time equals or older than current time. This is to enable timeout when there is no activity against session.
In order to automatically delete expired session, most of the time we use Windows Azure Worker Role to perform the batch activity.
For each time a session (with properties including expiry time) is created on a session table. For the subsequent request, it will be check against the table to see if it exists. For the scenario we need delete the record which expiry time equals or older than current time. This is to enable timeout when there is no activity against session.In order to automatically delete expired session, most of the time we use Windows Azure Worker Role to perform the batch activity.
In the last post, I showed you how to set timezone in Windows Azure. In this post, I would like to show how to manage timezone in SQL Azure.
At the time this post is written, unfortunately there’s no to change SQL Azure timezone.
Likewise in Windows Azure VM, SQL Azure use UTC as default timezone, regardless which datacenter you choose. Meaning that when you call getdate() function, it’s going to return you the current date and time according to the server’s timezone.
If you are building a new system with new created database without any existing data, I would consider UTC default timezone is fine.You can store the time as UTC inside your database. Optionally, if you want to ensure your local timezone to be displayed in UI, you can actually control it at the presentation layer.
However, imagine you have existing application with bunch of data (including date time column) using your own local timezone, it will definitely messed up when you move to SQL Azure UTC timezone. The data inconsistency will definitely occur. Then you’ll need to determine either:
a. to convert your current data to UTC-timezone compliance OR
b. change your any SQL Objects (including functions, stored procedure, etc.) to match your local date time.
I’ve tried to look for various solution on the internet, mailing list, or even talk with Microsoft MVPs and Product Group folks but still there’s no single answer that could really satisfy me. Accordingly, I would call the following technique as a trick or workaround, instead of saying a solution.
Back to the second point discussed at above, converting your current data to UTC-timezone maybe very painful especially you have a lot of date and time data inside your table. In my personal opinion, I would prefer changing SQL Objects. But one very important consideration is to make it flexible and elegant enough, especially when in the future, we would need to convert back to the original state.
Well, you could definitely Find and Replace any objects inside SQL schema from getdate() to select dateadd(hh,8,getdate()). But of course it’s not going to be a nice way. As such, here’s what I’ve done to make it better.
To call this function, you can either use:
You would need to find and replace entire SQL objects to identify which of the object that use getdate() function. Before doing so, you’ll of course need to generate the entire database schema. You can do so using the generate script wizard in SQL Server Management Studio.
The next step is to find which the database object that use getdate() function. Change it to dbo.GetLocalDate(DEFAULT). Execute it by altering the existing object.
By doing so, I can easily switch to UTC timezone system, just by only alter my GetLocaDate() function with Default value 0 on @TImezoneDiffInHour parameter. I don’t have to regenerate and convert back my objects again.
Frankly speaking, it may not the the best solution, but it just serves as workaround and fits my need. I won’t be surprise if there’s a better solution in the future, hopefully we can natively change the timezone in SQL Azure.
In certain circumstances it’s very challenging for us to troubleshoot SQL Azure issues since the SQL Azure Server is actually a virtual server (TDS Endpoint), not an actual physical machine as current on-premise SQL Server is.
As announced by SQL Azure team, SQL Azure Diagnostic Tool is a tool that has been developed to shorten the data collection process when troubleshooting SQL Azure issues. This tool is available to be download for free from here.
SQL Azure Diagnostic Tools is built on top of .NET 4.0 and SQL Server Reporting, as such some prerequisites are needed:
Having done installing the SQL Azure Diagnostic Tool, you will see a light-weight tool as this:
To use it, simply enter details of your SQL Azure account including:
And simply click on GO button to begin the diagnostic.
SQL Azure Diagnostic Tool will provide you a execution time of various kind of queries so that you could analyze it in more detail, what is the root cause your application’s issue.
I believe this is still an very early stage of tool and will be keep improving and improving. We would hope that in the next version of update, we can see more comprehensive analysis.
If you want to see how much size has you used in your SQL Azure database, you can definitely see it on the SQL Azure Developer Portal as shown below.
However, for some reason, sometimes you would either need to get the size more precisely or retrieve the numbers programmatically.
Here’s the query for you to retrieve the size of your database.
After running in on my SQL Server Management Studio R2, here’s the result:
If you need to explore in more detail how much size for each table, you can also use the following query to see.
You may want to learn more about SQL Azure tips and tricks, be sure to check out the following posts:
Hope this helps.
It was a great opportunity to talk about SQL Azure to SQL User group Singapore. In a nutshell, SQL Azure is SQL database as service that is hosted on the Microsoft cloud platform.
In the talk, I started my presentation with overview idea of cloud computing, introducing Azure Service Platform, and finally deep dive on more detail on SQL Azure.
As usual, rather than showing presentation slide, I also showed the audience 4 demos including:
Connecting and administering SQL Azure
Migrating to SQL Azure
Spatial support in SQL Azure
Silverlight version of SQL Azure management tools
There are a few photos taken as followings.
Finally, you can download my presentation slide here.