Category Archives: Database

This post was co-authored by Jamie Reding, Senior Program Manager, Sadashivan Krishnamurthy, Principal Architect, and Bob Ward, Principal Architect.

Today, most applications are running online transactional processing (OLTP) transactions. Online banking, purchasing a book online, booking an airline ticket, sending a text message, and telemarketing are examples of OLTP workloads. OLTP workloads involves inserting, updating, and/or deleting small amounts of data in a database and mainly deals with large numbers of transactions by large number of users. Majority of OLTP workloads are read heavy, use diverse transactions, and utilizes wide range of data types.

Azure brings many price-performance advantages for your workloads with SQL Server on Azure Virtual Machines (VM) with a wide range of Azure Virtual Machine series and Azure disk options. Memory optimized VM series like Intel based Es_v3 series or AMD based Eas_v3 series offer high virtual CPU (vCPU) to memory ratio at a very low cost. Constraint vCPU capable VM sizes offer reduced cost of SQL Server licencing by constraining the vCPU abailable to the VM, while maintaining the same memory, storage, and input or output (I/O) bandwidth. Premium Solid State Drives (SSDs) deliver high-performance and low-latency managed disks with high IOPS and throughput capabilities needed for SQL Server data and log files. Standard SSDs, cost-effective storage options optimized for consistent performance, come as an optimum destination for most SQL Server backup files.

In addition to the large IOPS capacity of the Premium Disks, Azure Blobcache is a huge value for mission critical OLTP workloads as it brings significant additional high-performance I/O capacity to Azure Virtual Machine for free. Blobcache is a multi-tier caching technology enabled by combining the VM RAM and local SSD. You can host SQL Server data files on premium SSD managed disks with read only Blobcache and leverage extremely high-performance read I/Os that exceed the underlying disk’s capabilities. High scale VMs comes with very large Blobcache sizes that can host the all the data files for most applications. As all I/O activity from the Blobcache is free, you can boost application throughput with extremely high performance reads and optimize price-performance by only paying for the writes. Considering the majority of the OLTP workloads today come with 10 to 1 ratio for read and write, this is up to a 90 percent price-performance gain.

Additionally, for workloads demanding very low I/O latency, Azure ultra-disks deliver consistent low latency disk storage at high throughput and high IOPS levels. Ultra-disks maximize application throughput if the workload was bottlenecked on I/O latencies.

Based on read to write ratio, transaction complexity and scale pattern you may choose to use TPC-E or TPC-C for performance measurements. In general, TPC-E represents majority of the OLTP workloads in these days as it includes complex transactions and high read to write ratio. If you have write intensive workloads running simple transactions, then you can leverage the simplicity of TPC-C benchmark for performance validation. For detailed testing of SQL Server performance on Azure Virtual Machines with a scaled down TPC-E workload and HammerDB TPC-C kit please see this article.

Get started with SQL Server in Azure Virtual Machines

Azure SQL Virtual Machine service offers full control on the VM, storage and SQL Server configuration and gives you full flexibility to deploy the most cost-efficient solution for your workload’s specific requirements. You can create an SQL VM with performance optimized storage configuration enabled by SQL VM resource provider today and boost price-performance gain for your workload with performance best practices for SQL virtual machine.

Click here to start testing with free SQL Server Developer edition images on Azure Virtual Machines.

Get started on your Azure migration with the Data Migration Guide.

Composite indexes were introduced in Azure Cosmos DB at Microsoft Build 2019. With our latest service update, additional query types can now leverage composite indexes. In this post, we’ll explore composite indexes and highlight common use cases.

Index types in Azure Cosmos DB

Azure Cosmos DB currently has the following index types that are used for the following types of queries:

Range indexes:

• Equality queries
• Range queries
• ORDER BY queries on a single property
• JOIN queries

Spatial indexes:

• Geospatial functions

Composite indexes:

• ORDER BY queries on multiple properties
• Queries with a filter as well as an ORDER BY clause
• Queries with a filter on two or more properties

Composite index use cases

By default, Azure Cosmos DB will create a range index on every property. For many workloads, these indexes are enough, and no further optimizations are necessary. Composite indexes can be added in addition to the default range indexes. Composite indexes have both a path and order (ASC or DESC) defined for each property within the composite index.

ORDER BY queries on multiple properties

If a query has an ORDER BY clause with two or more properties, a composite index is required. For example, the following query requires a composite index defined on age and name (age ASC, name ASC):

