Tuesday, August 5, 2014

Session management using SQL Azure and Update on automatic session clearance

Session management using InProc in azure roles is dangerous thing!!!
Why InProc is dangerous?
Create an azure cloud service project and add a web role in it. Then I added a sample page named as Session demo in it. To test the session data saving and retrieval I added following control in the <form> control present on the page –
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="btnAddToSession" runat="server" OnClick="btnAddToSession_Click" Text="Add To Session!!" />
            <br />
            <br />
            <asp:Button ID="btnReadFromSession" runat="server" OnClick="btnReadFromSession_Click" Text="Read From Session!!" />
            <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
            <br />
            <br />

            <asp:Label ID="lblInstanceId" runat="server" Text="Label"></asp:Label>
On the code behind I added below simple code to add values in session and retrieve it. Also in page load event I am retrieving the id of role instance to check that, my current request is being served from which instance.
public partial class SessionDemo : System.Web.UI.Page
        protected void Page_Load(object sender, EventArgs e)
            lblInstanceId.Text = "You are being served from - "+ RoleEnvironment.CurrentRoleInstance.Id;       

        protected void btnAddToSession_Click(object sender, EventArgs e)
            Session["MySession"] = TextBox1.Text;

        protected void btnReadFromSession_Click(object sender, EventArgs e)
            if (Session["MySession"] != null)
                Label1.Text = Session["MySession"].ToString();

In web.config file as shown below, I have configured my session state as InProc under <system.web> tag –
<sessionState mode="InProc"/>
Now right click on cloud Service project, select Properties and set the application to run in full emulator and full IIS as shown below -
Note – The option to run the cloud service project locally in Full Emulator mode is applicable till the Azure SDK 2.3 only. In Azure SDK 2.4, full emulator is deprecated. I don’t know the reason why they have deprecated full emulator mode in Azure SDK 2.4.
Now we always should set the number of instances for an azure role as minimum of 2. Therefore right click on web role node present in cloud service project and in properties set the instance count as 2.
We all set to see how InProc session management in Azure web role is devil. Set the cloud service project as startup project and I have set the Sessiondemo.aspx page as startup page and here I click F5. And here what I see on my page and in my IIS –

So as you can see, in IIS there are two instances got created as expected and below that the UI shows simple design to test sessions. Also if you see in IE, my current request is being server from INSTACE 0.
Now let’s say I add the value to session as “kunal” by clicking on button “Add to Session” and retrieve it from session by clicking on button “Read from Session” and I found that, my requests are still getting served from INSTACE 0 and session value is being retrieved correctly. Now I right click on instance 0 website in IIS and then I select the options as Manage Web Site | Stop. This actually stops the 0th instance running in IIs. Remember, my project is still running in debug mode. I just stopped one of the role instances of my azure application.

Once the IIS website is stopped you can view the black icon on the web site as shown. This confirms that my website is stopped.

Now I refresh my browser 2-3 times. This means now my request will start getting served from INSTANCE 1 of IIS website because instance 0 is stopped and here is what I see –

As you can see above, my request is being served from INSTACE 1 and my Session value is not retrieved on refresh. Therefore I click on button “Read from session” again and DAMN!!!
I am not receiving the value “kunal” that I had added in my session. This is the problem!!! Ideally I should have received my session values irrespective from which instance of role my request is getting served.
In Azure environment there is always possibility that, the instance of role gets recycled due to hardware failures or other errors. Means if you configure only 1 instance for your role then there is possibility that, your role instance is down and you DON”T GET HIGH AVAILABILITY for your application. Therefore it is always BEST PRACTICE TO HAVE ATLEAST 2 INSTANCE for your azure role.
But again, if you have 2 instance configured for your web role then session management using INPROC will not work as shown above!!!
What is the Solution?
You should always store the session state outside your role environment. How do I do that?
You have in total 3 options to achieve session management in multi instance environment.
1.   Use ASP providers to store session state in Azure Table Storage –

Don’t even think of this option. This is the cheapest but obsolete and also the library to be used for session storage is from Azure Storage client library version 1.7 and already Azure Storage Library version 4+ is out. - ******** link - https://www.nuget.org/packages/WindowsAzure.Storage/

