BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News SQL Azure Achitecture - Competitive Differentiation

SQL Azure Achitecture - Competitive Differentiation

This item in japanese

Bookmarks

Two weeks back Kalen Delaney published a whitepaper on SQL Azure internals focused on how SQL Azure achieves scalability and high availability.  Most competing cloud solutions are based on single instances of a database hosted within a virtual machine.  A foundation feature which differentiates SQL Azure from its cloud database competitors is the multi-tenant architecture upon which it has been built.    The whitepaper provides a detailed view of SQL Azure architecture.

Database Creation

To use SQL Azure one needs a Windows Azure platform account and can then create/manage databases with the SQL Azure Portal.  Once the SQL Azure server and the master database instantiated developers may then use Visual Studio 2010 or SQL Server Management Studio during development.  The current size limit on a database is 50GB with size increments of 10GB for their business edition and there is a limit of 149 databases per SQL Azure server.  

Security and the Master Database

Security is primarily administered by Microsoft, however access is still acquired by using userids and passwords.  Guest access to the master database is disabled.  There are two new roles in the master database, dbmanager and loginmanager.  The master database also has other differences in the following views are available: sys.firewall_rules, sys.bandwidth_usage and sys.database_usage.  

All IP traffic between SQL Azure and clients are SSL encrypted and users may create an access control list based on a list of acceptable IP addresses.  The USE command is not supported and all client connections are made directly to the connecting users's database.

Architecture

While a logical database is presented to the developer as a single logical unit, in actuality the developer's data is replicated across three SQL Server databases, one primary and two secondary replicas.  Microsoft abstracts the physical architecture and uses load balancing and connection routing to provide failover and scalability when access the data.

SQL Azure implements the same TDS interface as SQL Server thus providing the same access for client libraries and applications as SQL Server.  In addition, the network topology provides four distinct layers to provide a logical instance of SQL Azure:

  • Client Layer - used by application to communicate with SQL Azure
  • Services Layer - runs the gateway services such as connection routing, provisioning and billing
  • Platform Layer - nodes hosting the actual SQL Server databases
  • Infrastructure Layer - physical hardware, network and operating systems

High Availability

SQL Azure guarantees 99.9% uptime for storage accounts and to process correctly formatted requests they receive to add, update, read and delete data.  Data will not be considered committed until a primary and a secondary both report success and the transaction logs are written to disk.  

All nodes are monitored by six peers that exist on different physical racks and the peers are referred to as neighbors.  When a failure occurs within a given node, a reconfiguration is carried out by the Partition Manager to handle the failure.  The Partition Manager processes failures in random order and does so until there are no more failures to deal with.  For more detailed information refer directly to Kalen's whitepaper.

Scalability

Two features help SQL Azure achieve scalability, primarily engine throttling and the load balancer.  Each node contains an engine throttling component which monitors the node health, log sizes, log write duration, cpu usage, and database size limits.  When limits are exceeded the database will begin rejecting reads and writes and the developer must handle this programmatically through exception handling as suggested by Microsoft.  

The load balancer uses a just in time methodology by selecting the location of the primary and secondary replicas of a new database based on current load on nodes within a cluster.  Likewise, if a node becomes overwhelmed, the load balance will move a primary replica to another node that has less load.  It is important to note that all reads and writes take place on the primary replica so this can dramatically effect performance of a SQL Azure database.

Conclusion

Microsoft is looking to differentiate its data offering from cloud computing competitors while still offering much of the same functionality it provides in SQL Server through the use of multi-tenancy.  Microsoft also does not yet provide a performance guarantee for SQL Azure which is interesting.  Further, the use of multi-tenancy can be viewed from two different perspectives, SQL Azure wants to abstract away from software creators the implementation and management details to provide ease of use.  However, competitive vendors like Oracle, IBM and Sybase may argue developers want this level of power and configuration and they are provided it through the use of virtual machines.  

Rate this Article

Adoption
Style

BT