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

Comments

  • Anonymous
    May 09, 2013
    hi, thanks for interesting post There is a blocking issue for some devs who cannot move to Azure because it does not support FullText It means for big table LIKE operation will be very slow and expensive. Any plans to add FullText ?

  • Anonymous
    May 09, 2013
    Yes, Full text index is not supported yet on Azure. It should  be in the plan. We will need to wait and watch when it will be available. If I have updates, will surely post back

  • Anonymous
    May 09, 2013
    Hey Deepthi, Thanks for providing information about SQL Azure and the differences with SQL Server Database. Good article. Sai

  • Anonymous
    May 11, 2013
    Good article..

  • Anonymous
    May 12, 2013
    Azzzzzaming!! Thank you Deepthi... One question here... Isn't sql azure to sql server like... on the sales part... kind of honda amaze eating out of honda city sales?

  • Anonymous
    May 12, 2013
    There are some applications which can readily be moved to cloud and new ones we would definitely encourage to be developed on the cloud. for existing apps, a slow and steady migration will happen. My making cloud facilities available, we are making it easier and cost effective for end users and business alike. If you mean that if SQL Server Users on premise, move to cloud, the % of users of "SQL" (on premise and cloud ) would remain the same ?  The total share would still be more than the existing base now. There are some solutions for which cloud is the OPTIMAL and probably the only solution ? Please do try out SQL Azure offering and let us know how you feel about it

  • Anonymous
    May 12, 2013
    Its really Informative... Thank u for the post.

  • Anonymous
    May 12, 2013
    Nicely put .. From why to use it , and the differences.. loved the way you have presented it Deepthi...

  • Anonymous
    May 12, 2013
    Hey, Thanks for the interesting post. Regards, Purni

  • Anonymous
    October 20, 2013
    Good "clear" article for starter like me and helps to understand the "differences" of SQL Azure and inhouse SQL Server..