Deploying Umbraco to SQL Azure

Question

I've successfully implemented Umbraco 4.7 to a Windows Azure Website and SQL Azure, but sometimes I get errors similar to this one:

SQL helper exception in ExecuteScalar ---> System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) --->

It seems that Umbraco does not manage retry logic (sql azure transient fault handling). Does anybody know of any non-traumatic way to implement this on the umbraco side?

Please use SQLAzure Migrating wizard tool to migrate the database from your server to azure server and then change the connection string and appsettings(key=UmbracoDSN) in webconfig of your umbraco — Dennis
I already did, my site works fine. Only about 1% of the time I get an error like this. This is because of SQL Connection Throttling, the service is designed to work this way and a retry logic must be implemented on the data access layer to retry if the first one fails. — amhed
Hello. I get the same kind of error in the past. I enabled caching, which reduced the load on the database, which fixed it. Have you tried enabling the caching to see if that resolves it? — csharpforevermore
Are you using SQL sessionstate management? Or are you using AppFabric cache? — Digbyswift
It's actually using InProc, haven't changed it since I only have one server instance running — amhed

JamesKn JamesKn · Accepted Answer · 2013-05-10T23:40:03

Amhed, There is no easy way todo this, people using Umbraco in Azure have generally come up with session state problems that are causing connection issues, but as you stated you are only catching a 1% which means you are picking up transient errors. You will have probably seen the discussion here

http://our.umbraco.org/forum/core/general/27179-SQL-Azure-connectivity-issues?p=1

The net effect is that you have to take the umbraco code base and rebuild it with a retry framework in it. That comes with a serious overhead to using Umbraco and taking future releases. You would best lobbying Umbraco core team to put a full retry framework in place and supporting it. (Lets not even talk about security issues ;)

This probably not what you want to hear but effectively it is roll your own datalayer time.

Having said all that I went and had a look: ;) (Because this does interest me for something else)

As starting point though from looking at the source in Umbraco they are using

Microsoft.ApplicationBlocks.Data

As the base for making connections and executing SQL commands

from Looking at the umbraco.DataLayer.SqlHelpers.SqlServer.SqlServerHelper

using SH = Microsoft.ApplicationBlocks.Data.SqlHelper;

So my guess is that you would need to replace this block. A quick (dirty) search on the internet you get

http://www.sharpdeveloper.net/source/SqlHelper-Source-Code-cs.html

You could reflect out the class though to make sure.

This then you could rebuild out with a transient fault handling framework and then you could effectively drop in potential as a change of dll (famous last words).

But you could at least get

using (ReliableSqlConnection conn = new ReliableSqlConnection(connString, retryPolicy))

easily going in that type of class and more lovely stuff.

http://msdn.microsoft.com/en-us/library/hh680899(v=pandp.50).aspx

If I was going to do this, this is where i would start from and I would go in at that level.

I am not sure if that would cover 100% the connection set as I don't work actively in umbraco code base so this is best guess but looking at source this were I would start from and change out and looks to be your best starting point.

hths,

James

Deploying Umbraco to SQL Azure

1 Answers