This content is part of the Essential Guide: Evolution of Windows Azure SQL Database
Manage Learn to apply best practices and optimize your operations.

Why you should think twice about Windows Azure SQL Database

Expert Serdar Yegulalp explains why Windows Azure SQL Database may not be such a great fit for your IT shop after all. Learn what to watch out for.

In the first part of this piece, I took a look at the reasons why Windows Azure SQL Database -- or SQL Azure, for...

short -- might be a good fit for your database needs. In this second article, I turn the coin over and look at reasons why it might not suit you after all.

SQL Azure only implements a subset of T-SQL. For many people, especially those with large and involved database applications, this is a deal-killer. Azure does implement the most core and crucial of T-SQL features, including cursors, transactions and triggers, but there are a slew of T-SQL features that aren't implemented. The Common Language Runtime, trace flags, system tables, distributed transactions and queries, and database mirroring are some of the omissions; you can see a full list on Microsoft's website.

If your database depends on any of those features, you'll either have to rewrite the database, wait until SQL Azure supports the features you need, do without those features or stick with an implementation of SQL Server that supports everything; for example, if you run a virtual machine (VM) in Azure and equip it with a full installation of SQL Server, you won't have those limitations.

More on SQL Azure

SQL Azure recommendations and best practices

The ins and outs of SQL Azure development

Secrets to unlocking BI with Microsoft SQL Azure Database

You can't run agented jobs. This is one of the missing T-SQL features that is worth specifically calling out. SQL Agent itself is not supported on SQL Azure, so you cannot run jobs via the agent in the first place. Again, the workaround is to run a full instance of SQL Server in a VM, but that means not having SQL Azure's inherent fault tolerance and elasticity.

Not having your data hosted locally can be its own bottleneck. The only way to get data to an Azure instance is to upload it or sync it across the network. Make sure your bandwidth can handle it. Most of us are used to this by now, in the age of the cloud, but if you're used to simply plugging a disk into the server in question and copying files over that way, you'll want to look into the remote-sync mechanisms available to save yourself some trouble.

Be mindful of SQL Azure's connection governor. SQL Azure has hard-wired limitations on usage and will throttle or disconnect database connections under heavy loads. Some common scenarios for connections being killed include transactions that use more than a gigabyte of logging, consume more than one million locks or which run for more than 24 hours; or sessions that use more than 16 MB for more than 20 seconds or which idle for more than 30 minutes. SQL Azure's throttling and limiting behaviors have been discussed in other venues as well. However, the better-tuned your application, the less likely it is to be throttled.

Reporting also costs extra. SQL Reporting is another feature that costs extra in SQL Azure and is charged at a rate of 88 cents per hour per reporting instance. Up to 200 reports per hour can be generated before you run into overage charges.

There may be data charges. There are no charges for moving data into and out of SQL Azure per se, and any inbound data transfers to Azure generally are free. You are, however, charged for outbound data, starting at 12 cents per gigabyte for the first 10 terabytes each month. You're also charged per hour for any Windows Azure VPN connections to Azure, in either direction (at 5 cents per connection-hour), so bear that in mind if you decide to use Azure's VPN to securely transfer data to SQL Azure.

There might be other charges later. One of the disadvantages of using any service is changes to the service and service level agreement over time that will likely favor the company and not you. Reporting, for instance, was free until the end of August 2012. It seems likely that any features available in a preview mode now and are currently free are likely to have a fee assessed for their production use later on.

There's more. Other issues to keep in mind include lack of support for transparent data encryption, change data capture, FILESTREAM data, integrated full-text search or SQL Server replication, among other things -- at least, they're not available yet.

SQL Azure, like the rest of Azure around it, isn't a static entity. It's evolving and growing, and with each new iteration of the service, you can expect not only more features you want, but also less of the limitations you don't. That said, it helps to know if your current SQL Server applications will be a good fit for SQL Azure or if you should wait until Azure catches up.

Next Steps

How you should run SQL Server on an Azure cloud 

Dig Deeper on Microsoft SQL Server Consolidation and Virtualization