Considering SQL Azure

If you’re a DBA who’s feeling ambivalent or even dismissive of SQL Azure, Microsoft’s relational database in the cloud, you’re not alone: Even the industry analysts are at odds about cloud computing in general. Gartner says, “Cloud computing is at the Peak of Inflated Expectations on the Gartner ‘Hype Cycle for Cloud Computing, 2009.’ The idea … is appealing, extremely confusing, and very misleading.”

To add to the overall fog, Microsoft seems to be aiming SQL Azure at developers, yet continues to promote it as useful to DBAs, touting its manageability, high availability, and scalability. More importantly, Microsoft is promoting SQL Azure as a possible alternative or supplement to on-premise SQL Server—and whether you’re in the DBA world or the developer world, you need to keep your eye on this technology.

Built on SQL Server technologies, SQL Azure (formerly SQL Data Services) is part of the Windows Azure platform and extends SQL Server capabilities as web-based services. It also turns Microsoft into a host of databases in the cloud. For better or for worse, you don’t control the physical resources of SQL Azure—it runs in Microsoft data centers on hardware that’s owned, hosted, and maintained by Microsoft.

“If indeed that is \[SQL\] Azure's major selling point—let's call it database as a service, or DBaaS, to differentiate it from PaaS (Platform as a Service) which has been available for some time now—then it's going to be a godsend for developers who don't have a database administrator or database-savvy system administrator to help them out,” concludes Michelle A. Poolet, SQL Server Magazine contributing editor and cofounder of Mount Vernon Data Systems and Six Sigma Uptime.

“It's one thing to install SQL Express or SQL Developer on your own laptop and use it as a personal development platform; it's quite another to try to replicate a true web experience on a laptop. Too often, development that happens on a laptop fails when it's rolled into production, because the environments are so different,” Poolet says. “Then along comes \[SQL\] Azure, or DBaaS. Now, for a price that's commensurate with the length of time you, Mr. or Ms. Developer, need to create this new web-based, database-driven application, you can click the mouse a few times and Voila!—here's your SQL Server/web environment, ready to go. That's not only cool, that's huge.”

Michael K. Campbell, a consultant with SQL Server DBA and development experience agrees: SQL Azure “will hold amazing appeal for trainers, developers, and folks looking for test environments and so on. For trainers, they can basically get rid of the need for setting up labs—and can just create labs in 'the sky' that follow them where ever they go. Likewise, for devs, they can easily provision resources to work against and if they're building new systems you could argue that they'll have some very powerful scalability options.”

SQL Azure and DBAs

That’s all very nice for devs, DBAs might be thinking, but what about us? “Is SQL Azure more for developers? Yes, in that it provides you with a globally accessible database for your applications and you can access it using the same ADO.NET code that you currently use. That said, there most certainly is a DBA aspect to it,” says Michael Otey, technical director for Windows IT Pro and SQL Server Magazine and coauthor of Microsoft SQL Server 2008 New Features. “Someone must design the database, create indexes, constraints, triggers, views and all the things a DBA would do.”

Blogger and Quest Software SQL Server evangelist Brent Ozar further articulates the DBA side: “If DBAs made a list of the SQL Server features they used on a daily basis, they'd come up with things like backups, query execution plans, or maybe running sp_who2 to find out who's currently active on the server. Azure offers none of these things, so DBAs shake their heads and wonder who on earth would implement something like this.

“If developers made their own list, however, they'd find Azure supports a lot - but not all - of their favorite features. When a developer builds a brand-new app from the ground up, they can decide right from the start to only use the subset of features present in Azure. If they do, they can deploy their database in either Azure or SQL Server.

“Problem is, all of our existing database applications were built without Azure's limitations in mind,” Ozar says. “Before we could even consider Azure for an existing application, we have to do a detailed analysis of every SQL Server feature we're using, and if we're using something that's not supported in Azure, we have to figure out how to work around that. Take an application like Microsoft SharePoint, which stores files in the database - something Azure lacks. Reworking around that limitation would take a lot of time—and money.”

Software architect Kevin Hazzard writes in his Brain Spigot blog, “From a system administrator's standpoint, there are some radical differences between SQL Server and SQL Azure. How in the world are we going to live without the BACKUP command or the KILL STATS JOB command, after all? When designing SQL Azure, Microsoft took a long look at the list of features that had grown into SQL Server over time and realized that there were a lot of physicality features that had become baked into the T-SQL language that make no sense whatsoever running in a grid type configuration with thousands of other active databases. In my mind, this is a good thing because it forces Microsoft to think critically about what physical and logical assets really make up the database from an administrator's perspective and from a developer's perspective.”

Having recently tested SQL Azure for SQL Server Magazine (“Getting Started with SQL Azure Database,” InstantDoc ID 103133), Otey echoes Hazzard’s questions: “With no backup how do you recover from user error where you may need to restore the database to an earlier point in time? There must be a way but I didn’t run across this in testing.”