SELECT * FROM c WHERE c.age ASC, c.name ASC

This query will sort all results in ascending order by the value of the age property. If two documents have the same age value, the query will sort the documents by name.

Queries with a filter as well as an ORDER BY clause

If a query has a filter as well as an ORDER BY clause on different properties, a composite index will improve performance. For example, the following query will require fewer request units (RU’s) if a composite index on name and age is defined and the query is updated to include the name in the ORDER BY clause:

Original query utilizing range index:

SELECT * FROM c WHERE c.name = “Tim” ORDER BY c.age ASC

Revised query utilizing a composite index on name and age:

SELECT * FROM c WHERE c.name = “Tim” ORDER BY c.name ASC, c.age ASC

While a composite index will significantly improve query performance, you can still run the original query successfully without a composite index. When you run the revised query with a composite index, it will sort documents by the age property. Since all documents matching the filter have the same name value, the query will return them in ascending order by age.

Queries with a filter on multiple properties

If a query has a filter with two or more properties, adding a composite index will improve performance.

Consider the following query:

SELECT * FROM c WHERE c.name = “Tim” and c.age > 18

In the absence of a composite index on (name ASC, and age ASC), we will utilize a range index for this query. We can improve the efficiency of this query by creating a composite index for name and age.

Queries with multiple equality filters and a maximum of one range filter (such as >,<, <=, >=, !=) will utilize the composite index. In some cases, if a query can’t fully utilize a composite index, it will use a combination of the defined composite indexes and range indexes. For more information, reference our indexing policy documentation.

Composite index performance benefits

We can run some sample queries to highlight the performance benefits of composite indexes. We will use a nutrition dataset that is used in Azure Cosmos DB labs.

In this example, we will optimize a query that has a filter as well as an ORDER BY clause. We will start with the default indexing policy which indexes all properties with a range index. Executing the following query as referenced in the image below in the Azure Portal, we observe the query metrics:

Query metrics:

This query, with the default indexing policy, required 21.8 RU’s.

Adding a composite index on foodGroup and _ts and updating the query text to include foodGroup in the ORDER BY clause significantly reduced the query’s RU charge.

Query metrics:

After adding a composite index, the query’s RU charge decreased from 21.8 RU’s to only 4.07 RU’s. This query optimization will be particularly impactful as the total data size increases. The benefits of a composite index are significant when the properties in the ORDER BY clause have a high cardinality.

Creating composite indexes

You can learn more about creating composite indexes in this documentation. It’s simple to update the indexing policy directly through the Azure Portal. While creating a composite index for data that’s already in Azure Cosmos DB, the index update will utilize the RU’s leftover from normal operations. After the new indexing policy is defined, Azure Cosmos DB will automatically index properties with a composite index as they’re written.

Explore whether composite indexes will improve RU utilization for your existing workloads on Azure Cosmos DB.

When data is the lifeblood of your business, you want to ensure your databases are reliable, secure, and available when called upon to perform. Service level agreements (SLA) set an expectation for uptime and performance, and are a key input for designing systems to meet business needs. We recently published a new version of the SQL Database SLA, guaranteeing the highest availability among relational database services as well as introducing the industry’s first business continuity SLA. These updates further cement our commitment to ensuring your data is safe and the apps and processes your business relies upon continue running in the face of a disruptive event.

As we indicated in the recent service update, we made two major changes in the SLA. First, Azure SQL Database now offers a 99.995% availability SLA for zone redundant databases in its business critical tier. This is the highest SLA in the industry among all relational database services. It is also backed by up to a 100% monthly cost credit for when the SLA is not maintained. Second, we offer a business continuity SLA for databases in the business critical tier that are geo-replicated between two different Azure regions. That SLA comes with very strong guarantees of a five second recovery point objective (RPO) and a 30 second recovery time objective (RTO), including a 100% monthly cost credit when the SLA is not maintained. Azure SQL Database is the only relational database service in the industry offering a business continuity SLA.

The following table provides a quick side by side comparison of different cloud vendors’ SLAs.

Data current as of July 18, 2019 and subject to change without notice.

Understanding availability SLA

