Unobtrusive transient fault handling in Azure SQL Database with Entity Framework, Linq To Sql, NHibernate and ADO.NET

It’s fairly well known know that when connecting to an Azure SQL Database you need to protect your application from transient errors that occur when using it.

Luckily, Microsoft provide a library that takes care of most of the effort of detecting these errors and retrying your operations according to a retry policy.

The problem with this library is that most of the documentation out there tells you to essentially wrap every database call in your system in a retry call using the library. Let’s be honest – this is a crappy solution to the problem, particularly for legacy code bases where this might result in a major set of changes to the codebase.

Luckily, there is a way around it:

  • If you are using Entity Framework 6 then check out the new Connection Resiliency functionality, although note that the default implementation doesn’t retry for timeout errors (see the documentation of the below mentioned libraries to see why you might care about that)
  • If you are using NHibernate then you can use the library I’ve previously announced/released; NHibernate.SqlAzure
  • If you are using EntityFramework then the best unobtrusive solutions on the net previously have retries for connection establishment (that have other potential connection management side-effects) and save operations, but not command retries – I’d like to announce the release of a library I have created called ReliableDbProvider that solves these problems in an unobtrusive way (generally you will only need to change your config and possibly one or two lines of code)
  • If you are using ADO.NET (provided you use the Db Provider functionality for managing connections and commands) or Linq To Sql then the ReliableDbProvider library can be used as well

Simulating transient errors when using Azure SQL Database

I recently gave a presentation on the ins and outs of using Azure SQL for the Perth Ms Cloud User Group on Azure SQL Database. If you are curious feel free to check out the presentation file at: https://github.com/robdmoore/SQLAzurePresentation

One of the things I did for the presentation was come up with a demo that illustrated the need for, and impact of, having transient error protection when talking to an Azure SQL Database. I have taken this code and cleaned it up a bit and released it on Github at: https://github.com/robdmoore/SQLAzureTransientDemo

This code is quite unique since I have managed to find a way to reliably elicit the transient exceptions (something I haven’t seen anywhere else on the net). If you need to test some code that needs these exceptions to happen to run and you want to be really sure the code works then feel free to check out the code.

Windows Azure Web Sites Programmatic Web Deploy

I was setting up a continuous integration / deployment pipeline last night for my team at GovHack Perth and I had a lot of trouble getting the web deploy deployment working against an Azure Web Sites site.

I had downloaded the .publishsettings file and copied out the username, password and server to pass through to the .cmd file that is generated by MSBuild when creating a web deploy package. I do this because I create the package separately as part of the CI build and attach it as a build artifact and then separately do the deployment with msdeploy as another CI build step.

The problem I came across was that I kept getting the following error when running the deployment:

C:\Path\To\Project\obj\Release\Package>projectname.deploy.cmd /T /M:https://waws-prod-hk1-001.pu
blish.azurewebsites.windows.net:443/msdeploy.axd /U:$sitename /P:password /A:Basic
SetParameters from:
"C:\Path\To\Project\obj\Release\Package\projectname.SetParameters.xml"
You can change IIS Application Name, Physical path, connectionString
or other deploy parameters in the above file.
-------------------------------------------------------
Start executing msdeploy.exe
-------------------------------------------------------
"C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -source:package='C:
\Path\To\Project\obj\Release\Package\projectname.zip' -dest:auto,computerName="https://waws-prod
-hk1-001.publish.azurewebsites.windows.net:443/msdeploy.axd",userName="$sitename"
,password="password",authtyp
e="Basic",includeAcls="False" -verb:sync -disableLink:AppPoolExtension -disableL
ink:ContentExtension -disableLink:CertificateExtension -setParamFile:"C:\Path\To\Project\obj
\Release\Package\projectname.SetParameters.xml" -whatif
Info: Using ID '744a18b1-51c2-4d05-a674-6c407a7f80c5' for connections to the rem
ote server.
Error Code: ERROR_USER_UNAUTHORIZED
More Information: Connected to the remote computer ("waws-prod-hk1-001.publish.a
zurewebsites.windows.net") using the Web Management Service, but could not autho
rize. Make sure that you are using the correct user name and password, that the
site you are connecting to exists, and that the credentials represent a user who
has permissions to access the site. Learn more at: http://go.microsoft.com/fwl
ink/?LinkId=221672#ERROR_USER_UNAUTHORIZED.
Error: The remote server returned an error: (401) Unauthorized.
Error count: 1.

It turns out the problem was I needed to include the ?site=sitename parameter in the msdeploy destination otherwise (I assume) the load balancer on the publish server can’t authenticate your username and password properly. This meant I needed to switch to using msdeploy.exe directly like so:

"C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -source:package='projectname.zip' -dest:auto,computerName="https://waws-prod-hk1-001.publish.azurewebsites.windows.net:443/msdeploy.axd?site=sitename",userName="$sitename",password="password",authtype="Basic",includeAcls="False" -verb:sync -disableLink:AppPoolExtension -disableLink:ContentExtension -disableLink:CertificateExtension -setParamFile:"projectname.SetParameters.xml"

Hopefully this post helps other people stuck in the same situation (I couldn’t find anything on Google to help with this)!