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)
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 instance 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.
More info:
Choose the right deployment option in Azure SQL
Azure SQL Database Managed Instance
