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 http://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 annoying intermittent errors SQL Azure seems to give randomly in production – 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.
|
1 |
( connection = ()) { connection.Open(); ( command = ()) { ( reader = connection.ExecuteCommand<>(command)) { (reader.Read()) { } } }} |
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.
|
1 |
t = command.ExecuteScalar(); t ? 0 : ()t; |
The modificiation was in the ReliableSqlConnection.ExecuteCommand code replacing the behaviour in the scalar situation to the following.
|
1 |
{ result = command.ExecuteScalar(); (result != && result != .Value) { (T).ChangeType(result, resultType); } {(T); }} |
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 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 started 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 3rd party libraries as they’re probably using SqlConnection directly so you’ll encounter problems on things you’ve locally with no problems.
At least next time when I google I’ll find this article, but just to make it more likely – shibboleet
Load comments