Keep Your Eyes on Microsoft's SQL Azure

I think Microsoft's SQL Azure holds astounding potential, both for Microsoft and for SQL Server users. But the current, or first, release of SQL Azure has a number of significant shortcomings that make it non-viable for most business and development needs.

The Promise of SQL Azure

On the surface, the allure of SQL Azure is obvious: Microsoft will host your SQL Server databases and handle all of the headaches associated with the gory, physical, implementation details; and, as a side benefit, your organization trades licensing costs for flat service rates and, eventually, the ability to scale without having to incur expensive hardware costs out of hand. And while there are some current limitations in terms of scalability (the initial release of SQL Azure appears to be capped at 10GB databases), I'm pretty sure that Microsoft will get where it needs to be in order to deliver on these alluring benefits for many types of users.

However, for me, the promise and potential of SQL Azure goes much deeper than the obvious, surface, benefits. I'm convinced that SQL Server will continue to more fully embrace virtualization, and that SQL Azure will play an important role in helping Microsoft iron out many of the kinks involved in achieving that reality.

For an indicator of how SQL Server is embracing virtualization, take a look at the approach proffered by SQL Server 2008 R2's Parallel Data Warehouse solutions - where appliances are tasked with handling workloads in much the same way that virtualization hosts today handle various business workloads. In my estimation, SQL Server will head further down that path as way to continue to treat databases more and more like workloads that can be shunted from physical host to host as needed as means of better facilitating management, scalability, and availability.

A key part of adapting SQL Server to become more of a workload-hosting platform though will be to further decouple physical details from the workload itself, or to make the overall process of hosting SQL Server databases more virtualized. Interestingly enough, that's exactly what SQL Azure is working on right now. And as Microsoft continues to make progress on that front in order to better facilitate the goals of SQL Azure, I'm hoping that a non-trivial solution will emerge - which helps shape some of the competitive direction and focus that SQL Server will need to compete over the next decade.

Production Show Stopper: Lack of Backup/Restore

Despite how excited I am about the potential benefits that SQL Azure will bring (long term) to SQL Server as a platform, and despite how sold even I am on cloud services (I still have some reservations in some cases, but my experiences with cloud-based services in the past have worked out better than I would have anticipated), I still see some big show stoppers with SQL Azure in its current, or first, iteration. For businesses or organizations contemplating the use of SQL Azure for production solutions, the biggest of those concerns centers around the lack of backup and restore capabilities. And, ironically enough, those considerations are due to limitations that the Azure team has encountered while abstracting the logical representation of SQL Azure services from the underlying, physical, implementation details.

I'm hopeful that Microsoft will figure these issues out shortly, but until they do, I would strongly recommend against putting all but a handful of specialized applications up into SQL Azure because of the potential for data loss. Of course, Microsoft has tried their best to try and diffuse this article and they are keen to point out (in forums, blog posts, and elsewhere) that SQL Azure data is protected from hardware or Azure services failure - which is only to be expected.

But if it comes to a stupid user error (like an UPDATE without a WHERE clause against your Inventory table), or some kind of goofy application glitch or snafu in YOUR software, there's no way to recover your data to the point it was before your disaster occurred. And that's because SQL Azure currently has no support for BACKUP or RESTORE commands. Several work-arounds exist - in the form of using SSIS or the SQL Azure Migration Wizard as a way to regularly 'pump' data out of your system as a sort of poor-man's backup. However, if you've ever had to recover from a stupid-user-error on a production system, you know that restoring data to the way it was before the disaster is only half of the problem - because you have to push back any changes made AFTER the disaster as well. And that's hard enough to do under the best of circumstances, which means it's going to be nearly impossible when working from one of the currently suggested work-arounds.

Consequently, as cool as SQL Azure is today, it's not ready for prime time - except for in a handful of cases where data is only ever going to be a copy of data from another system, or in cases where the possibility of losing data to a stupid-user-error or some other issue is either not possible or not a serious threat.

Development Show Stopper: Missing Features

A common question I get when asked about SQL Azure is whether I think it would be viable as a solution for developers. Obviously, managing SQL Server instances for developers is a pain that many organizations have solved by turning to virtualization, where they're able to let developers host their own SQL Server workloads as a way to help cut down on server sprawl and administrative headaches. So, since that approach works out so well for so many organizations, it seems logical that moving those virtualized workloads out into the cloud would make sense—especially for developers.

And while that logic makes perfect business sense, SQL Azure is going to fall short in just enough cases due to current limitations that it's probably going to make using it as a development platform for EXISTING applications more difficult to use than just incurring the headache and difficulty of hosting developer needs on something hideous like SQL Server Express.

To get a feel for why I think using SQL Azure (in its current form) could be painful for developers working with existing applications, take a look at one of three pages Microsoft has provided that outline the limitations of SQL Azure in its current form. In looking at that list of functional limitations, you'll see that SQL Azure actually does a good job of providing parity with 'real' versions of SQL Server. But if you're using any one of the following features (or one of the others that I don't list) such as Full Text search, Geography or Geometry data types, Typed XML, or CLR functionality then you'll find that your developers will need to figure out a way to 'dumb down' their Azure database, which is hardly a viable way to proceed with development. Granted, plenty of applications aren't using any of these features but if you use SQL Azure today, you won't be able to add these features if you find that you need them.

SQL Azure and DBAs

I haven't touched on some of the more common complaints or considerations that DBAs tend to have when it comes to SQL Azure databases - and that's primarily because (while I'm in that camp) one of the key benefits of SQL Azure is that it shouldn't require a DBA. And, as long as databases stay under 10GB in size, they should perform just fine. But if they start moving in to the 20 or 40GB range, poorly configured indexes will begin to manifest themselves accordingly and Microsoft will need to allow additional instrumentation and the use of Profiler (or something similar).

Otherwise, as cool as I think Azure is now and as cool as I think it will become in the future, I think that, in its current form, there are enough limitations to preclude its use by all but a handful of specialized applications or services today—unless you're willing to work really hard to get around those limitations. Hopefully, though, the v2 release of SQL Azure will address most of the issues I've outlined here (along with others) and we'll start to see more and more folks adopt it for more 'day to day' use because I think it holds some very interesting potential.