3 tips for SQL Azure connection perfection

SQL Azure can come up with intermittent connection problems in production. To avoid these, make sure you use the correct connection string and use ReliableSqlConnection rather than SqlConnection

One of my main annoyances when dealing with SQL Azure is of course the occasional connection problems that communicating to a cloud database entails. If you’re used to programming against a locally hosted SQL Server box this can be quite a change and annoying like you wouldn’t believe.

So after hitting the problem again in cloudservices.red-gate.com  I thought I’d write a little post to remind myself how I’ve got it working, I don’t say it’s right but at least “it works on my machine”.

Tip 1: Do Not Use SqlConnection

This may sound a bit bizarre but unless you enjoy re-writing retry loops all over the place a much better place to start is ReliableSqlConnection. This is contained in the “Microsoft Enterprise Library Integration Pack” (it’s MS-PL licenced so you should be safe). The bit you’re after is the transient fault handling application block http://www.microsoft.com/en-us/download/details.aspx?id=28189

What this gives you is a way stop all of those irritating intermittent errors SQL Azure seems to give randomly in production, usually to do with load balancing, limited connection duration, and “naughty” servers.

Tip 2: ReliableSqlConnection API is (slightly) different

The API isn’t a direct replacement for SqlConnection but it’s pretty close. You can use extension methods such as SqlConnection.OpenWithRetry() however, I always use ReliableSqlConnection directly as I prefer breaking compilation to ensure I change all uses properly.

The new way of usage is pretty similar but you execute commands via the connection, not the command.

Also be aware that ExecuteCommand() != (int)ExecuteCommand() one’s a scalar the other’s a rowcount – just another gotcha I had to debug.

Lastly, I found the null behaviour of ReliableSqlConnection wasn’t ideal in the scalar case, so in order to get the following code to work I had to modify ReliableSqlConnection very slightly.

The modificiation was in the ReliableSqlConnection.ExecuteCommand<> code replacing the behaviour in the scalar situation to the following.

Tip 3: Get your connection strings right

Lastly, or rather, firstly you have to make sure connection string is good. Connection strings are normally good or bad –  there is no ‘sometimes it’s ok’, sorry that’s just no longer the case.

SQL Azure only currently supports SQL Server authentication so that’s easy enough. You have to include a username and password and it all works. Well it did, until you start having to retry connections. That’s because for efficiency you don’t want to do a DNS lookup every connection attempt – so you cache the IP. Great. However, because you didn’t have @sqlazureservername after your user the Azure system can’t track it on second attempt. This of course will only happen occasionally, making it a pain to locate. So always ensure you have the @sqlazureservername after your username in the connection string.

Secondly – and this is what wasted a good half a day for me recently – ensure you’ve added TrustServerCertificate=True to your connection string. I’ve talked to Microsoft in the past about this but never really got any further than them admitting other customers have had to do the same. If you want it to work you have to do this (and accept the potential highly difficult man-in-the-middle attack), set it to false at your peril.

Job Done

Interested to hear other opinions and things I’ve probably missed from this post. Also beware of 3rd party libraries as they’re probably using SqlConnection directly so you’ll encounter problems on things you’ve done locally with no problems.

At least next time when I google I’ll find this article, but just to make it more likely – shibboleet.