Notes from Pluralsight SQL Azure course

Here is a summary of the 2h 38min duration Pluralsight SQL Azure course  -

* Advantages of SQL Azure -

• Highly available
• Manageable
• Scalable
• Familiar – similar to SQL Server 2005 & 2008, but differences/limitations exist
• Maintained by MS IT

* Some differences from standard SQL Server  -

• Data migration – SSIS, BCP, Scripts, SQL Azure Migration Wizard, System.Data.SqlClient.SqlBulkCopy; Restore not supported
• Transactions – possible in same DB instance only
• Collation applies only to Column & Expression level; Server, Database excluded
• Tables must have clustered index
• Supports most but not all of TSQL 

* Candidate apps that can leverage SQL Azure -

• Require relational data model. Consider Windows Azure Table for other needs
• Easily partitioned data sets, 5 – 50 GB database
• All queries use one database. (Most Enterprise apps may need to access multiple DBs)
• Require high availability

* Besides Memory, Time, Disk costs, Devs must also understand SQL Azure pricing
* SQL Azure pricing based on usage units: Web (upto 149 databases of 1—5GB each possible) or Business (upto 15 databases of 10—50GB each possible)
* No cost for data transfer if SQL & Azure are on the same availability zone
* Bill accessible through Windows Azure Portal
* SQL Azure usage scenarios -

• Cloud Hub Data Collaboration
• Scale-out Enterprise Applications
• Departmental Applications
• SaaS
• BI

* MS Tools to access SQL Azure – SSMS, Visual Studio, SQL Azure Portal
* Understand Connection termination failure codes to manage recovery -

• System is busy, reconnect after 10s: 40501
• Database hit maximum size: 40544
• Transaction Termination: 40549
• Lock Consumption: 40550
• TempDB uses 5GB: 40551
• Log File issues:  40552
• Excessive Memory Usage: 40553

* Ideal candidates for migration to SQL Azure –

• Tables with indices
• Transations stay in a single db
• DB under 50GB

* Tasks in migration -

• Migrate users & login IDs
• Map data types
• Map db objects: tables, triggers, views, SPs
• Update incompatible objects to work with SQL Azure
• Update apps to work with SQL Azure
• Migrate data to SQL Azure

* It is possible to expose SQL Azure database over OData especially to non-SQL consumers
* OData makes your data consumable everywhere
* OData is a web protocol for querying & updating data
* OData builds on HTTP, Atom Publishing Protocol, JSON
* Feeds may supply more than one collection
* SQL Azure can feed needs of data analysis (through Excel, SSRS etc)

Also see:

Things I learnt from the Pluralsight course "Introduction to Visual Studio 2010"