Pythian Blog: Technical Track

Introduction to Azure SQL Managed Instance – SQL on the edge episode 17

by Warner Chaves on Aug 7, 2018 12:00:00 AM

SQL Server is one of the most adopted and widely-used database engines in the world. Almost every company either builds on it or uses some enterprise third-party software that uses it as the back end. For this reason, it makes sense that Microsoft has invested in their SQL database offering in Azure from the initial years of the cloud platform. Looking at the timeline of SQL-related Azure releases, we can also see that in the last few years, Microsoft has aggressively increased these investments to provide customers with more choice and more power. Let's see:
  • Azure SQL Database was released in 2010.
  • Performance-based tiers released in 2014.
  • Elastic Pools released in 2015.
  • Azure SQL Data Warehouse released in 2016.
  • Azure SQL Managed Instance in preview now, H1 2018.
At this point, Managed Instance does not have a set date to be generally available, but it is very likely that the team is trying to make this happen during the second half of this year. So with a new offering in place, let's look at what makes it different, and why it might be the best fit for your SQL Server workload going forward. Why Managed Instance? From its inception, Azure SQL Database has focused on the database as the unit of deployment, security and performance. While this fits well with new developments or SaaS providers, it wasn't always a good fit for on-premises customers. Many existing SQL Server clients are used to having multiple databases per instance and being able to work with them on a low isolation environment. This means moving data back and forth, using three-part names, cross-database security, etc. For these clients, there is too much friction to migrate to the existing database-centric offering. This is the gap that Managed Instance is coming to fill. It will provide the instance as the fundamental unit and allow this close relationship between the databases that live inside of it. It will be a better fit for many existing SQL Server clients but it will also open up new scenarios that are not available on the single database Azure SQL Database offering. For example, you will be able to place a managed instance directly into a VNET, backup and restore from binary SQL Server backups and use features not allowed on the single database model like SQLCLR, linked servers, etc. Compatibility As I mentioned, backups can be restored into Managed Instance and they can be as old as SQL Server 2005 backups. However, once you are on Managed Instance, if you back up your database, you will get a backup that is for the latest SQL Server version; you won't be able to simply back up back to an old version. The actual database compatibility models are the same as what Azure SQL Database offers, which is from 100 (SQL Server 2008) to 140 (SQL Server 2017). In regards to the other features, there are some limits based on the fact that this is a managed service. For example, linked servers will only be to SQL Server or Azure SQL databases since you can't install third-party drivers on the underlying system. Similarly, SQL Agent currently only allows T-SQL jobs. Something like a command line job would provide direct access to the underlying operating system and thus could interfere with the operation of the service. Pricing model The pricing model for Managed Instance is split into four components:
  1. Cores: the amount of cores will also dictate how much RAM you get.
  2. Data Storage: the first 32GB are included, then you pay in 32GB increments.
  3. Backup Storage: the amount in GB of your compressed backups.
  4. IO Rate: flat rate per 1 million IOPS.
For clients that have an existing SQL Server investment with Software Assurance, Microsoft is providing the Azure Hybrid Benefit. This means you can migrate your SQL Server licenses into Managed Instance and get a large discount off the sticker price (about 40%). There are also some opt-in features that have to be paid for separately, like the ability to have active geo-replication (for DR) and security monitoring with Azure Threat Detection. During the preview, the DR options are not live yet but we can expect those when the service becomes generally available. Hardware specifications In terms of technical specifications, Managed Instance is offered on two hardware generations: Gen4 and Gen5. Gen 4 CPUs are based on Intel E5-2673 v3 (Haswell) 2.4 GHz processors and 1 vCore maps to 1 physical core. Gen 5 CPUs are based on Intel E5-2673 v4 (Broadwell) 2.3 GHz processors and 1 vCore maps to 1 logical core (HT thread). The higher core counts (from 32 all the way to 80) are only available on Gen5 and also Gen5 has NVMe SSD storage. There is a drawback that Gen5 maps 5.5 GB of RAM per core versus 7GB of RAM per core for Gen4. Service tiers The service is offered in two different tiers: General Purpose and Business Critical. Both of them are okay for production use and choosing one over the other depends on performance, RTO and RPO requirements. For Tier 3 - Tier 2 workloads, General Purpose could be enough, for Tier 1 mission-critical workloads, definitely go with Business Critical. Business Critical supports higher IOPS with less latency by using local NVMe storage instead of remote Premium Storage. Business Critical also offers faster failover capabilities built on top of Availability Groups, with three replicas including one for read-only workloads. The final big distinction is that Business Critical is required if you use the In-Memory OLTP feature. Demo Okay, now that we have a good understanding of Managed Instance, let's run a demo of creating your Managed Instance, connecting to it and extracting cores and memory information. Check it out on the video below, cheers! Azure SQL Managed Instance – SQL On The Edge Episode 17
Topics: Microsoft Azure Technical Track

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.