And SQL Server MVP Derek Comingore’s question is voiced by many in the DBA world: “How are you, Microsoft, going to prove to me that my data will not be leaked or lost?” As Ozar says, “When water cooler talk turns to Azure, DBAs need to ask about the security, reliability, backup, and performance needs of the application.”

Getting Serious About SQL Azure

Differences in schema requirements and limited data types could make it difficult for businesses to port their applications to it, says Otey, “And even if they did so they might receive limited benefits as on-premise SQL Server offers very high scalability and has many options for high availability.”

Ozar concurs: “Moving existing SQL Server apps to the cloud just doesn't make sense to DBAs.” However, he adds, “We have to be able to explain the reasons to our managers in a way that makes sense to the business.” Ozar outlines four points DBAs should make clear with their managers before taking one step into the cloud:

·         “‘I can't guarantee our data is secure in the cloud.’ I can't control who has access to the SQL Server where my data lives. I'm not saying it's out of control, but it's out of MY control. DBAs love control, and managers expect DBAs to be in control, but this is where Azure starts to get a little fuzzy.

·         ‘I can't guarantee our service reliability.’ If Azure services go bump in the night, I can get paged, and I can place a call to Microsoft, but it's no different than calling the electric company when the power goes out. I'm at their mercy to find and fix the problem as quickly as possible. Some folks are quick to tout that Azure has high availability built in, but it still goes down.

·         ‘I can't troubleshoot performance.’ If a query is running slow, I can't find out what other users are hitting the same database or view the query plan to find ways to make it run faster. Again, it's not that no one can tune the server to go faster—but the party responsible is Microsoft, not the DBA. If I was the DBA, I'd hand the developers the phone number for the Azure team, and say, "From here on out, you can call for support yourself whenever you run into a problem."

·         ‘I don't have a simple solution for backups.’ Azure fees don't include the cost—or even the ability—to do backups. If a user deletes a record or a developer drops a table, there's no transaction log we can use to get our data back. It's up to us to figure out how to keep historical archives of our data to prevent those kinds of accidental deletions or malicious hacks.”

Ozar says, “Just by laying out bullet points like that, the DBA can set expectations for management so that everyone understands Azure is a very different solution.”

Will It Fly?

“SQL Azure will probably be a slowly adopted platform,” Comingore says. Campbell adds, “I think that Azure will be an insanely hard sell with DBAs, sys admins, and IT folks in general. And I totally understand why: IT pros are mistrustful of putting their intellectual property, core business processes, and (frankly) entire business up in the cloud…. especially when we're talking about a 1.0 release from Microsoft.

“As a developer I've been looking at \[Azure\] but still see too many limitations, concerns, and quirks to get too excited about Azure,” Campbell says, though he admits to using Amazon’s services for his own projects and finds it “awesome” in spite of weaknesses the company is still working at ironing out. “I just don't have that same trust in Azure because it's late to the game, it's a 'me-too' solution, and it's a first-release version of a new Microsoft offering, making it something I wouldn't feel comfortable recommending to most clients as something they should bet parts of their business on.”

Still, in spite of questions about security, reliability, backup, and performance, SQL Azure will prove useful in certain situations or for certain organizational needs, as Ozar points out. “Not every app needs to store encrypted credit card data, needs 24/7 uptime, or needs to analyze query plans. In edge cases like this, \[SQL\] Azure and other SQL solutions like Amazon EC2 can make sense.”

The issues people raise about SQL Azure “don't mean \[SQL\] Azure is no good—it just means we have to build and deploy our applications differently with some of these limitations in mind,” Ozar says. “For example, I didn't need security, reliability, or backups when I built SPWho2.com, a site to analyze StackOverflow.com user data. The data is open to the public, licensed with Creative Commons, and I just needed to use SQL Server to slice and dice it in new ways. The data comes out once per month, so I only needed SQL Server power in short bursts. A cloud-based solution was perfect for me, and I've been very happy with it.”

And in spite of doubts about adoption in the business world, Otey concurs about the experience of using SQL Azure: “Personally, after using SQL Azure I found I liked it a lot more than I thought I would and found it surprisingly familiar and useable.”

“If Microsoft can convince business managers that it's a safe thing to do, that the development experience is great and that the pricing's right, SQL Azure could be quite popular in the marketplace,” Hazzard writes in his blog. “Only time will tell.”

And with time come improvements, as Ozar points out: “…as cloud-based SQL Server solutions get better, they'll be able to satisfy more and more use cases. More new databases will land in the cloud, although existing apps still won't be likely to get retooled into the cloud due to the development expense.”

Hazzard adds, “I sincerely hope that some of what Microsoft is learning with SQL Azure creeps back into SQL Server 2011 (or whatever it will be called).” Whether or not this occurs, SQL Azure is definitely not a technology to dismiss, in spite of its nebulous aspects.