Joey on SQL Server

A Closer Look at the Newest Azure SQL Database Features

Microsoft's cloud DBaaS has had a whirlwind month with the introduction of managed instances, database migration tools and more. Here's what each feature does and when to use it.

• By Joey D'Antoni
• 03/19/2018

This month, Microsoft introduced the public preview of a new database offering named "Azure SQL Database Managed Instance."

This offering is in the Azure SQL Database family, though it bears more resemblance to your traditional on-premises SQL Server than it does to the cloud Database as a Service (DBaaS) product. The Azure SQL Database Managed Instance feature is designed to make it easier for customers to migrate their existing third-party applications by maintaining 100 percent feature compatibility with SQL Server.

Managed Instance also offers all of the benefits of a cloud service offering like built-in high availability and automated patching. Additionally, the Managed Instance architecture allows for native virtual network integration. Support for virtual networks was a late addition to Azure SQL Database, and still requires the use of public endpoints. The Managed Instance service is also fully isolated and offers a private IP address.

In terms of programmability and feature compatibility, Managed Instance supports compatibility all the way back to SQL Server 2008, and allows for direct migration of databases all the way back to SQL Server 2005. Direct migration means you can copy your on-premises backups to Azure (or back up directly to Azure) and restore them into the service seamlessly. Other features Microsoft has enabled include service broker, change data capture and linked servers, which had previously been limiters for moving on-premises applications into Azure SQL Database.

Common Language Runtime (CLR), which was removed from Azure SQL Database last year, is also available, allowing users to implement functions and stored procedures based on .NET code. Managed Instance also adds the SQL Agent service, which performs scheduling and notification tasks, and was a gap between SQL Server and Azure SQL Database.

Unlike Azure SQL Database, Managed Instances are priced using a more traditional model. Users simply select the number of virtual cores -- eight, 16 or 24 -- and the rest of the configuration. The amount of RAM allocated to the virtual machine (VM) is proportional to the number of cores. There is no abstraction like the database transaction unit (DTU) model in Azure SQL Database, which uses DTUs to abstract RAM, CPUs and IO. I would look for Microsoft to move to this virtual core-based model across both platforms. By moving to this core-based model, Microsoft would allow for a hybrid-use licensing benefit for customers; if you have an existing on-premises SQL Server license with software assurance, you can bring it to the Managed Instance service and reduce your overall costs. This is a big change from the existing Azure SQL Database model, which lacks a "bring your own license" option.

Currently, the Managed Instance service supports up to 8TB of data. Storage performance in the service is based on the number of data files, so for high-throughput databases, you would ideally spread your data across multiple data files.

Like Azure SQL Database, Managed Instances rely on Azure Active Directory (AD) for authentication (SQL Authentication is available, as well). High availability and backups are built natively into the service. Managed Instance currently offers seven-day backup retention during the public preview. Given that Azure SQL Database offers a long-term data retention option, I would suspect such an option would be available for Managed Instance at general availability. The service offers a 99.99 percent uptime SLA and, in addition to automated backups, also allows for user-created backups. However, those backups will not be able to be restored to an on-premises SQL Server.

To reduce the effort required for cloud migrations, Microsoft also announced support for the new Azure Database Migration Service. This service allows for data migration with minimal downtime by keeping your data in sync with your on-premises server. The data migration service also supports Azure SQL Database and is currently free during the preview. However, given that there's a pricing page on the product's Web site, I would not be surprised to see Microsoft charge for the service.

Azure SQL Database Availability Zone Support
In the same announcements, Microsoft also introduced a preview for Zone Redundancy for Azure SQL Database, adding an extra level of high availability to the platform.  If you aren't familiar with the concept of availability zones, they were added in late 2017 and physically separate VM workloads (and, in this case, the underlying VMs for the SQL service) across Azure datacenters in a specific region. This design is built around the notion of network latency between the regions. Availability zones support up to three replicas.

In a default configuration, Azure SQL Database creates a set of replicas in a single datacenter using a variant of the Always On Availability Group technology that is built into SQL Server. The way Zone Redundancy is implemented within Azure SQL Database is that for Premium-tier databases, you can place the different replicas across multiple availability zones, giving your application a higher level of uptime.

Microsoft does mention here that there may be a performance impact for busy OLTP applications, since in order to commit on the primary replica, the data transaction must make it to the transaction log of the secondary replica. However, as Azure CTO Mark Russinovich mentioned in his session at Microsoft Ignite last year, the latency boundary for an availability zone is 600 microseconds:

In my experience, with synchronous replication technologies and databases, most applications are tolerant of that level of latency, and users should not experience any impact.

However, applications that are extremely sensitive to performance -- for example, an exchange trading system -- can still choose to remain in a single datacenter implementation. Users can revert back to the default configuration, which will result in a short outage to cutover.

You will want to use availability zones if you need the absolute highest levels of uptime and data protection for your databases. Even in the event of a datacenter failure, you will not lose committed data, and based on the other availability zone metrics Microsoft has provided, your database uptime will meet the 99.95 percent Azure promises for availability zone deployments.

As you can see, the pace of development in the cloud is rapid and never-ending. As Microsoft continues to deliver new offerings and features, IT pros will have to adjust designs to best benefit their users. The flexibility of the cloud also means that it is easier to change your design after deployment, something that was much more of challenge with physical infrastructure.