Introduction to Azure SQL Database (PaaS)

Azure SQL Database is the new database as a service offering from Microsoft in the cloud. It allows you to spin up a database in Azure within a minute or two without worrying about the VM size, OS or SQL installation etc.  Azure SQL Database is suitable for developers or DevOps designing new applications who do not need to worry about installation, configuration and management of SQL Server instance and would like to focus on the functionality and features provided by the relational database.

Azure SQL Database allows to scale up single databases with no downtime and provides predictable performance that can be dialed up or down as needed, while you can also choose elastic databases to scale out to thousands of databases within a budget that you control, leveraging the powerful manageability features of elastic database jobs and the programmatic features of elastic database tools.

Azure SQL Database provides an SLA of 99.99% (Four nines) which translates to downtime of 52.56 minutes per year. The built-in data protection via auditing, restore, and geo-replication helps ensure your data is protected. SQL Database provides you with the ability to restore your data to any point-in-time from any transaction up to 35 days. You can also replicate your data to an Azure region of your choice and easily implement a geographic disaster recovery policy designed for your business needs. The latest version of SQL Database, V12, provides nearly complete compatibility with the SQL Server on premise engine (box version).

Azure SQL Database is available in Basic, Standard, and Premium service tiers which support light-weight to heavy-weight database workloads, so you can move across or blend the tiers together for innovative application designs. With the power and reach of Azure, you can mix-and-match Azure services with SQL Database to meet your unique modern app design needs, drive cost and resource efficiencies, and unlock new business opportunities.

Azure SQL Database Service Tiers

As mentioned earlier, Azure SQL Database is available in 3 tiers namely Basic, Standard and Premium service tiers. Each service tier provides an increasingly higher amount of capacity and processing power measured in terms of Database Transaction Units (DTUs). To understand Service Tiers it is first important to understand Database Transaction Units (DTUs) which is a new unit of transactional performance coined in Azure SQL Database Benchmark (ASDB).

Database Transaction Units (DTUs)

Database Transaction Units is not a real world metrics like CPU Utilization or transactions per second but it is a complex calculation which is coined as a measure or unit of relative capacity of a database performance level provided by each service tier based on a blended measure of CPU, memory, and read and write rates offered by each performance level. Doubling the DTU rating of a database equates to doubling the database power. The reason for introducing this unit was to provide a relative measure of transactional processing power of the SQL Database across different service tiers. Just as IOP is to storage, Frequency is to CPUs, DTUs is to Azure SQL Database.

Microsoft Product team performed a benchmark testing on Azure SQL Database to try to produce a mix of database operations (insert, update, delete and select) common in all OLTP workload against a schema containing range of tables and data types. The goal of the benchmark is to provide a reasonable guide to the relative performance of a database that might be expected when scaling up or down between performance levels. The detailed overview of the Azure SQL Database Benchmark (ASDB) can be found in the following article

https://msdn.microsoft.com/en-us/library/azure/dn741327.aspx

The following table summarizes the results of running the benchmark against each performance level.

Table 5: Azure SQL Database Benchmark Summary

Now that you understand Database Transaction Units, let us compare the various service tiers of Azure SQL Database below

Basic Tier

Basic tier is designed for applications with a very low transactional activity. It might be preferred tier when a developer is developing the application with only single user connected to the database and querying on the database.

A typical use case is a lightweight application that needs a small database with a single operation at any given point in time.

Table 6: Performance Size and Features supported by Basic Tier

Standard Tier

Standard Tier offers better performance and built-in business continuity features compared to Basic Tier which can support multiple concurrent transaction. A typical use case is an application with not too high concurrency that needs a medium size database.

Table 7: Performance & Size Supported by Standard Tier

Table 8: Features Supported by Standard Tier

Premium Tier

Premium Tier offers the best level of performance and advanced business continuity features with active geo-replication in up to 4 Azure regions of your choice. A typical use case is a mission-critical application with high transactional volume and many concurrent users.

