SQL Database MI benefits
As I see a huge number of customers migrating their on-prem databases to the Azure cloud, the main question they ask is about whether they should go with an IaaS solution (SQL Server in a VM) or a PaaS solution (SQL Database). Because SQL Database MI (Managed Instance) has near 100% compatibility with on-premise SQL Server (supports SQL Agent, VNET, cross-database queries, CLR, replication, CDC, Service Broker, see Azure SQL Database Features), I’m seeing a large majority of them go with MI.
The only reasons to go with IaaS:
- You need control over / access to the operating system
- You need full control over the database engine. You can choose when to start maintenance/patching, change the recovery model to simple or bulk-logged, pause or start the service when needed, and you can fully customize the SQL Server database engine (but with this additional control comes the added responsibility to manage the virtual machine)
- You have to run an app or agent side-by-side with the database
- You need one of the few features MI does not support such as Filestream, Filetable, or linked servers to non SQL Server (see Azure SQL Database Features). You can use Database Migration Assistant to see if there is anything in your database that is not supported. So if you do have a database that has a feature that is not supported, land that database in a VM and place the others in MI
- You have a 3rd-party database that the vendor has not tested on MI. Even though it may work fine, the vendor would not provide support until they give it a stamp of approval
- Performance (80 cores is the max supported by MI) or storage (MI supports a database max of 8TB)
- Cost (out of scope for this blog), but check out Cost savings of the cloud
Below I’ll list the major benefits of an PaaS solution over IaaS:
- No VM’s (virtual machines). How great is that! Never have to remote into a server anymore or manage it in any way. Everything is done via the Azure portal via tools like SSMS. Of course there is a VM somewhere that hosts the databases, but you are completely obfuscated from that
- No patching or upgrading. I was a DBA for many years, and one of the biggest pains was having to patch or upgrade SQL Server, OS, drivers, etc. Having to setup a test environment, then knocking people off the servers on a weekend to upgrade and hoping not to run into any problems. Those days are gone! MI is patched with no downtime (see Hot patching SQL Server Engine in Azure SQL Database)
- You get database backups out of the box. As soon as you create a database, Azure automatically starts backing up the database and you can do a point-in-time restore and even restore a deleted database. No more using SQL Agent or a 3rd-party product to setup and monitor the backups
- Simplified disaster recovery (DR). Availability Groups are a pain to setup and monitor. With MI, it has auto-failover groups that basically just requires you to click an area of the country that you want the DR to be and Azure takes care of setting it up and making sure it keeps working
- You get the latest version of SQL server. Think of it as SQL Server 2019+. New features are added to MI every few weeks – you can choose to use them or not. Every couple of years those features will be gathered up and added to the boxed version, but you get them right away with MI. Database compatibility level exists so you code won’t break when there is an upgrade
- Built-in Advanced threat detection that detects anomalous activities indicating unusual and potentially harmful attempts to access or exploit databases
- Built-in Vulnerability Assessment that helps you discover, track, and help you remediate potential database vulnerabilities
- Built-in Data Discovery & Classification that provides advanced capabilities built into Azure SQL Database for discovering, classifying, labeling & reporting the sensitive data in your databases
Migrating you database can be as simple as backing up your on-prem database, copying the .bak to Azure storage, and restoring to an SQL Database MI database. Check out Database Migration Service (DMS) for help in the migration. Also check out the Azure Database Migration Guide and Get help choosing the right Azure SQL database service.
More info:
Do you have a reference for controlling whether or not new features are used? We have a scenario where we will need to backup an MI to a VM, and as far as we can tell the MI will always be on a later version of SQL Server than the VM, so the backup/restore process won’t work.
There is not way to control whether new features are used. Can you explain the use case where you need to back up a MI to a VM?
Well one use case would be if I wanted to get off MI.
In our case we have a dev server stood up in IAAS for running conversions. It looks like we will have to rebuild as an MI, almost as soon as we built it, and I’m trying to understand our options.
I’m just trying to understand your comment “New features are added to MI every few weeks – you can choose to use them or not. “. I suppose you don’t mean that you can choose whether or not they happen, just choose whether or not you want to use the new features. I thought you meant the former.
Yes, I mean you can choose to use the new feature or not, but can’t prevent them from happening. Can you clarify the use case that would make you want to get off MI? I just have not seen customers who decide to go back to a VM after using MI, so would like to hear your possible reasons. Thanks!
I have no reason to do it, it just seems to me to be the most likely use case. MI seems to lock you in, and not everyone wants that.
Another use case would be restoring your MI to a dev environment which isn’t MI.
I did read of a Microsoft tool called bacpac, but I haven’t read up on it.
If you have a Pluralsight license, John Savill talks in decent detail about very similar topics. I forget which course it is.
Oliver, is your use case best stated as a desire to use IaaS for an an anlytical development sandbox for experimentation….using the existing prod structures as a starting point……with the obvious cautionary tale of balancing needs of decreased red tape vs “dev” inadvertently becoming “experimental production”?
In my case, I’m doing database conversions, and because one database is on MI, they must all be on MI. So choice is limited if it’s difficult to get off MI. I wanted to understand the alternatives. And it struck me that it’s hard to get off MI if you wanted/needed to. Our development machine will have to be MI, for instance. Nothing to do with experimental just reduced choices.
It would seem that bacpac will output a schema and some bcp files in a structure, and you can use that to import. So that overcomes the backup issue. I’m told by the DBA it’s slow, but we haven’t tested it.
What about the pricing?
is the overall cost for azure MI comparable to on-prem SQL, or far higher?