Windows Azure SQL Database (WASD) Primer

  • Article

Are you a SQL Server DBA who is now being asked to migrate databases to Windows Azure SQL Database
(WASD)? Are you a developer who is writing code for a cloud service that shall use SQL Database as the back-end?  

This is a series of blogs by Microsoft CSS engineers for guidance & strategies you can use to make a smooth transition to this new platform. The content is based on issues encountered while supporting customers that were once in your shoes and the lessons learned in the process.

There already is a vast amount of documentation for the various aspects of WASD in MSDN. This blog series aims to provide a summarized version with which you can get started and fills gaps where applicable so that you can get things done faster.

In this first installment of the blog series let us start the series by understanding some common terminology when it comes to WASD.

  • WASD is a Platform as a Service (PaaS) offering where multiple tenants (or users) are sharing resources to achieve reduced cost.

  • Data center is a host for thousands of machines (in multiple clusters) that host the various offerings in Windows Azure – of which SQL Database is one. As of writing of this blog we have the following data centers in various parts of the world

    • East US
    • North Central US
    • South Central US
    • West US
    • North Europe
    • West Europe
    • East Asia
    • Southeast Asia

     

    Note that traffic between data centers flows over the internet – which is why most customers host their application and database in the same data center for best performance.

  • Cluster is a group of machines within a data center that host the same Windows Azure service. It is the unit for building out cluster capacity. Note that this is unrelated to Windows clustering

  • A Machine is the lowest granularity for infrastructure in Windows Azure. It runs commodity hardware.

  • A Database Node is single physical instance of SQL Server installed on a machine. Each instance hosts databases for multiple tenants.

  • A Replica is a copy of your user database at any point in time. WASD has a primary replica for the read-write user database that is currently in use & visible to the user. Behind the scenes we maintain two or more secondary replicas for disaster recovery (DR) purposes that are not visible to the end user.

  • WASD has a Quorum commit model for any database transaction i.e. a transaction needs to be committed on primary and at least one of the secondary replicas

  • A Reconfiguration happens when there is a change of status of any of the replicas for the user database. For example, creation of a new secondary due to a node going offline or a secondary being promoted to primary due to a failover are examples of different causes of reconfiguration.

From an end-user perspective, you need to understand the following terms

  • A Subscription is the unique identifier for a customer in the Windows Azure platform. The subscription id is the common key used across the board for everything from billing, to creating VMs, databases or cloud services.
  • A Logical Server is a fully qualified domain name (e.g. xyz.database.windows.net) used to access your databases hosted in WASD. Think of it as analogous to server name for your on premise SQL Server.

Additional In-depth information about the architecture of WASD can be found here.

For SQL Server DBA's, a majority of the concepts that you know about SQL Server on premise still apply to WASD. Some of the key differences that you should be aware of are as follows:

  • Your connection to WASD is scoped to a single user database i.e. no cross database queries are allowed – since each database on your logical server might reside on different Database nodes)
  • From a system databases perspective you will only see a single master database per server – which stores your logins and some metadata tables. Tempdb is shared among tenants.
  • Each table needs to have a clustered index
  • Limitations on T-SQL syntax usage like lock hints, join hints, fill factor being unsupported and some other operations like ALTERs being partially supported
  • The feature set that is enabled for WASD is limited – so common solutions like database mirroring, replication etc. are not supported. For an exhaustive list look at the comparison outlined here
  • Queries that exceed system resource limits will be throttled

For .Net Developers here are some common terminology that you shall encounter while developing applications for WASD

  • Entity Framework is an ADO.Net technology that allows you to refer to database tables as object and operate on them using LINQ to SQL. These technologies are commonly used to write code against WASD.
  • Web Role is analogous to an Asp.Net web application that consists of aspx pages with data access logic. The difference is that this is hosted as a Windows Azure Cloud Service instead of running on an IIS box.
  • Worker Role is analogous to a Task running in the Windows Azure platform

 

References

  1. Inside Windows Azure SQL Database https://social.technet.microsoft.com/wiki/contents/articles/1695.inside-windows-azure-sql-database.aspx

  2. Comparison of SQL Server with Windows Azure SQL Database https://social.technet.microsoft.com/wiki/contents/articles/996.comparison-of-sql-server-with-windows-azure-sql-database.aspx

  3. General Guidelines and Limitations (Windows Azure SQL Database) https://msdn.microsoft.com/en-us/library/ee336245.aspx

  4. Resource Limits (for Engine Throttling) https://msdn.microsoft.com/en-us/library/windowsazure/dn338081.aspx

     

Author: - Rohit Nayak (@sqlrohit)

Reviewers: - Keith Elmore, José Batista-Neto

Escalation Services, Microsoft