If you feel that, you can update old ASP provider’s library code to suit to latest azure storage library then great. Do it and share the library with me also. J

This was the cheapest way to do it and unfortunately no more supported.

2.   Use Azure Managed Cache Service or Redis Cache –

Don’t even think of this option unless you are ready to pay a lot to your Azure subscription. This is officially supported by Microsoft but too much costly. As it is very costly, obviously supported officially by Microsoft. J

3.   Use SQL Azure/ On premise SQL Server -

The excellent solution as of today in terms of support and cost. Almost 99% of projects have SQL Azure/ SQL Server as part of their existing projects therefore it is always good to store session in SQL Azure DB or SQL Server.
So let’s look at the implementation part of it. In the implementation part, I will be showing SQL Azure DB usage to store the session state for Azure web role application.
I am using the technology stack as – Azure SDK 2.3, VS 2013 and SQL Azure.
To implement the SQL Azure as session storage for Azure role you need ASPNET UNIVERSAL PROVIDERS. Therefore install the ASP NET universalproviders for web role project as shown below –
Stop the running project and in visual studio select options as - Tools | Library Package Manager | Package Manager console.

Type the command as shown in below –

Install-Package Microsoft.AspNet.Providers

And press enter to install it in Azure web role. This will update the web.config file. Open web.config file and search for DefaultConnection name string. Update it as per below with your respective values –

<add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Server=YourAzureSQLServer.database.windows.net,1433;Database=YourDB;User ID=YourUsername@YourAzureSQLServer;Password=YourPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" />

Please fill yellow marked area in above connection string with your respective values. This can be your on premise SQL server as well. Now next change is important. Search for InProc in your web.config file and replace it with following -
<sessionState mode="Custom" customProvider="DefaultSessionProvider" timeout="5" allowCustomSqlDatabase="true" cookieless="false">
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" />

If you observe we have specified timeout as 5 minutes. You can change as per your requirement. With the above change in session provider in web.config we are telling the application to store the session state values in SQL Azure DB.
Important – Now at this stage w ehave everything setup and application can store session state in SQL Azure however, I could not make the simulate requests from different instances locally and therefore I decided to test directly on SQL Azure. Therefore I published my project to Azure cloud service.

Now I followed below steps –
1.     Run the cloud service URL in browser.

2.     Open another tab in the same browser, copy paste the URL in new tab and keep refreshing till the browser shows same UI with different Instance Id.

3.     Input the value as “kunal” in textbox and Click on Add To Session button and then click on “Read from session”.  The value was retrieved properly as request was being served from INSTACE 0 only.

4.     In another browser instance we opened in step 2, directly click on Read from session button and you should be receiving the same session value as shown below. Note that, both requests are being served from different instance.

Now if you go to your SQL Azure DB using SQL Management studio, you can actually find that Session table got created automatically with few records.

That’s it. You are done with implementation of Session using SQL Azure DB.
Caution – It might be possible that, while testing with SQL express/ SQL Azure you may receive error as –
A connection was successfully established with the server, but then an error occurred during the login process. (Provider: SSL Provider, error: 0 - The target principal name is incorrect.)]
To resolve this error add this line to your connection string in web.config –
This will resolve the error of principal name.
Session Record Clearance
Here you have good news on the part of deleting expired session records from Session table. ASPNET universal providers take care of deleting expired session records from SQL Azure AUTOMATICALLY!!!
You don’t need to run worker role or call any store procedure in the background to remove expired sessions from SQL Azure and that is the beauty of ASPNET universal providers. As soon as the expiry time is elapsed, the records will be get automatically deleted.
To download the source code example refer to the link - http://gallery.technet.microsoft.com/Session-management-using-bbe47fa3
Hope this helps.


  1. Really a great post

  2. Hi there, really great post. I have a question and I hope you knew the answer. I have an ASP.NET Web Forms project and I have deployed it as an Azure Website (no web roles). Will your solution work in case I choose to use SQL Azure session state?



    1. Hi Chris, Thanks for the reply. Yes it should work with Azure Web Sites.

  3. very use full post

  4. This comment has been removed by the author.

  5. C ool... thx for sharing