Reasons and considerations to move from on premise SQL server to SQL Azure in the cloud.

A lot of times I got questions from people why to use SQL Azure instead of using the on premise version. I strongly believe that the cloud is the future.  It’s the only way to go. Big question to answer: Is it already good enough to say good bye to the on premise version? In general this question can be answered with all advantages of using the cloud but thers are still some limitations.

Advantages:

• It allows you to scale up and pull back capacity as required, enabling services to be arranged around periods of peak demand
• 24/7 availability (99,9% monthly SLA) 
• Pay for what you uses. Low upfront costs
• Anywhere access
• Always use of the latest software
• Reduced management
• No patching or maintenance on SQL server level. SQL Azure administers the physical hardware such as hard drives, servers, and storage. However, database maintenance is still needed like index fragmentation and update statistics.
• Every database has built-in high-availability, failover, and redundancy. Every SQL database is replicated twice (to make a total of three databases) over different hardware boundaries in the same datacenter. 
• No backups needed for data disaster recovery. However, backups for user failures are still needed.
• Shift in focus on SQL development by Microsoft from on premise to SQL Azure. At this moment SQL Azure is already running on version (11.0.1820) while the latest on premise version is running on version 10.50.2500 (SQL 2008 R2 SP1).

So moving to the cloud with your database will have advantages but you should look to the limitations of the current version.

Limitations:  

• No performance guarantees at this moment.  Part of the reason for this is the multitenant problem: many subscribers with their own SQL Azure databases share the same instance of SQL Server and the same computer, and it is impossible to predict the workload that each subscriber’s connections will be requesting. However, not having guarantees doesn’t mean that performance is not a critical aspect of the design of the SQL Azure infrastructure. SQL Azure provides load balancing services that evaluate the load on each machine in the data center. When a new SQL Azure database is added to the cluster, the Load Balancer determines the locations of the new primary and secondary replicas based on the current load on the machines. If one machine gets loaded too heavily, the Load Balancer can move a primary replica to a machine that is less loaded. The simplest way to do this move is to switch a primary and secondary replica for a SQL Azure database that is performing sluggishly. This switch can have a major and immediate impact on performance, because all reads and writes take place on the primary replica. See:  Inside SQL Azure
• SQL Azure provides a significant subset of the functionality of SQL 2008 R2, it does not currently have complete feature parity. For instance:
• No fill factor support for indexes.
• No Full-Text search support.
• Cross database queries are not possible.
• No support for page level or row level compression like the enterprise edition of SQL Server 2008. Work around is use the .NET CLR 2.0 System.IO.Compression namespace in your data layer
• No Azure version available of SSAS and SSIS. I expect it will come in the future but it is unknown when this will come available.
• No SQL Server Agent to schedule jobs. You need to use a Windows Azure worker role and create your own custom code to schedule jobs.
• Performance analysis tooling is minimal. SQL Profiler is still not supported but here you can vote to get it higher on the priority list. A lot of DMV's are added to get insight in query performance. However you can't get insight in query details for one application of user connection.
• Maximum of 149 user database per SQL Azure Server + one master database = 150 databases.
• Maximum database size 150 Gb.  Size limitations can be mitigated through the implementation of sharding which distributes load across multiple SQL Azure databases.
• Clustered index requirement before inserting data. This will make a clustered index change more complex. Because you can’t drop the clustered index if there is data in the table.
• SQL Azure Reporting still in CTP. No support for custom assemblies. This is needed to support multi languages and localization in reports.
• Performance of SQL Azure Reporting is not consistent. This is because not contain processing cache feature. I hope this will be fixed when general available.
• What to do to improve performance? I can’t add memory or add spindles to my SQL Azure database server.  Is the only solution to split up my database in multiple databases using SQL Azure Elasticity?
• Application can be hosted in your own data center. However to minimize network latency  you should host your application in the Windows Azure platform.
• More limitations can be found here.

Conclusion: One year ago, I already blogged about difference between SQL Azure and SQL 2008. A lot is done in the last year. Development of the (SQL) Azure platform is going very fast, but still a lot need to be done. This is not strange because it takes time to make all on premise functionality available in the cloud. I’m looking forward what is coming in the coming year(s).