Azure SQL Database Managed Instance: Easing the Move from On Premise to Cloud

Abstract

Managed Instances join the other offerings--of both the Singleton and Elastic Pools variety--for the Azure SQL Database line of products and services. [...]the release of Azure SQL Database Managed Instance, the options for Microsoft Azure SQL Database deployments constrained access to any SQL Server instance level settings. Most of the routine administrative tasks typically performed by database administrators are now automatically managed by the automation afforded in the Azure platform: backups are automated and retention is configurable with Azure SQL Database Managed Instance, yet customers still can initiate additional backups should the need arise. The reduced overhead of managing hardware, labor costs, power and service contracts, as well as the efficiencies that come into play, may very well exceed the additional costs of multiple Managed Instances over a single server hosted in a company’s data center.

Microsoft has announced the latest addition to its Azure cloud offerings for SQL Server: Azure SQL Database Managed Instance. This new product is still in preview, but it promises to fill a significant gap for those customers looking to lift and shift applications backed by Microsoft SQL Server from on premise to the cloud. Managed Instances join the other offerings--of both the Singleton and Elastic Pools variety--for the Azure SQL Database line of products and services.

Until the release of Azure SQL Database Managed Instance, the options for Microsoft Azure SQL Database deployments constrained access to any SQL Server instance level settings. There were also no options in either Singleton or Elastic Pools for SQL Server Agent, the job scheduling and execution service for Microsoft SQL Server. Database administrators found the lack of control over instance-level settings such as min/max server memory and parallelism to be too limiting to effectively control performance.

Even more damning was the lack of SQL Server Agent in either Singleton or Elastic Pools. This gap severely limited the ability for enterprises to lift and shift their SQL Server instances from on premise to the cloud because the agent is used extensively for SQL Server job scheduling--for everything from routine database maintenance tasks to complex data warehouse loading processes, as well as critical business logic that runs in the data layer but requires execution at specific times or on a repeatable schedule.

The inability to control instance-level behavior for SQL Server and to schedule specific tasks natively inside of SQL Server through SQL Server Agent forced a decision between remaining in an “earthed” solution on premise and migration to the Azure Cloud via a dedicated Azure VM (IaaS). While available, the cost structure for Azure IaaS is significantly different than what is offered through Azure SQL Database, whether Singleton or Elastic Pools. The announcement of Azure SQL Managed Instance changes the playing field significantly.

Platform as a Service (PaaS)

Azure SQL Database Managed Instance is slated to become the preferred option for enterprises looking to migrate their existing on premise--and even existing IaaS deployments--to a fully managed platform as a service (PaaS) offering. PaaS in Azure SQL Database Managed Instance means hardware purchasing, servicing and implementation are no longer worries for the enterprise. Servicing the infrastructure is all controlled by the Azure platform. It also means all patching and upgrades are handled behind the scenes and off the plate of DBAs, freeing them for more strategic tasks.

Business Continuity with Azure SQL Database Managed Instances

Thanks to being backed with Azure Premium Storage, Azure SQL Database Managed Instance boasts 99.99% uptime. Redundancy built into the structure under Azure SQL Database Managed Instance also means high availability is built into the architecture. This is a significant benefit and cost savings over traditional on premise AlwaysOn Availability Group (AG) deployments in that there is no cost of procuring and staging a fully clustered environment with at least a complete matching server. It’s also beneficial from a servicing and management aspect because any failovers allowing for redundancy in a Managed Instance are completely controlled within the Azure architecture.

Highly available solutions require senior level staff to not only build but also administer. But when deploying or migrating to a Managed Instance, the complexity in building the high-availability structures is removed from the process altogether. Most of the routine administrative tasks typically performed by database administrators are now automatically managed by the automation afforded in the Azure platform: backups are automated and retention is configurable with Azure SQL Database Managed Instance, yet customers still can initiate additional backups should the need arise. Native to Azure SQL Database Managed Instance is the ability to recover to a point in time, allowing for recoverability immediately prior to any event that would require restoration and salvage of a database.

Security and Compliance

Unlike other Azure SQL Database offerings, Azure SQL Database Managed Instance is an isolated environment. You are not sharing an instance with any other customer in a multi-tenant fashion. You have dedicated compute and storage and AD authentication support, unlike with Singleton or Elastic Pool offerings. Data is encrypted while in transit, and, as with other Azure SQL Database offerings, threat detection and SQL auditing are built in.

Currently under preview, there are two offerings for hardware:

• Gen 4: Intel E5-2673 v3 (Haswell) 2.4 GHz processors, attached SSD vCore = 1 PP (physical core) available in 8, 16, or 24 vCores
• Gen 5: Intel E5-2673 v4 (Broadwell) 2.3 GHz processors, fast eNVM SSD, vCore=1 LP (hyper-thread) available in 8, 16, 24, 32, and 40 vCores

Product Specifications

• Memory differs between the CPU options. Gen4 allows for 7GB per core, whereas Gen5 comes in at 5.5GB per core.
• Near 100% feature parity with the latest version of SQL Server Enterprise edition is the goal once the preview period ends. The platform itself is the latest version of Microsoft SQL Server available.
• Storage is Azure Premium storage, with a minimum of 32GB of storage and a maximum of 8TB of storage.
• Current IOPS range from 500 to 7,500 per data file. These speeds are dictated by the limits of Azure Premium Storage.

Hard Limits on Database Count May Be Cause for Concern

Currently, Azure SQL Database Managed Instances have a hard cap of 100 databases per managed instance. While that is sufficient for most enterprises, certain lines of business such as IaaS providers with hundreds and even thousands of databases per instance, will find that limit likely makes Managed Instance incapable of supporting their structure in a pure lift-and-shift process. If horizontal scaling is possible for an enterprise’s needs, then it’s possible that scaling from a single on premise SQL Server instance to multiple Managed Instances is still a viable option if the math plays out. There are significant cost savings when PaaS is involved. The reduced overhead of managing hardware, labor costs, power and service contracts, as well as the efficiencies that come into play, may very well exceed the additional costs of multiple Managed Instances over a single server hosted in a company’s data center.

Next Steps

If Managed Instance sound like an option worth pursuing, Microsoft has a variety of resources to aid in the decision-making process. In addition to reaching out to your dedicated Microsoft Partner you can also find resources at the links below.

• Azure SQL Database Managed Instance Overview
• Azure SQL Database Pricing
• New Options to Modernize Your Applications with Azure SQL Database

CREDIT: Tim Ford