Migration overview: SQL Server to Azure SQL Managed Instance

Applies to: Azure SQL Managed Instance

Learn about the options and considerations for migrating your SQL Server databases to Azure SQL Managed Instance.

You can migrate SQL Server databases running on-premises or on:

• SQL Server on Virtual Machines
• Amazon EC2 (Elastic Compute Cloud)
• Amazon RDS (Relational Database Service) for SQL Server
• Google Compute Engine
• Cloud SQL for SQL Server - GCP (Google Cloud Platform)

For other migration guides, see Database Migration.

Overview

Azure SQL Managed Instance is a recommended target option for SQL Server workloads that require a fully managed service without having to manage virtual machines or their operating systems. SQL Managed Instance enables you to move your on-premises applications to Azure with minimal application or database changes. It offers complete isolation of your instances with native virtual network support.

Be sure to review the SQL Server database engine features available in Azure SQL Managed Instance to validate the supportability of your migration target.

Considerations

The key factors to consider when you're evaluating migration options are:

• Number of servers and databases
• Size of databases
• Acceptable business downtime during the migration process

One of the key benefits of migrating your SQL Server databases to SQL Managed Instance is that you can choose to migrate the entire instance or just a subset of individual databases. Carefully plan to include the following in your migration process:

• All databases that need to be colocated to the same instance
• Instance-level objects required for your application, including logins, credentials, SQL Agent jobs and operators, and server-level triggers

Choose an appropriate target

You can use the Azure SQL migration extension for Azure Data Studio to get right-sized Azure SQL Managed Instance recommendation. The extension collects performance data from your source SQL Server instance to provide right-sized Azure recommendation that meets your workload's performance needs with minimal cost. To learn more, see Get right-sized Azure recommendation for your on-premises SQL Server database(s)

The following general guidelines can help you choose the right service tier and characteristics of SQL Managed Instance to help match your performance baseline:

• Use the CPU usage baseline to provision a managed instance that matches the number of cores that your instance of SQL Server uses. It might be necessary to scale resources to match the hardware configuration characteristics.
• Use the memory usage baseline to choose a vCore option that appropriately matches your memory allocation.
• Use the baseline I/O latency of the file subsystem to choose between the General Purpose (latency greater than 5 ms) and Business Critical (latency less than 3 ms) service tiers.
• Use the baseline throughput to preallocate the size of the data and log files to achieve expected I/O performance.

You can choose compute and storage resources during deployment and then change them afterward by using the Azure portal, without incurring downtime for your application.

Another key consideration in the selection of the target service tier in Azure SQL Managed Instance (General Purpose versus Business Critical) is the availability of certain features, like In-Memory OLTP, that are available only in the Business Critical tier.

SQL Server VM alternative

Your business might have requirements that make SQL Server on Azure Virtual Machines a more suitable target than Azure SQL Managed Instance.

If one of the following conditions applies to your business, consider moving to a SQL Server virtual machine (VM) instead:

• You require direct access to the operating system or file system, such as to install third-party or custom agents on the same virtual machine with SQL Server.
• You have strict dependency on features that are still not supported, such as FileStream/FileTable, PolyBase, and cross-instance transactions.
• You need to stay at a specific version of SQL Server (2012, for example).
• Your compute requirements are much lower than a managed instance offers (one vCore, for example), and database consolidation is not an acceptable option.

Migration tools

We recommend the following migration tools:

The following table lists alternative migration tools:

Compare migration options

Compare migration options to choose the path that's appropriate to your business needs.

The following table compares the recommended migration options:

The following table compares the alternative migration options:

Feature interoperability

There are more considerations when you're migrating workloads that rely on other SQL Server features.

SQL Server Integration Services

Migrate SQL Server Integration Services (SSIS) packages and projects in SSISDB to Azure SQL Managed Instance by using Azure Database Migration Service.

Only SSIS packages in SSISDB starting with SQL Server 2012 are supported for migration. Convert older SSIS packages before migration. See the project conversion tutorial to learn more.

SQL Server Reporting Services

You can migrate SQL Server Reporting Services (SSRS) reports to paginated reports in Power BI. Use the RDL Migration Tool to help prepare and migrate your reports. Microsoft developed this tool to help customers migrate Report Definition Language (RDL) reports from their SSRS servers to Power BI. It's available on GitHub, and it documents an end-to-end walkthrough of the migration scenario.

SQL Server Analysis Services

SQL Server Analysis Services tabular models from SQL Server 2012 and later can be migrated to Azure Analysis Services, which is a platform as a service (PaaS) deployment model for the Analysis Services tabular model in Azure. You can learn more about migrating on-premises models to Azure Analysis Services in this video tutorial.

Alternatively, you can consider migrating your on-premises Analysis Services tabular models to Power BI Premium by using the new XMLA read/write endpoints.

High availability

The SQL Server high-availability features Always On failover cluster instances and Always On availability groups become obsolete on the target SQL managed instance. High-availability architecture is already built into both General Purpose (standard availability model) and Business Critical (premium availability model) service tiers for SQL Managed Instance. The premium availability model also provides read scale-out that allows connecting into one of the secondary nodes for read-only purposes.

