SQL Server vs. SQL Azure: Where SQL Azure is Limited

Cloud computing is one of Microsoft’s big pushes in 2010, and SQL Azure is its cloud-based database service. Built on top of SQL Server, it shares many features with on-premises SQL Server.

For example, applications connect to SQL Azure using the standard Tabular Data Stream (TDS) protocol. SQL Azure supports multiple databases, as well as almost all of the SQL Server database objects, including tables, views, stored procedures, functions, constraints, and triggers.

However, SQL Azure is definitely not the same as an on-premises SQL Server system. Here's how it differs:

7. SQL Azure Requires Clustered Indexes

When you first attempt to migrate your applications to SQL Azure, the first thing you’re likely to notice is that SQL Azure requires all tables to have clustered indexes. You can accommodate this by building clustered indexes for tables that don’t have them. However, this usually means that most databases that are migrated to SQL Azure will usually require some changes before they can be ported to SQL Azure.

6. SQL Azure Lacks Access to System Tables

Because you don’t have access to the underlying hardware platform, there’s no access to system tables in SQL Azure. System tables are typically used to help manage the underlying server and SQL Azure does not require or allow this level of management. There's also no access to system views and stored procedures.

5. SQL Azure Requires SQL Server Management Studio 2008 R2

To manage SQL Azure databases, you must use the new SQL Server Management Studio (SSMS) 2008 R2. Older versions of SSMS can connect to SQL Azure, but the Object Browser won’t work. Fortunately, you don’t need to buy SQL Server 2008 R2. You can use the free version of SSMS Express 2008 R2, downloadable from Microsoft's website.

4. SQL Azure Doesn't Support Database Mirroring or Failover Clustering

SQL Azure is built on the Windows Azure platform which provides built-in high availability. SQL Azure data is automatically replicated and the SQL Azure platform provides redundant copies of the data. Therefore SQL Server high availability features such as database mirroring and failover cluster aren't needed and aren't supported.

3. No SQL Azure Support for Analysis Services, Replication, Reporting Services, or SQL Server Service Broker

The current release of SQL Azure provides support for the SQL Server relational database engine. This allows SQL Azure to be used as a backend database for your applications. However, the other subsystems found in the on-premises version of SQL Server, such as Analysis Services, Integration Services, Reporting Services, and replication, aren't included in SQL Azure. But you can use SQL Azure as a data source for the on-premises version of Analysis Services, Integration Services, and Reporting Services.

2. SQL Azure Offers No SQL CLR Support

Another limitation in SQL Azure is in the area of programmability: It doesn't provide support for the CLR. Any databases that are built using CLR objects will not be able to be moved to SQL Azure without modification.

1. SQL Azure Doesn't Support Backup and Restore

To me, one of the biggest issues with SQL Azure is the fact that there no support for performing backup and restore operations. Although SQL Azure itself is built on a highly available platform so you don’t have to worry about data loss, user error and database corruption caused by application errors are still a concern. To address this limitation, you could use bcp, Integration Services, or the SQL Azure Migration Wizard to copy critical database tables.

Learn more from my article "SQL Azure Enhancements."