» » » Notes from Pluralsight SQL Azure course
,

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: