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 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
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.
Reading the article, I found the following statement to be quite interesting: "…that server-level objects are replicated to the failover instance…"
As typically features seem to get rolled out to Azure first, then (sometimes) pushed out to on-premises SQL Server, if this follows that pattern it's going to make a lot of people using replication very happy.
The killer constraint: "…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."
Tempting, but "No!" I need my DB out of Production to re-create issues/trial upgrades/even index-tune. If this restriction stays in place, Managed Instances, for this one reason alone, is an absolute no go. (2.4TB of data exports!? Seriously? From a thousand tables?)
Sadness propounds.
But a lot of those things just aren't the same on-prem, especially since it will be impossible for you to ever have full engine parity or even underlying hardware equivalence. Wouldn't you be better off using a separate Managed Instance for those kinds of things? Not only does it allow you to have better apples-to-apples comparisons, but it also significantly reduces or eliminates altogether any egress costs (I don't know exactly how you would be charged for backups that you then have to move on-prem).
If two-way migration is a need for you, then yeah, Managed Instances aren't for you and, like Azure SQL DB, they won't be for everyone. Maybe what you want for this specific use case is a standard Azure VM and BYOL.
"standard Azure VM and BYOL" – which is where we are at today. With a DBA "Team" of one, and a growing portfolio of regular Azure VMs and our own licenses, but with our own hardware for "ultimate performance testing" for Tier-1 on-prem customers, we need the "problem-child" databases in-office to tune on top-shelf hardware, which is what the on-prem installations will be.
Agreed this isn't at all apples-to-apples, but that's not why we use Azure.
My hope was to have a better hosting experience, with lowered DBA-effort (and costs), while being able to bring home the pick of the crop to tune for each business module (no single DB as yet houses all of our software modules, so there are inevitably multiple databases we would need to pull back to optimally tune because those reflect reality, rather than our dreamed-up, somewhat-real, test cases.)
Make more sense? Sorry for not outlining the desired criteria initially.
And, yes, the cost of pulling a 90GB Red Gate SQL Backup of a 2.4TB database is possibly a daunting/prohibitive cost that's an unknown… Thanks, sincerely, for that reminder of forgotten costs with Azure.
With it built on the Azure SQL DB platform, I would hope we still have the same ability to 'copy' a database in Managed Instances. I'll be researching this soon. If so, to do the one off testing you are mentioning, simply create an exact copy of the database, do your validation/testing/research, and then delete the copy. That is what I currently do for Azure SQL Database, there is zero reason to pull the data back down to on-premises unless you are migrating off the platform for that type of testing.
Our use case is due to deployment options already a reality: on-prem, Azure VM+SQL, and a.n.other Cloud Host on iron. Coupled with Azure VM capacity limits, this mix must continue. Azure is not our default/required platform – as you can see, it's one of 3, and is usually choice #2 or #3.
Not all real-world databases exist on-prem or in-host – they very well may reside in Azure because that's what the original Customer required. To certify performance of a module for an on-prem or in-host customer that is either a new deployment or upgrade, or has informed us of significant volume increases, we have to go-to-the-iron, which is our SQL 2017, Storage-Appliance, dedicated performance-testing server. Thus, pulling from Azure is a need, and as no Azure VM to-date has been able, even with premium disks, to match our processing and IO needs, a parallel Managed Instance isn't a viable choice, as there isn't an equivalent VM to our iron to be had.
That leaves us with what Aaron finished with originally… "standard Azure VM and BYOL"
Thanks for contributing feedback @Tim and @Aaron. Much appreciated. Should the one-way restriction ever see a demise, Managed Instances would become attractive, especially from a SQL bits maintenance perspective. (If Red Gate's SQL Backup were the backup handler for these MI's, so much the better.)