In our previous blog SQL Database Managed Instance, we saw an overview of Managed Instance of SQL database.
In this article, we compare Azure SQL database and SQL Database Managed Instance and walk you through database migration.
The following table outlines key differences and envisioned usage scenarios between SQL IaaS, Azure SQL Database, and SQL Database Managed Instance:
|SQL Database Managed Instance||
|Azure SQL Database (single or pool)||Elastic pools: For customers developing new SaaS multi-tenant applications or intentionally transforming their existing on-premises apps into a SaaS multitenant app, propose elastic pools. Benefits of this model are:
Single databases: For customers developing new apps other than SaaS multi-tenant, whose workload is stable and predictable, propose single databases. Benefits of this model are:
|SQL IaaS virtual machine||For customers needing to customize the operating system or the database server, as well as customers having specific requirements in terms of running third-party apps by side with SQL Server (on the same VM), propose SQL VMs / IaaS as the optimal solution|
Managed Instance supports several database migration options as follows.
- Data Migration Service (DMS)
The Azure Database Migration Service is a fully managed service designed to enable seamless migrations from multiple database sources to Azure Data platforms with minimal downtime. This service streamlines the tasks required to move existing third party and SQL Server databases to Azure. Deployment options include Azure SQL Database, Managed Instance, and SQL Server in Azure VM at Public Preview. For step-by-step explanation about how to migrate your on-premises database to Managed Instance using DMS, please visit this link.
- Backup and restore
The migration option leverages SQL backups to Azure blob storage. These backups can be directly restored into Managed Instance. For this, you can:
- Use Data Migration Service
- Use T-SQL Restore command. Follow this link which shows how to upload a backup file and secure it using Shared Access Signature (SAS) key.
- Import from a BACPAC file. For more detail, visit this link.
Key Differences between SQL Server on-premises and Managed Instance
- Managed Instance are always up-to-date in the cloud, while some features in on-premises SQL Server may be obsolete.
- Automated backups and point in time restore. Customer can initiate copy-only backups that do not interfere with automatic backup chain.
- Managed Instance does not allow specifying full physical paths so all corresponding scenarios have to be supported differently.
- Managed Instance supports Azure AD authentication as cloud alternative to Windows authentication.
- Managed Instance automatically manages XTP filegroup and files for databases containing In-Memory OLTP objects.