The availability SLA reflects SQL Database’s ability to automatically handle disruptive events that periodically occur in every region. It relies on the in-region redundancy of the compute and storage resources, constant health monitoring and self-healing operations using automatic failover within the region. These operations rely on synchronously replicated data and incur zero data loss. Therefore, uptime is the most important metric for availability. Azure SQL Database will continue to offer a baseline 99.99% availability SLA across all of its service tiers, but is now providing a higher 99.995% SLA for the business critical or premium tiers in the regions that support availability zones. The business critical tier, as the name suggests, is designed for the most demanding applications, both in terms of performance and reliability. By integrating this service tier with Azure availability zones (AZ), we leverage the additional fault tolerance and isolation that AZs provide, which in turn allows us to offer a higher availability guarantee using the compute and storage redundancy across AZs and the same self-healing operations. Because the compute and storage redundancy is built in for business critical databases and elastic pools, using availability zones comes at no additional cost to you. Our documentation, “High-availability and Azure SQL Database” provides more details of how the business critical service tier leverages availability zones. You can also find the list of regions that support AZs in our documentation, “What are Availability Zones in Azure.”

99.99% availability means that for any database, including those in the business critical tier, the downtime should not exceed 52.56 minutes per year. Zone redundancy increases availability to 99.995%, which means a maximum downtime of only 26.28 minutes per year or a 50% reduction. A minute of downtime is defined as the period during which all attempts to establish a connection failed. To achieve this level of availability, all you need to do is select zone redundant configuration when creating a business critical database or elastic pool. You can do so programmatically using a create or update database API, or in Azure portal as illustrated in the following diagram.

We recommend using the Gen5 compute generation because the zone redundant capacity is based on Gen5 in most regions. The conversion to a zone redundant configuration is an asynchronous online process, similar to what happens when you change the service tier or compute size of the database. It does not require acquiescing or taking your application offline. As long as your connectivity logic is properly implemented, your application will not be interrupted during this transition.

Understanding business continuity SLA

Business continuity is the ability of a service to quickly recover and continue to function during catastrophic events with an impact that cannot be mitigated by the in-region self-healing operations. While these types of unplanned events are rare, their impact can be dramatic. Business continuity is implemented by provisioning stand-by replicas of your databases in two or more geographically separated locations. Because of the long distances between those locations, asynchronous data replication is used to avoid performance impact from network latency. The main trade-off of using asynchronous replication is the potential for data loss. The active geo-replication feature in SQL Database is designed to enable business continuity by creating and managing geographically redundant databases. It’s been in production for several years and we have plenty of telemetry to support very aggressive guarantees.

There are two common metrics used to measure the impact of business continuity events. Recovery time objective (RTO) measures how quickly the availability of the application can be restored. Recovery point objective (RPO) measures the maximum expected data loss after the availability is restored. Not only do we provide SLAs of five seconds for RPO and 30 seconds for RTO, but we also offer an industry first, 100% service credit if these SLAs are not met. That means if any of your database failover requests do not complete within 30 seconds or any time the replication lag exceeds five seconds in 99th percentile within an hour, you are eligible for a service credit for 100% of the monthly cost of the secondary database in question. To qualify for the service credit, the secondary database must have the same compute size as the primary. Note however, these metrics should not be interpreted as a guarantee of automatic recovery from a catastrophic outage. They reflect the Azure SQL’s reliability and performance when synchronizing your data and the speed of the failover when your application requests it. If you prefer a fully automated recovery process, you should consider auto-failover groups with automatic failover policy, which has a one hour RTO.

To measure the duration of the failover request, i.e. the RTO compliance, you can use the following query against the sys.dm_operation_status in master database on the secondary server. Please be aware that the operation status information is only kept for 24 hours.

SELECT  datediff(s, start_time, last_modify_time) as [Failover time in seconds] FROM sys.dm_operation_status    WHERE major_resource_id = '',  operation=’ALTER DATABASE FORCE FAILOVER ALLOW DATA LOSS ’, state=2 ORDER BY start_time DESC;

The following query against sys.dm_replication_link_status in the primary database will show replication lag in seconds, i.e. the RPO compliance, for the secondary database created on partner_server. You should run the same query every 30 seconds or less to have a statistically significant set of measurements per hour.

SELECT link_guid, partner_server, replication_lag_sec FROM sys.dm_replication_link_status

Combining availability and business continuity to build mission critical applications

What does the updated SLA mean to you in practical terms? Our goal is enabling you to build highly resilient and reliable services on Azure, backed by SQL Database. But for some mission critical applications, even 26 minutes of downtime per year may not be acceptable. Combining a zone redundant database configuration with a business continuity design creates an opportunity to further increase availability for the application. This SLA release is the first step toward realizing that opportunity.