Beyond the high-availability architecture that's included in SQL Managed Instance, the failover groups feature allows you to manage the replication and failover of databases in a managed instance to another region.

SQL Agent jobs

Use the offline Azure Database Migration Service option to migrate SQL Agent jobs. Otherwise, script the jobs in Transact-SQL (T-SQL) by using SQL Server Management Studio and then manually re-create them on the target SQL managed instance.

Logins and groups

Move SQL logins from the SQL Server source to Azure SQL Managed Instance by using Database Migration Service in offline mode. Use the Select logins pane in the Migration Wizard to migrate logins to your target SQL managed instance.

By default, Azure Database Migration Service supports migrating only SQL logins. However, you can enable the migration of Windows logins by:

• Ensuring that the target SQL managed instance has read access to Microsoft Entra ID (formerly Azure Active Directory). A user who has the Global Administrator role can configure that access via the Azure portal.
• Configuring Azure Database Migration Service to enable Windows user or group login migrations. You set this up via the Azure portal, on the Configuration page. After you enable this setting, restart the service for the changes to take effect.

After you restart the service, Windows user or group logins appear in the list of logins available for migration. For any Windows user or group logins that you migrate, you're prompted to provide the associated domain name. Service user accounts (accounts with the domain name NT AUTHORITY) and virtual user accounts (accounts with the domain name NT SERVICE) aren't supported. To learn more, see How to migrate Windows users and groups in a SQL Server instance to Azure SQL Managed Instance using T-SQL.

Alternatively, you can use the PowerShell utility specially designed by Microsoft data migration architects. The utility uses PowerShell to create a T-SQL script to re-create logins and select database users from the source to the target.

The PowerShell utility automatically maps Windows Server Active Directory accounts to Microsoft Entra accounts, and it can do a UPN lookup for each login against the source Active Directory instance. The utility scripts custom server and database roles, along with role membership and user permissions. Contained databases aren't yet supported, and only a subset of possible SQL Server permissions is scripted.

Encryption

When you're migrating databases protected by Transparent Data Encryption to a managed instance by using the native restore option, migrate the corresponding certificate from the source SQL Server instance to the target SQL managed instance before database restore.

System databases

Restore of system databases isn't supported. To migrate instance-level objects (stored in the master and msdb databases), script them by using T-SQL and then re-create them on the target managed instance.

In-Memory OLTP (memory-optimized tables)

SQL Server provides an In-Memory OLTP capability. It allows usage of memory-optimized tables, memory-optimized table types, and natively compiled SQL modules to run workloads that have high-throughput and low-latency requirements for transactional processing.

If you have memory-optimized tables or memory-optimized table types in your on-premises SQL Server instance and you want to migrate to Azure SQL Managed Instance, you should either:

• Choose the Business Critical tier for your target SQL managed instance that supports In-Memory OLTP.

• If you want to migrate to the General Purpose tier in Azure SQL Managed Instance, remove memory-optimized tables, memory-optimized table types, and natively compiled SQL modules that interact with memory-optimized objects before migrating your databases. You can use the following T-SQL query to identify all objects that need to be removed before migration to the General Purpose tier:

  SELECT * FROM sys.tables WHERE is_memory_optimized=1
  SELECT * FROM sys.table_types WHERE is_memory_optimized=1
  SELECT * FROM sys.sql_modules WHERE uses_native_compilation=1
  

To learn more about in-memory technologies, see Optimize performance by using in-memory technologies in Azure SQL Managed Instance.

Advanced features

Be sure to take advantage of the advanced cloud-based features in SQL Managed Instance. For example, you don't need to worry about managing backups because the service does it for you. You can restore to any point in time within the retention period. Additionally, you don't need to worry about setting up high availability, because high availability is built in.

To strengthen security, consider using Microsoft Entra authentication, auditing, threat detection, row-level security, and dynamic data masking.

In addition to advanced management and security features, SQL Managed Instance provides advanced tools that can help you monitor and tune your workload. Azure SQL Analytics allows you to monitor a large set of managed instances in a centralized way. Automatic tuning in managed instances continuously monitors performance of your SQL plan execution and automatically fixes the identified performance problems.

Some features are available only after the database compatibility level is changed to the latest compatibility level (150).

Migration assets

For more assistance, see the following resources that were developed for real-world migration projects.

The Data SQL Engineering team developed these resources. This team's core charter is to unblock and accelerate complex modernization for data platform migration projects to Microsoft's Azure data platform.

Next steps

• To start migrating your SQL Server databases to Azure SQL Managed Instance, see the SQL Server to Azure SQL Managed Instance migration guide.

• For a matrix of services and tools that can help you with database and data migration scenarios as well as specialty tasks, see Services and tools for data migration.

• To learn more about Azure SQL Managed Instance, see:

  • Service tiers in Azure SQL Managed Instance
  • Differences between SQL Server and Azure SQL Managed Instance
  • Azure Total Cost of Ownership Calculator

• To learn more about the framework and adoption cycle for cloud migrations, see:

  • Cloud Adoption Framework for Azure
  • Best practices for costing and sizing workloads migrated to Azure

• To assess the application access layer, see Data Access Migration Toolkit (Preview).

• For details on how to perform A/B testing at the data access layer, see Database Experimentation Assistant.