Tuesday, May 01, 2012

SQL Azure vs Microsoft SQL Server

I've been working on a response to an RFI recently and I needed to list the limitations of SQL Azure over SQL Server.

There are limitations, but there is one major advantage of SQL Azure over SQL Server and that is clustering. SQL Azure out of the box gives me a three node active cluster without doing a single thing. This is because for every write operation on SQL Azure, you get the same data written to two other databases within the same data centre.

As we are on the subject of advantages, there is actually one-other major advantage, and that is it makes it easier to synchronise your SQL Azure database to provide synchronisation with another data centre for resilience using the DataSync component which is based on the SyncFramework which is very easy to use and setup. Synchronising SQL Server is a little more complex as you have more choice albeit, you could use the SyncFramework also, in a clustered environment you'd normally use mirroring or in BizTalk server log shipping. Now you'd use AlwaysOn Availability Groups provided by SQL Server 2012 - not yet released BTW.

I found this really good technet resource that compares SQL Azure features with SQL Server: http://social.technet.microsoft.com/wiki/contents/articles/996.compare-sql-server-with-sql-azure.aspx#Scalability

But for convenience, I provided the table here on my blog (for my benefit in the future too!):

Feature
SQL Server (On-premise)
SQL Azure
Mitigation
Data Storage
No size limits as such
* The Web Edition Database is best suited for small Web applications and workgroup or departmental applications. This edition supports a database with a maximum size of 1 or 5 GB of data.
* The Business Edition Database is best suited for independent software vendors (ISVs), line-of-business (LOB) applications, and enterprise applications. This edition supports a database of up to 150 GB of data, in increments of 10 GB.
Exact size and pricing information can be obtained at Pricing Overview
· An archival process can be created where older data can be migrated to another database in SQL Azure or on premise.
· Because of above size constraints, one of the recommendations is to partition the data across databases. Creating multiple databases will allow you take maximum advantage of the computing power of multiple nodes. The biggest value in the Azure model is the elasticity of being able to create as many databases as you need, when your demand peaks and delete/drop the databases as your demand subsides. The biggest challenge is writing the application to scale across multiple databases. Once this is achieved, the logic can be extended to scale across N number of databases.
Edition
· Express
· Workgroup
· Standard
· Enterprise
* Web Edition
* Business Edition
For more information, see Accounts and Billing in SQL Azure

Connectivity
· SQL Server Management Studio
· SQLCMD
* The SQL Server Management Studio from SQL Server 2008 R2 and SQL Server 2008 R2 Express can be used to access, configure, manage and administer SQL Azure. Previous versions of SQL Server Management Studio are not supported.
* SQLCMD

For more information, seeTools and Utilities Support Description: http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-components-sitefiles/10_5F00_external.png .
Data Migration

For more information, seeMigrating Databases to SQL Azure

Authentication
* SQL Authentication
* Windows Authentication
SQL Server Authentication only
Use SQL Server authentication
Schema
No such limitation
SQL Azure does not support heaps. ALL tables must have a clustered index before data can be inserted.
Check all scripts to make sure all table creation scripts include a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.
TSQL Supportability
Certain Transact-SQL commands are fully supported; some are partially supported while others are unsupported.
* Partially Supported Transact-SQL: http://msdn.microsoft.com/en-us/library/ee336267.aspx
“USE” command
Supported
In Microsoft SQL Azure Database, the USE statement does not switch between databases. To change databases, you must directly connect to the database.
In SQL Azure, each of the databases created by the user may not be on the same physical server. So the application has to retrieve data separately from multiple databases and consolidate at the application level.
Transactional Replication
Supported
Not supported
You can use BCP or SSIS to get the data out on-demand into an on premise SQL Server. When this article is being updated, the Customer Technology Preview of SQL Azure Data Sync is also available. You can use it to keep on-premise SQL Server and SQL Azure in sync, as well as two or more SQL Azure servers.
For more information on available migration options, seeMigrating Databases to SQL Azure Description: http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-components-sitefiles/10_5F00_external.png .
Log Shipping
Supported
Not supported
Database Mirroring
Supported
Not supported
SQL Agent
Supported
Cannot run SQL agent or jobs on SQL Azure
You can run SQL Server Agent on your on-premise SQL Server and connect to SQL Azure Database.
Server options
Supported
Some system views are supported
For more information, see System Views (SQL Azure Database) on MSDN.
The idea is most system level metadata is disabled as it does not make sense in a cloud model to expose server level information
Connection Limitations
N/A
In order to provide a good experience to all SQL Azure customers, your connection to the service may be closed. For more information, see General Guidelines and Limitations on MSDN and SQL Azure Connection Management.
SQL Server Integration Services (SSIS)
Can run SSIS on-premise
SSIS service not available on Azure platform
Run SSIS on site and connect to SQL Azure with ADO.NET provider