SQL Azure - An introduction

  • Article

SQL Azure – why should you use it?

Hardware sizing is always a chore that is more based on “guesses”. It is a difficult science to predict and forecast usage of database and its growth.  Having something elastic like “SQL Azure” makes it best for costs and maintenance.  SQL Azure gives a lot of flexibility to increase / decrease usage of resources almost instantaneously. This means that valuable dollars are not locked up and increases efficiency in terms of usage of resources. There are no hazards of procuring, managing, patching, licensing, hardware etc.

Benefits of moving to SQL Azure

Apart from “elasticity”, the other features are listed below

  • High Availability with Zero Configuration: There are many ways to support high availability and disaster recovery for business critical applications. Some of the means used are clustering, log shipping, mirroring etc. SQL Server 2012 provides “ALWAYS ON” which is a new High Availability disaster recovery option. It allows up to 3 active secondary servers. All these are configured by a DBA. But on SQL Azure, high availability is readily available. For every single database there are at least 3 physical databases. Thus the SLA of 99.9999 % uptime. This takes away the additional work of configuring, maintaining and test availability scenarios. All reads/ write go through primary database on SQL Azure.

 

  • Zero Maintenance: Security patches, disk space, moving databases between servers to handle workloads are provided by the service provider. Hence your database always has the latest patches. The patches are also tested before being applied on your data.
  1.  

Differences between SQL Azure and SQL Server Database

 

  • With SQL Azure, users are forced to use SQL Server Authentication which is a recommended best practice to enhance security. 
  • In SQL Server, an idle connection never goes down. In SQL Azure it goes down in 5 minutes. There will have to be a bit of rethinking of strategy when connection pools are being used 
  • There is no SQL Server Agent on SQL Azure. Thus no jobs can be scheduled. However BCP and SSIS can move data to cloud. 
  • Currently databases cannot be backed up. But they can be copied 
  • All tables should have a clustered index. The exception to this rule is only for tables in tempdb. 
  • No cross database referencing in SQL Azure. 
  • Linked Servers cant be accessed in SQL Azure 
  • Default collation is SQL_LATIN_1 and currently has a limitation of being the only collation available on SQL Azure. However other collations will be supported soon. 
  • Regardless of the data center chosen, time zone is set to UTC and currently cant be changed. 

 

No limitation on Scale:

An individual SQL Server database can support up to 150 GB of data. Databases larger than that can be partitioned across multiple Azure databases using SQL Database federations.  150GB does not include logs or multiple copies of databases automatically maintained by SQL Azure.

Web editions can support up to 5 GB and Business Editions up to 150 GB. Billing is based on peak db size in a day.

Price Comparison:

Comparing the price of SQL Server and SQL Azure is like comparing oranges and apples. SQL Server licenses + hard ware costs + OS licensing cost + maintenance cost. SQL Azure provides all of this with networking bandwidth, storage and management of data. In my opinion for managing the size of database, over long durations Azure should work out much cheaper.

Basket of important things

SQL Data Sync is an option by which data can be synced between SQL Server and SQL Azure. Currently there are no charges for SQL Data Sync.

SSIS, SSAS and SSRS are also supported on the cloud.

Scripts which are compatible with SQL Azure can be generated from an on-premise installation