Azure SQL Database Managed Instance – the Good, the Bad, the Ugly

/0 Comments/in /by

Azure SQL Database Managed Instance is available for public preview (for pricing see this page). It will play a very important role in the SQL Server cloud PaaS derivatives, which are now four:

  • Azure SQL Database – A database-scoped service available in Basic, Standard, and Premium performance tiers.
  • Azure SQL Data Warehouse – A scalable farm of Azure SQL databases that has roots in Microsoft Analytics Platform System (APS).
  • Elastic Pools — A pool of Azure SQL databases that share the same resources and it’s particularly useful for multi-tenant scenarios.
  • (NEW) SQL Database Managed Instance – Similar to an on-premises SQL Server, SQL Database Managed Instance allows you to host multiple database under the same instance.

A customer used SQL Database Managed Instance while it was in private preview and I followed its evolution closely. Here are few notes:

The Good

The good is in the name. It’s common to split your data in multiple databases. In the BI world, we typically have a staging database and a DW database, but you can also have MDS, SSISDB database, and others. And it’s much easier and faster to reference directly objects in another database instead of exporting and staging the data. Now, instead of isolated cloud databases, SQL Database Managed Instance can host multiple databases, just like your on-premises SQL Server. This allows you to “lift” your on-premises databases and shift them to the cloud if this makes sense. Further, there is almost a full feature parity between SQL Server and SQL Database Managed Instance as far as the Database Engine is concerned. Even better, SQL Agent is here so you can schedule jobs! Although not all job types as you’ll find in a moment, sigh…

The Bad

SQL Database Managed Instance is all about the Database Engine. It doesn’t support the BI offerings – SSAS, SSRS, SSIS, MDS. SSAS cloud migration requires deploying to Azure Analysis Services. SSRS needs to go a virtual machine but the Report Server database can be hosted in the managed instance. SSIS to Azure Data Factory (ADF) or a VM (the SSISDB catalog can be hosted in the managed instance). MDS need to go to a VM (Enterprise Edition alert!). All these paths require additional deployments and additional pricing. So, if you’re on SQL Server Standard Edition on premises, don’t expect your bill to remain the same. In general, speaking of pricing, don’t expect any cloud migration to cost less. It might, or it might not. Do your homework. My experience shows that cloud deployments are for convenience and make sense when data is in the cloud, and not that much for cost cutting. No vendor will let cloud offerings cannibalize its on-premises counterparts.

The Ugly

The SSIS story and the whole Microsoft cloud ETL story for that matter deserves the ugly award. I don’t know why Microsoft added ADF given that thousands of ETL developers use SSIS. To “lift and shift” your SSIS-based ETL you need to configure ADF to host the SSIS runtime. This scenario is still in preview and it doesn’t have UI (you need to use PowerShell for the configuration part). I failed to get an answer from Microsoft what engineering predicament precludes spinning the ISServerExec process to host the SSIS runtime directly in the managed instance so that we can lift and shift SSIS jobs and leave them still running under SQL Agent. I wonder how many customers would continue deploying SSIS to a VM, bypassing ADF and its idiosyncrasies altogether.

Conclusion

Azure SQL Database Managed Instance is a great addition the SQL Server cloud family. Azure SQL Database Managed Instance will remove developer’s pain in the cloud by allowing you to host multiple databases under the same instance. However, “lift and shift” scenarios need to be carefully evaluated, especially for existing on-premises BI solutions.