Making SQL sense

+44 (0)20 3051 3595 info@coeo.com
coeoClose

Making SQL sense

+44 (0)20 3051 3595 info@coeo.com

Careers

We're looking for people who share our commitment to excellence in Microsoft's data platform to join us consultants working on exciting business intelligence, analytics, and SQL Server projects on-premises and in the cloud.

Azure SQL Database Managed Instances - a real game changer!

The Coeo Blog

Since its announcement 8 years ago, Azure SQL Database (DB) has been the go-to Platform-as-a-Service (PaaS) offering for SQL Server workloads in Microsoft Azure. During that time, SQL DB has matured to the point where the chasm that once existed with the boxed product we know and love, has narrowed significantly. Yet despite all of this, the offering has come under scrutiny in recent years from the broader community; no support for SQL Server Agent, no support for SQL CLR, no support for native cross-database transactions, no support for virtual network isolation, are just some examples of the feature limitations we still experience with Azure SQL Database today. Fortunately, all of this is about to change with Microsoft's announcement for Azure SQL Database Managed Instances.

What is it?

This new offering was announced earlier in the year and provides customers with a fully managed SQL Server instance in Microsoft Azure. It's not a replacement for Azure SQL Database, it's a newer flavour designed to simplify lift-and-shift migrations while providing the benefits of PaaS. Since release, Azure SQL DB has been targeted at new, cloud-based projects with minimal time-to-market constraints. However, customers have struggled to migrate existing applications including commercial-of-the-shelf (COTS) products to Azure SQL DB for a variety of reasons, including limited vendor support. Only time will tell if this problem will be solved by Managed Instances.

SQLPaaS.png

 

What are we likely to see?

Unlike a standalone Azure SQL DB, when you spin up a Managed Instance, the entirety of that SQL Server instance will be surfaced to you. That means you'll be able to deploy and interact with SQL Server Agent jobs, deploy multiple databases onto the same instance and configure other instance level properties we're familiar with. Microsoft will provide an SLA of 99.99% for Managed Instances, which is in line with traditional Azure SQL Databases. High availability is built-in with geo-redundancy also configurable. You can place your Managed Instance onto an existing virtual network subnet with a private IP, thereby addressing any concerns around network security and isolation.

Microsoft has stated that Managed Instances will provide nearly all features that are available within the SQL Server boxed-product, significant highlights include:

  • SQL Server Agent
  • Database Mail
  • Native database backup and restore
  • Linked Servers
  • Cross-database transactions
  • SQL CLR modules
  • Transparent Data Encryption (TDE)
  • Row-Level Security
  • Always Encrypted
  • SQL Audit

That is not an exhaustive list and may not reflect what's available by the time we hit public preview! The eagle-eyed amongst you may notice a lack of support for Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS). Microsoft has stated that none of these will be installed along-side your Managed Instances. This is hardly surprising given the investments Microsoft has made in PaaS offerings for each of these components: Azure-SSIS integration runtime (IR) running in Azure Data Factory for SSIS packages and Azure Analysis Services (AAS) for tabular models. There is a bit more choice with SSRS; you can either continue running SSRS on a virtual machine in Microsoft Azure or re-write the reports for PowerBI.

As we head towards general availability, Microsoft has stated that more features are likely to be included between now and then. Don't be disappointed if the features you're looking for aren't available at release, the chances are they will be at some point. Managed Instances is currently in private preview and Microsoft has said we should see it in public preview by the end of this year, with a view to being generally available in the first half of 2018. 

Migrations

Historically, migrating SQL Server databases to PaaS has been an unguided set of complex tasks involving various tools. To simplify and support the migration journey, Microsoft announced last month the public preview of the Azure Database Migration Service. This is a managed service offering in Azure, designed to provide you with a guided migration experience to a variety of target data platforms, including: Azure SQL Database, Azure SQL Database Managed Instance and SQL Server on Azure virtual machines. Whilst this new offering doesn't do everything for you, it does provide you with actionable tasks and a general framework to follow.

Service tiers and pricing

To date, Microsoft has not made public any information regarding pricing. However, what do we know is that through the Azure Hybrid Use Benefit, customers with Software Assurance will be able to use their on-premises core licenses to access discounts for deployments of Managed Instances.

Customers will be able to select the required compute resources independent of storage, giving more flexibility and choice when it comes to deploying solutions on the platform. Microsoft has created two service tiers; General Purpose and Business Critical. The former will provide a high SLA with fast remote storage (limited to 35TB per instance), whereas the latter will provide a very high SLA with faster storage based on local SSDs (limited to 4TB per instance). Within each service tier, there are three distinct compute sizes; 8 vCores, 16 vCores, 24 vCores and will be charged on a per vCore per hourly basis. Interestingly, Microsoft has not adopted Database Transaction Units (DTUs) for Managed Instances (as used in Azure SQL Database), which should make it easier when sizing your workload requirements ahead of a migration. When it comes to storage, the space used by your databases and backups will be charged on a per GB per hourly basis. IOPs will also be charged per million IOs. Retaining backups for less than 7 days will be free of charge, after that the storage used will be chargeable thereby making backup archives gradually more expensive as data volumes grow.

It seems Microsoft has simplified the service tier definitions for Managed Instances, compared to those available with traditional Azure SQL Databases, although this may change at General Availability. I believe this is a positive step and suggests Microsoft has learnt from their experience with Azure SQL Database and understand that customers want clear and measurable performance levels, that they comprehend, not an abstracted unit or measure that's an amalgamation of hardware factors that is opaque and difficult to unpick.

Closing thoughts

It's worth emphasizing that all of this is subject to change before General Availability. This blog includes the highlights of this fantastic session from Microsoft Ignite.

Make no mistake, this is a significant undertaking from Microsoft. To transform a product like SQL Server into a fully-fledged managed service is a game changer! It will present new opportunities for customers and product vendors alike to modernise their data platforms in ways that haven't been possible with traditional PaaS services to date.

Back to the Coeo blog

Subscribe to Email Updates