Manage Learn to apply best practices and optimize your operations.

The pros of Windows Azure SQL Database

What are the pros of choosing Windows Azure SQL Database over more traditional infrastructure? Serdar Yegulalp shares his knowledge on the subject.

Windows Azure -- Microsoft's cloud infrastructure platform -- has become a major part of Microsoft's overall strategy, and the Windows Azure SQL Database, formerly called SQL Azure, has become a major component of Windows Azure itself.

Microsoft's ambitions for Azure in general, and for Windows Azure SQL Database specifically (which we'll call SQL Azure in this article for the sake of simplicity), are much greater than just providing a database-backed service in the cloud. Their intention is to create an infrastructure for petabyte-scale "big data" and everything on down.

What are the advantages to using SQL Azure, as opposed to setting up your own SQL Server instance? Here are some of the most crucial, especially for those who are already running SQL Server on their own premises.

Use the tools you already have. Working with SQL Azure can be done via SQLCMD or the SQL Server Management Studio, both of which ought to be intimately familiar to SQL developers.

No physical administration. For people who are sick and tired of dealing with the hassle of setting up SQL Server, apportioning databases and mucking about with physical machines in general, having your database hosted in such a way where all of that becomes someone else's job is a big plus. This is doubly so if you're starting fresh and don't have an existing store of data to pipe up into the cloud somewhere.

High availability out of the box. Azure generally obviates the need for building high availability architectures. This applies to SQL Azure as well; any databases built within it are automatically highly available.

Scalable service plans for multiple needs and budgets. The industrial-strength features of SQL Azure may seem like overkill to folks with relatively modest needs, but Microsoft has taken steps to make SQL Azure appealing to both big and small business users. For one, they created a pricing structure that allows users with smaller needs to be accommodated; you can pay as little as $4.99 per database per month, with the maximum storage currently set at 150 GB per database.

Elasticity. A much-abused buzzword, but in SQL Azure's case, it applies. Applications written for multiple databases (via sharding, for example) can be scaled out with Azure by adding instances as needed. That said, the best benefits of SQL Azure don't just consist of being able to spin nodes up or down on demand. Microsoft also provides an infrastructure of federations to make scaling out easier and less impactful, as well as tools like the SQL Azure Federation Data Migration Wizard to further automate this process.

Growing support for non-Microsoft technologies. What? Yes. No paradox there: The more third-party software technologies Microsoft is able to support across all the different layers of Azure, the more likely people will use it to build applications with other software they're already comfortable with. Aside from Python, NoSQL, Java, node.js, and PHP being supported throughout the Azure stack, one other major and pivotal tech that specifically applies to SQL Azure is Hadoop. The Hadoop on Azure system (Windows Azure HDInsight) is only available by invitation right now, but its mere presence is significant.

For more on the Windows SQL Azure database

How to scale out using Windows SQL Azure database

Use SQL Azure to beef up BI

Use it via a VM if you need it. One of the ways SQL Azure manifests being part of the whole stack is by offering multiple levels of implementation. If you simply need a website and a database, you can hitch a SQL Azure instance to an Azure website and be done with it. If you need a full-blown virtual machine (VM), you can get that as well -- and even use a locally deployed instance of SQL Server in the VM instead of SQL Azure.

Sync and migration tools. If you already have a clutch of SQL Server-stored data somewhere and want to either sync it with SQL Azure or passively migrate it via sync, Microsoft has published a service -- the appropriately named SQL Data Sync -- to accomplish this. Use of the service itself is free, and all inbound data to Azure costs nothing, so you won't have to worry about racking up costs by syncing gigabyte-sized tables.

All of the existing tools used for migrating data between SQL Server instances also work. You can use the Microsoft Sync Framework to do that job, use T-SQL scripting, the bcp utility or the SQL Database Migration Wizard (although Microsoft does not support the last).

In the next article we'll look at the flipside: Why SQL Azure might not be the right choice for your database hosting.

Dig Deeper on Microsoft SQL Server Tools and Utilities