Head in the Cloud

Like everyone else in this field, I've been following Microsoft's moves into the cloud with interest these past couple of years. I took out a free intro subscription to SQL Azure in 2010 to see what it was and what the possibilities were, created a test database, noted the restrictions and, to be honest, fairly well left it at that. Being up to speed with the newest developments is part of the job description, but SQL Azure didn't feel, at the time, like something my customers were going to be asking for.

Just this week, though, I found myself struggling with the implementation of a fairly simple web app for a customer. The app had been working happily on my test web server and on an old SQL2005-based database server, but we ran into problems moving the database to my customer's database server. This was a fairly typical SQL database setup provided as part of a standard hosting package. The problem was, the provider's security policies restricted the ability of standard tools (like the SQL Management Studio) to connect to their db servers, but the online tools they provided themselves were buggy and inadequate.

I'll spare you all the gory details, but we couldn't restore local backups, couldn't run INSERT scripts, and finally, after dropping the entire database in frustration to start again, couldn't recreate the database. Hair-pulling time.

And then I remembered Azure. I'd already registered, of course, so it was the work of a moment to log in. Within minutes, I had a new dedicated server and database up and running. I tried running the CREATE objects and INSERT data script that I'd already prepared, but I got some (entirely understandable) errors relating to filegroups. I ran a fresh "Script Objects and Data" task from my local Management Studio, and this ran without problems on the SQL Azure database.  Of course, if I'd had the time to look around a little further, I'd have seen that I could simply have exported my local database en masse as a data-tier application, but that was something I discovered a little later once the dust had settled.

Having run some data-integrity checks on the cloud database I moved on to connecting my ASP.NET application to it. Which turned out to be comically easy; it was simply a change to the server name in the connection string. I was able to call my customer within a little more than an hour and say, "look at this". The decision was quickly made; this was the way to go.

Once the dust settled, I had the time to take a good luck around the management interface. It's really impressive these days, unrecognisable from just a year ago. In first instance, and not unimportantly, it integrates seamlessly with your desktop Management Studio. An Azure server is just another server you can connect to. The online tools are impressive in their own right, though, with features that I just hadn't expected at this stage of development, like graphical query execution plans.

I also discovered a missing foreign key relationship (my fault), which I was able to correct using the online GUI; no scripting of painful temporary tables to preserve existing data, no data loss, and done within the blink of an eye.

There's a lot more too, of course, but to me the most important aspect was seeing how SQL Azure behaved in a production situation. When you're dealing with  real data and real data structures, that's when a platform shows what it can and can't do, and Azure passed with flying colours. I'm really looking forward to the next chance I get to use it in a production setting.