Azure SQL Database Managed Instance (Part 2 – Migration)

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:

	Usage scenario
SQL Database Managed Instance	For customers looking to migrate a large number of apps from on-premises or IaaS, self-built, or ISV provided, with as low migration effort as possible, propose Managed Instance. This can be done using fully automated Data Migration Service. With Software Assurance, you can exchange their existing licenses for discounted rates on a SQL Database Managed Instance using the Azure Hybrid Use Benefit for SQL Server. It is the best migration destination in the cloud for SQL Server instances that require high security and a rich programmability surface.
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: Conversion of the business model from selling licenses to selling service subscriptions (for ISVs) Easy and bullet-proof tenant isolation A simplified database-centric programming model The potential to scale out without hitting a hard ceiling 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: A simplified database-centric programming model Predictable performance for each database
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

Database Migration

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:

1. Use Data Migration Service

1. 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.
2. 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.