Microsoft Azure - An Intro

Microsoft Azure

Advantages:
Manageability, High Availability :
Higher availability with less management required. (less labor required to maintain network security , maintenance of servers etc ).No Physical servers to buy, install, patch, maintain or secure.

Scalability:

1 GB to 50 GB support and scalable with partitioning to larger DBs.

Global Scalability:

If you want to target a specific region, you can deploy your database at the closest data center

Familiar Development Model : Built on T-Sql

Shortcomings:

Doesn’t support XML datatypes and many Sql commands.

No support for backup and restore. Need to use following third party components.

1. For Restore: SQL Azure Migration Wizard (SSMS 2008 R2 should be installed) To Migrate database from Sql Server to Windows Azure. Other way out is to generate Script from SSMS 2008 R2 for windows azure and then deploy the script on Azure server. But not a robust solution.


2. For DB Sync: Microsoft Sync Framework Power Pack for SQL Azure to synchronize data between a data source and a SQL Azure installation. But if the source DB is more than 50 GB, the client needs to manage the partitioning manually.


3. For Partitioning: Enzo SQL Shard Third party component for partitioning.


4. For Backup: Found following third party components\scripts. We need to drill down to these to check the best suitable for us. Used to backup from Sql Azure to Sql Blob Storage. Please note: Microsoft charges ~$0.10 per GB for Blob Storage.


5.  Red Gate Backup tool: Considered as best but presently available as beta version. Probably would be paid software once it comes out of Beta.


6. Script to automate back up to sql blob storage


7. SQL Azure Migration Wizard: The SQL Azure Migration Wizard gives you the options to analyzes, generates scripts, and migrate data (via BCP) from:

1. SQL Server to SQL Azure


2. SQL Azure to SQL Server


3. SQL Azure to SQL Azure

Other options not available when working with a SQL Azure database include: (one in red would affect us)

1. Full-text indexing


2. CLR custom types (however, the built-in Geometry and Geography CLR types are supported)


3. RowGUIDs (use the uniqueidentifier type with the NEWID function instead)


4. XML column indices


5. Filestream datatype


6. Sparse columns


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

Pricing:

As per the example below Pricing is quite simple:

Database + Data transfer based per day charging

Costing Examples: http://www.microsoft.com/en-us/showcase/details.aspx?uuid=09aa4f10-333a-4c98-aed1-4cb300de63ec&WT.mc_id=otc-f-corp-jtc-DPR-MVA_INTROSQLAZURE

Following page makes the pricing a bit confusing as it states that we would be charged on Bandwidth usage, catching etc in addition to the Database and Data transfer.

Costing Calculator: https://www.windowsazure.com/en-us/pricing/calculator/

Pricing can also be based on Computation Power, Db storage, Blob Storage, Bandwidth

1. Compute: For front end application hosting. You can have 0 to 10 Instances of VM. à Required to consider in cast we plan to host our front end also.


2. Database :2 plans.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   Database	Standard pay-as-you-go pricing
   Web Edition (up to 5 GB)	$9.99 per 1 GB of database per month
   Business Edition (up to 150 GB)	$99.99 per 10 GB of database per month (Maximum charge of $499.95 per database)*

   
   


3. Data transfers:  All inbound data transfers, i.e. data going into the Window Azure platform datacenters, are free. Price for outbound data transfers, i.e. data going out of the Windows Azure platform datacenters, is shown below.
   
   
   
   
   
   
   
   
   
   
   
   
   

   Pricing details for outbound data transfers

   North America and Europe regions: $0.12

   Asia Pacific Region: $0.19

   
   


4. Bandwidth: 0 to 2000 Gb. Charged as per bandwidth used.
   http://msdn.microsoft.com/en-us/library/windowsazure/ee730903.aspx
   Bandwidth used between SQL Azure and Windows Azure or Windows Azure AppFabric is free within the same sub-region or data center. When deploying a Windows Azure application, locate the application and the SQL Azure database in the same sub-region to avoid bandwidth costs. For more information, see Accounts and Billing in SQL Azure.


5. Catching services, Storage Services etc are also provided and charged.

Some important tools that you can use with SQL Azure for Tuning and Monitoring include:

1. SSMS Query Optimizer to view estimated or actual query execution plan details and client statistics


2. Select Dynamic Management views to monitor health and status:
   http://msdn.microsoft.com/en-us/library/windowsazure/ff394114.aspx

Some Depreciated features in Sql Azure

1. ‘ANSI_NULLS’ is not a recognized SET option.


2. Deprecated feature ‘SET ANSI_PADDING OFF’ is not supported in this version of SQL Server.


3. Deprecated feature ‘More than two-part column name’ is not supported in this version of SQL Server.  This a significant change if your using Schemas.


4. Deprecated feature ‘Data types: text ntext or image’ is not supported in this version of SQL Server.


5. Deprecated feature ‘Table hint without WITH’ is not supported in this version of SQL Server.


6. Global temp Tables are not supported in this version of SQL Server.


7. A full list id found at Deprecated Database Engine Features in SQL Server 2008 – MSDN.

Important Links:

Video tutorial for scripting out DB Schema for migration to SQL Azure using SSMS R2

https://www.microsoftvirtualacademy.com : Free course to learn basics of SQL Azure by Microsoft.

http://www.geeksco.com/blog/?p=36 : sql azure errors and trouble shooting

http://sqlazuretutorials.com/wordpress/sql-statements-not-supported-for-sql-azure/ : SQL Statements not supported for sql azure