Tim Radney

Bridging the Azure gap : Managed Instances

February 5, 2018 by in Azure | 6 Comments
Pragmatic Works Software is now part of SentryOne
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Register to Download

Featured Author

Paul White is an independent SQL Server consultant specializing in performance tuning, execution plans, and the query optimizer.

Paul’s Posts

If you’ve been considering moving your SQL Server environment to Azure, you’ve only had a couple of options. First you could utilize the PaaS solution of Azure SQL Database and move a single database or use an elastic pool. Your other option has been an IaaS solution using Azure Virtual machines running Microsoft SQL Server. We will soon have a third option called a SQL Database Managed Instance.

Managed Instances bridge the gap between on-prem SQL Server and Azure SQL Database
Managed Instances bridge the gap between on-prem SQL Server and Azure SQL Database

Managed Instances were first introduced at the Microsoft Build conference in Spring 2017, and so far the preview has been limited to a small number of customers and consultants. Managed Instances can be considered a hybrid between a full version of SQL Server and Azure SQL Database. Single and elastic databases are built upon a database-scoped programming model and Managed Instances are built on an instance-scoped programming model. This makes Managed Instances more compatible with on-premises SQL Server.

Managed Instances provide much more of a feel of an on-premises SQL Server, yet are built on the same infrastructure as Azure SQL Database. What sets it apart from Azure SQL Database is that it presents an entire SQL Server instance to the customer. In Azure SQL Database, you configure a server, which is really a container, and then can have multiple databases in that server, but they can’t easily talk to one another. With Managed Instances, all databases within the instance are on the same actual SQL Server, so you have full support for cross-database queries. This is a huge feature for many applications that otherwise were not a good fit for Azure SQL Database, and I think that’s going to allow many more SQL Server applications to move into the cloud.

Functionality that is instance-level is now supported. This includes things like global temp tables, SQL Server Agent, Service Broker, replication, SQL Audit, and Common Language Runtime (CLR). Managed instances can also support databases up to 35TB in size. Currently the largest capacity for an Azure SQL Database is 4TB in the top tier. I suspect this may change soon, and again, will open up the cloud for more applications.

Managed Instances also get to take advantage of all the features of the PaaS platform, to include automatic backups, threat detection, vulnerability assessments, high availability, geo-replication, database advisor, and much more. I’ve seen a presentation that was discussing how the automatic failover process works and learned that server-level objects are replicated to the failover instance. This means that things like logins and jobs – pain points for many of our environments today – are handled for you.

Over the past year, I have helped numerous customers migrate to Azure SQL Database, and one of the primary challenges is migrating the data. You can’t just perform a SQL Server backup and restore to Azure SQL Database. I was very pleased to hear that with SQL Database Managed Instances, we can use native SQL Server backups and restore to Managed Instances, however, you must use the backup to URL mechanism. This will make migrations to Managed Instances much easier, however, since Managed Instances are built on Azure SQL Database, this is a one-way ticket: You can’t back up your Managed Instance databases and restore back to on-premises. If you ever decided to bring your database back to on-premises or out of Managed Instances, you will have to export your data.

Databases on Managed Instances are much more ready to migrate to Azure SQL Database
Databases on Managed Instances are much more ready to migrate to Azure SQL Database

On the other hand, since they are built on the Azure SQL Database platform, the individual databases you put into a Managed Instance can be migrated to their own individual Azure SQL Databases. This makes a Managed Instance a perfect stepping stone, where you can work out the isolation complications that prevent you from migrating directly to PaaS.

I am curious about replication being supported. I haven’t been able to find out yet if a Managed Instance database can be a publisher or whether it can only be a subscriber, like an Azure SQL Database. If it can be a publisher, then that could be an effective way of migrating back to on-premises. I am really hoping that in the near future, we will have the ability to also restore native SQL Server backups to singleton Azure SQL Databases. It appears the technology is there, it just needs to be expanded for the existing PaaS environment.

Another interesting observation about Managed Instances is that since the technology is built on the Azure SQL Database model, the SQL Server version will follow that of Azure SQL Database. This might complicate things with vendor support. Many vendors will state that they certify their product on SQL Server version X. Even though Managed Instances will support almost all the features of SQL Server 2017, it will not use the same build version, so programmatic version checks will be complicated. Your best course of action here is to push back on the vendor, since Microsoft is unlikely to waver on this stance, and I have no doubt some of those conversations will be challenging.

Will vendors go through the effort of certifying their products on Managed Instances, or will this become an issue like we experienced with virtualization? In the early days of virtualization, many vendors stated they didn’t support their products running virtualized, however Microsoft fully supported Windows X and SQL Server X being virtualized. Hopefully we will see vendors getting on board and certifying their products on Managed Instances. I certainly see some SQL Server pioneers out there that will move to Managed Instances after their own testing.

Any time a customer is looking to migrate to the cloud, security is a big concern. Managed Instances offer VNET support with private IP addresses and VPN to on-premises networks. This can allow a client to protect their environment from the public internet and have full isolation.

I am excited about Managed Instances and really can’t wait until it is more widely available. For clients that would like a managed environment, but need a more feature-rich solution than a singleton or elastic Azure SQL Database, I feel that Managed Instances would be the perfect fit. There has been a gap between Azure SQL Database and SQL Server on an Azure VM, as many customers need more than Azure SQL Database offers, but SQL Server on an Azure VM is still more maintenance and responsibility than they wanted. Managed Instances really bridge that gap. They support much larger database, allows for easier data migrations, allow for cross-database queries, and shouldn’t require any code changes since the platform is so highly compatible with on-premises SQL Server.

In summary, if your organization is considering a move to a hosted environment within the Azure SQL Database platform, you will be able to choose between individual Azure SQL Databases, elastic pools, or Managed Instances. Depending on your application needs, one of these solutions should be a great fit. Otherwise, you also have the option of running a traditional SQL Server instance on an Azure virtual machine, which offers nice features like managed backups, geo-replication, Azure Site Recovery, and so much more. Microsoft continues to invest in the Azure platform by delivering new products and features that their customers need, and the upcoming release of Managed Instances is continued proof of that focus. Stay tuned, as we’ve been promised a public preview in the very near future.