Table 9: Performance & Size Supported by Premium Tier

Table 10: Features Supported by Premium Tier

Note:  The above specs for the service tiers are the numbers supported by Azure SQL Database as of today. As Azure SQL Database develops and grows, the specs or service tier may also grow. The latest details on the service tier can be found in the following article:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/

Azure Database Resource Limits

Besides the size & transactional limitation imposed by Service tiers on Azure SQL Database, there are certain additional resource restriction on the Azure SQL Database to ensure a bad written code doesn’t disrupt the performance of the server on which the database is deployed, as azure is a shared multi-tenant environment.

Table 11: Resource limits on Azure SQL Database

The error codes received when you hit any of the above listed limitation and the latest resource limits are documented in the following article

https://azure.microsoft.com/en-us/documentation/articles/sql-database-resource-limits/

What's new in Azure SQL Database V12

When Azure SQL Database was first introduced, one of the major drawbacks was lack of its parity with on premise SQL Server version. It appeared as if Microsoft took a step backwards in SQL Server with Azure SQL Database but it may be due to the fact the Azure SQL Database code was re-written to make it as a cloud service. The latest version of Azure SQL Database is at par with SQL 2014 and in fact some of the latest service updates which went Generally Available (GA) have exceeded the features provided by SQL 2014 and which are due to come in on premise version of SQL 2016.

Following are some of the enhancements seen in Azure SQL Database v12

T-SQL Programming and Application Compatibility

A key goal for SQL Database V12 was to improve the compatibility with Microsoft SQL Server 2014. Among other areas, V12 achieves parity with SQL Server in the important area of programmability. For instance:

• Common Language Runtime (CLR) assemblies
• Window functions, with OVER
• XML indexes and selective XML indexes
• Change tracking
• SELECT...INTO
• Full-text search

However there are still T-SQL Features which are either partially supported or not supported in Azure SQL Database all of which are documented in the following MSDN article

https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/

Performance Enhancements

In V12, the database throughput units (DTUs) allocated to all Premium performance levels was increased by 25% and new performance related features were added like

• In-Memory Columnstore Index
• Table Partitioning by rows

• Dynamic Management Views (DMVs) and Extended Events (XEvents) to help monitor and tune performance

Security Enhancements

Security is a primary concern for anyone who runs their business in the cloud. The latest security features released in V12 include:

• Row-level security (RLS)
• Dynamic Data Masking
• Contained databases
• Application roles managed with GRANT, DENY, REVOKE
• Transparent Data Encryption (TDE)

Business Continuity Enhancements

V12 made some major improvements in recovery point objectives (RPOs) and estimated recovery times (ERTs) for geo-restore and geo-replication which gives you the confidence for running mission critical application on Azure SQL Database. The following table list the RPO and ERTs enhancements in V12.

Table 12: Business Continuity Enhancements in V12

Introducing Elastic Database Pool

Elastic Database Pool allows to programmatically create, maintain and manage performance (including DTUs) across group of database within the desired spent limit. This feature is specifically beneficial for cloud SaaS (Software as a Service) developers who like to scale out databases and would prefer to leverage the shared resources in the pool to overcome some of the performance & sizing restrictions imposed by service tier.

At the time of writing this book, this feature is still in preview and will go live soon but you can track the Service Updates for features with Azure SQL Database by subscribing to the following feeds

http://azure.microsoft.com/en-us/updates/?service=sql-database&update-type=general-availability

Developing with Azure SQL Database

Azure SQL Database supports connectivity from clients running on Windows, Mac OS and Linux. Further Microsoft provides SDKs and samples for developing on Azure SQL Database for variety of programming language namely .Net, Java, PHP, Python, Ruby. You can find short quick start samples to connect, query and retry to Azure SQL Database from various programming languages in the following article

https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-quick-start-client-code-samples/