Q&A: Moving forward with SQL Server in the cloud

Brent Ozar: It's about a different kind of performance – the job performance of the DBA. How many times has someone come to the DBA and said, "I need a database, but I can't tell you how it's going to perform, because we've never worked with this app or this code before. I can't tell you how many users we're going to have, because I have no idea how popular this application is going to be. Just give me a database and we'll let you know if performance is bad. Oh, and by the way, we don't have much money in the budget."

Cloud computing gives us an answer to questions like that. In theory, we can deploy a database and scale it up based on demand with much less difficulty than scaling traditional database servers. Unfortunately, this really only means scaling up in terms of data size and user quantity – it doesn't mean we can whip out our credit cards and make the application twice as fast. If the queries are bad and the table design is bad, we're still going to be stuck with less-than-desirable performance.

Aside from questions about security, what are some other reasons why folks might be hesitant to deploy cloud databases?

Ozar: First and foremost? Backups. Microsoft's recent problems with its cloud-based service for T-Mobile Sidekick users illustrate how dangerous it can be to put your data in someone else's hands.

The autopsy's still happening as we speak, but in a nutshell, Microsoft temporarily lost all data – contacts, emails, and more – for Sidekick users. That's inconvenient for phone users, but for businesses with mission-critical customer and sales data, that's a business-ending event. Before you put your data exclusively in someone else's data center, you need to have solid backup and disaster recovery plans. Don't assume everything is just magically taken care of in the cloud.

Assumptions about development toolsets can be dangerous, too. Database administrators and developers are used to troubleshooting and performance tuning a certain way, but those techniques may not be available in the cloud. For example, querying SQL Server's [Dynamic Management Views] can yield great information about indexes and query plans, but that information isn't available in the cloud-based version of SQL Server.

Microsoft has done more than just change the name of SQL Data Services in the past year. For example, T-SQL was not originally going to be supported with SQL Azure Database -- but it is now. How important would you say this is to the future prospects of "SQL Server in the cloud"?

Ozar: I applaud how closely Microsoft has worked with the community to adapt their product to a rapidly changing marketplace. We DBAs and developers just don't know what we want to see in the cloud yet, and our opinions seem to be changing all the time.

Knowing that Microsoft has been willing to change Azure has been reassuring for those of us who are developing applications that will count on its future expansion.

What are some of the other important changes that have been made to Azure recently?

Ozar: I think the most important change is something that isn't in Azure directly – the addition of data-tier applications (DACs) in SQL Server 2008 R2. I recently blogged about its implications.

We DBAs and developers just don't know what we want to see in the cloud yet, and our opinions seem to be changing all the time. Brent Ozar SQL Server expertQuest Software

The DAC Pack's new concept of packaged databases means that developers can deploy their application either to conventional SQL Servers or to SQL Azure, whichever way is more convenient for each deployment. Take what we do at Quest Software, for example. We could build database repositories as DAC Packs, then let customers decide whether to deploy the repository in the cloud or in local SQL Servers. For customers without an existing SQL Server infrastructure, an Azure repository might be a great answer.

I'm excited to see how this DAC Pack functionality continues to evolve in coming versions of Azure and Microsoft Visual Studio.

What about other cloud database options? Relational databases in the cloud are rare. From a SQL Server perspective, how does Azure compare to the other options that are available?

Ozar: Azure has a 10 GB database limit. Developers are encouraged to work around this using sharding, a scale-out technique that involves manually partitioning the database using logic in the application server. Unfortunately, this isn't an easy technique, and a lot of sharding lessons get learned the hard way.

Other cloud databases have more of a "build-it-and-forget-it" approach. Developers design their database once, and can evolve it over time, but they never have to worry about how to do load balancing. That difficult job is handled by the cloud vendor, who is probably more suited to doing that kind of work since they'll see it over and over across all of their clients.

Azure has a killer edge, though: T-SQL compatibility. That one feature alone gives developers a big leg up in converting their older applications to work in Azure. Not all – not even most – T-SQL features are supported yet in Azure, but what's there is a lot more than other cloud databases support.

You're presenting on this topic at PASS 2009. While there is definitely interest in cloud computing amongst database folks, it seems like a lack of knowledge at this point is the biggest challenge (or at least the first major hurdle) facing those interested in deploying SQL Server in the cloud. Would you agree?

Ozar: I'll let you in on a dirty little secret – I don't think DBAs are the target market for SQL Server in the cloud. If a company has enough database needs to hire a full-time DBA, then they also probably have enough needs to build their own database server infrastructure internally as well.

I think Microsoft's real focus with Azure is developers. Years ago, Steve Ballmer jumped around chanting about how important developers were to Microsoft, and the entire Azure services portfolio illustrates this belief. Azure is designed to make it easier to design, build, and scale applications. Right now, developers are toying with Azure, figuring out how it works, and building proof-of-concept applications. The killer apps for Azure won't come from DBAs, but from developers who never really wanted to hassle with DBAs in the first place. Azure will help them do an end run around DBAs, venture capitalists, systems administrators and other barriers to entry.

I encourage DBAs to learn about Azure services not because they'll need Azure for their day jobs, but because Azure services enable them to design, build, and scale applications too! The DBA can build the next great killer app -- just as developers can -- and I'll be talking about that during my session at PASS.

Check out more preview coverage of PASS Summit 2009.