Migrate SQL Server workloads (FAQ)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL Server on Azure VM

Migrating on-premises SQL Server workloads and associated applications to the cloud usually brings a wide range of questions that go beyond mere product feature information.

This article provides a holistic view and helps understand how to fully unlock the value when migrating to Azure SQL. The Modernize applications and SQL section covers questions about Azure SQL in general as well as common application and SQL modernization scenarios. The Business and technical evaluation section covers cost saving, licensing, minimizing migration risk, business continuity, security, workloads and architecture, performance and similar business and technical evaluation questions. The last section covers the actual Migration and modernization process, including guidance on migration tools.

Modernize applications and SQL

Azure SQL

What are the benefits of moving applications and SQL Server workloads to Azure?

A migration to Azure brings optimized costs, flexibility and scalability, enhanced security, compliance, improved business continuity, and simplified management and monitoring.

What is Azure SQL?

Azure SQL is a family of services that use the SQL Server database engine in the Azure Cloud. The following services belong to Azure SQL: Azure SQL Database (SQL Database), Azure SQL Managed Instance (SQL Managed Instance), and SQL Server on Azure VMs (SQL VMs).

What is the difference between migration and modernization to Azure SQL?

Migration to Azure SQL involves moving applications, infrastructure, and data from one location (for example, a company's on-premises datacenter) to Azure infrastructure. For SQL Server customers, this means migrating your workloads while minimizing impact to operations. You can reduce IT costs, enhance security and resilience, and achieve on-demand scale.

Modernization to Azure SQL involves updating existing applications for newer computing approaches and application frameworks and use of cloud-native technologies. This can be achieved by using PaaS services such as Azure SQL Database and Azure SQL Managed Instance, which provides extra benefits of app innovation, agility, developer velocity, and cost optimization.

What does IaaS and PaaS mean?

Infrastructure as a service (IaaS) is a type of cloud computing service that offers essential compute, storage, and networking resources on demand.

Platform as a service (PaaS) is a complete development and deployment environment in the cloud, with resources that enable you to deliver everything from simple cloud-based apps to sophisticated, cloud-enabled enterprise applications.

PaaS provides other advantages over IaaS, such as shorter development cycles, extra development capabilities without adding staff, affordable access to sophisticated tools, to mention a few. Azure SQL provides both PaaS (SQL Managed Instance, SQL Database) and IaaS (SQL VM) services.

How do I decide if I should move my SQL Server to a Virtual Machine, SQL Managed Instance or SQL Database?

  • SQL Managed Instance is the right PaaS target to modernize your existing SQL Server applications at scale providing almost all SQL Server features (including instance-level features) while reducing the costs of server and database management.

  • SQL Database is the most appropriate choice when building native cloud applications, as it offers high elasticity and flexibility of choosing between architectural and compute tiers, such as Serverless tier for increased elasticity and Hyperscale service tier for a highly scalable storage and compute resources.

  • If you need full control and customization, including OS access, you can opt for SQL Server on Azure VM. The service comparison provides more details.

How can I reduce costs by moving to Azure SQL?

Moving to Azure brings savings in resource, maintenance, and real estate costs, in addition to the ability to optimize workloads so that they cost less to run. Azure SQL Managed Instance and SQL Database bring all the advantages of PaaS services, providing automated performance tuning, backups, software patching and high-availability, all of which entails enormous effort and cost when performing manually.

For example, SQL Managed Instance and SQL Database (single database and elastic pool) come with built-in HA. Also, Business Critical (SQL Managed Instance) and Premium (SQL Database) tiers provide read-only replicas at no additional cost, while SQL Database Hyperscale tier allows HA and named secondary replicas for read scale-out at no license cost. Additionally, Software Assurance customers can use their on-premises SQL Server license on Azure by applying Azure Hybrid Benefit (AHB).

What is the best licensing path to save costs when moving existing SQL Server workloads to Azure?

Unique to Azure, Azure Hybrid Benefit (AHB) is a licensing benefit that allows you bringing your existing Windows Server and SQL Server licenses with Software Assurance (SA) to Azure. Combined with reservations savings and extended security updates, AHB can bring you up to 55% savings compared to pay-in-advance pricing in Azure SQL.

Applications and SQL modernization scenarios

Scenario 1: Data center move to the cloud: what is the modernization path for applications and SQL Server databases?

Updating an organization's existing apps to a cloud first model can be achieved by using fully managed application and data services including Azure App Service, Azure Spring Apps, Azure SQL Database, Azure SQL Managed Instance, and other PaaS services. Azure Kubernetes Services (AKS) provides a managed container-based approach within Azure. Application and Data Modernization in Azure is achieved through several stages, with the most common scenario examples described within the Cloud Adoption Framework.

Scenario 2: Reducing SQL Server costs: How can I reduce the cost for my existing SQL Server fleet?

Moving to Azure SQL VMs, SQL Managed Instance or SQL Database brings savings in resource, maintenance, and real estate costs. Using your SQL Server on-premises licenses in Azure via Azure Hybrid Benefit, and using constrained vCPU capable Virtual Machines will give you a wide variety of options to build a cost-effective solution.

Also, Business Critical (SQL Managed Instance) and Premium (SQL Database) tiers provide read-only replicas at no additional cost, while SQL Database Hyperscale tier allows HA and named secondary replicas for read scale-out at no license cost.

Scenario 3: Optimize application portfolio: How can I at the same time modernize both my application portfolio and SQL Server instances?

Application and Data Modernization in Azure is achieved through several stages, with the most common scenario examples described within the Cloud Adoption Framework.

Scenario 4: SQL Server end of support: Which options do I have to move to Azure SQL?

Once your SQL Server has reached the end of support stage, you have several modernization options toward Azure SQL. One of the options is to migrate your workload to an Azure SQL Managed Instance, which provides high feature parity with the on-premises SQL Server product. Alternatively, with some additional effort, you can move the workload to Azure SQL Database. These services run on SQL Server evergreen features, effectively granting you "the end of End of Support".

Backward compatibility is provided via compatibility levels and database compatibility level settings. Tools like Data Migration Assistant will help you identify possible incompatibilities.

Whenever a Platform-as-a-Service (PaaS) solution doesn't fit your workload, Azure SQL Virtual Machines provide the possibility to do an as-is migration. By moving to Azure SQL VM, you also receive free extended security patches that can provide significant savings (for example, up to 69% for SQL Server 2012).

Scenario 5: Meeting regulatory compliance: How does Azure SQL help meet regulatory compliance requirements?

Azure Policy has built-in policies that help organizations meet regulatory compliance. Ad hoc and customized policies can also be created. For more information, see Azure Policy Regulatory Compliance controls for Azure SQL Database & SQL Managed Instance.

Getting started, the holistic approach

How to prepare a migration business case?

The Microsoft Cloud Adoption Framework for Azure is a great starting point to help you create and implement the business and technology strategy necessary for your move to Azure.

Do I have to modernize applications and SQL at the same time? What are my options?

No. Feel free to take an iterative approach to modernizing each workload and component.

Can I modernize SQL Server to SQL Managed Instance and just lift and shift my application to a VM?

Yes. You can Connect your application to Azure SQL Managed Instance through different scenarios, including when hosting it on a VM.

Business and technical evaluation

Total cost of ownership, licensing, and benefits

How can I estimate Total Cost of Ownership (TCO) savings when moving to Azure SQL?

Moving to Azure SQL brings significant TCO savings by improving operational efficiency and business agility, as well as eliminating the need for on-premises hardware and software. According to the ESG report on The Economic Value of Migrating On-Premises SQL Server Instances to Azure SQL Solutions, you can save up to 47% when migrating from on-premises to Azure SQL Virtual Machines (IaaS), and up to 64% when migrating to Azure SQL Managed Instance or Azure SQL Database (PaaS).

What is the licensing model for SQL Managed Instance?

SQL Managed Instance licensing follows vCore-based licensing model, where you pay for compute, storage, and backup storage resources. You can choose between several service tiers (General Purpose, Business Critical) and hardware generations. The SQL Managed Instance pricing page provides a full overview of possible SKUs and prices.

What is the licensing model for SQL Database?

SQL Database provides a choice between the vCore-based purchasing model and Database transaction unit purchasing model. You can explore Pricing - Azure SQL Database Single Database and learn about pricing options.

Can I use my on-premises SQL Server license when moving to Azure SQL?

If you own Software Assurance for core-based or qualifying subscription licenses for SQL Server Standard Edition or SQL Server Enterprise Edition, you can use your existing SQL Server license when moving to SQL Managed Instance, SQL Database, or Azure VM by applying Azure Hybrid Benefit (AHB). You can also simultaneously use these licenses both in on-premises and Azure environments (dual use rights) for up to 180 days.

I'm using SQL Server subscription license. Can I use it to move to Azure SQL?

Yes, qualifying subscription licenses can be used to pay Azure SQL services at a reduced (base) rate by applying Azure Hybrid Benefit (AHB).

I'm using SQL Server CAL licenses. How can I move to Azure SQL?

SQL Server CAL licenses with appropriate license mobility rights can be used on Azure SQL VMs, and on Azure SQL Dedicated Host.

What is Azure Hybrid Benefit (AHB)?

Unique to Azure, Azure Hybrid Benefit (AHB) is a licensing benefit that allows you bringing your existing Windows Server and SQL Server licenses with Software Assurance (SA) to Azure. AHB can bring you up to 55% savings compared to pay-in-advance pricing in Azure SQL, when combined with reservations savings and extended security updates.

How do I translate my SQL Server on-premises license to vCore license in SQL Managed Instance, SQL Database, and SQL VM?

For every one (1) core of SQL Server Enterprise Edition, you get four (4) vCores of SQL Managed Instance General Purpose tier or one (1) vCore of SQL Managed Instance Business Critical tier. Similarly, one (1) core of SQL Server Standard Edition translates to one (1) vCore of SQL Managed Instance General Purpose tier, while four (4) vCores of SQL Server Standard Edition translate to one (1) vCore of SQL Managed Instance Business Critical.

The Azure Hybrid Benefit August 2020 update provides an overview of possible core-to-vCore conversions for SQL Managed Instance, SQL Database, and SQL VM. Applicable AHB rights are also described in the Product Terms.

Is Software Assurance (SA) required for using SQL Server license on Azure SQL?

Software Assurance is a licensing program that can be applied to on-premises SQL Server licenses, allowing license mobility, AHB, and other benefits. SA is required if AHB is to be invoked for using existing SQL Server licenses (with SA) when moving to Azure SQL. Without SA + AHB, customers are charged with Pay-In-Advance pricing.

Alternatively, the outsourcing software management terms applicable to SQL Server licenses acquired before October 1, 2019, permit you to allocate your existing licenses to Azure Dedicated Host just as you would license a server in your own data center: see Pricing - Dedicated Host Virtual Machines.

Do I have to pay for high availability (HA) in SQL Managed Instance and SQL Database?

Both General Purpose and Business Critical tiers of SQL Managed Instance and SQL Database are built on top of inherent high availability architecture. This way, there's no extra charge for HA. For SQL Database Hyperscale tier, the HA replica is charged.

Workloads and architecture

How do I determine which SQL Server workloads should be moved to SQL Managed Instance?

When migrating SQL Server workloads to Azure SQL Managed Instance is normally the first option, as most databases are "as-is" ready to migrate to SQL Managed Instance. There are several tools available to help you assess your workload for compatibility with Azure SQL Managed Instance.

You can use the Data Migration Assistant. It provides help to detect issues that can affect the Azure SQL Managed Instance migration and provide guidance on how to resolve them. After verifying compatibility, you can run the SKU recommendation tool to analyze performance data and recommend a minimal Azure SQL Managed Instance SKU. Make sure to visit Azure Migrate which is a centralized hub to assess and migrate on-premises servers, infrastructure, applications, and data to Azure.

How do I determine the appropriate SQL Managed Instance target for a particular SQL Server on-premises workload: SQL Managed Instance General Purpose or Business Critical tier?

SQL Managed Instance tier choice is guided by availability, performance (for example, throughput, IOPS, latency), and feature (for example, in-memory OLTP) requirements. The General Purpose tier is suitable for most generic workloads, as it already provides HA architecture and a fully managed database engine with a storage latency between 5 ms and 10 ms. The Business Critical tier is designed for applications that require low-latency (1-2 ms) responses from the storage layer, fast recovery, strict availability requirements, and the ability to off-load analytics workloads.

How can I move a highly automated SQL Server to SQL Managed Instance?

Infrastructure deployment automation of Azure SQL can be done with PowerShell and CLI. Useful examples can be found in the Azure PowerShell samples for Azure SQL Database and Azure SQL Managed Instance article. You can use Azure DevOps Continuous Integration (CI) and Deployment (CD) Pipelines to fully embed automation within your Infrastructure-as-Code practices.

Building your database models and scripts can also be integrated through Database Projects with Visual Studio Code or Visual Studio. The use of Azure DevOps CI/CD pipelines will enable deployment of your Database Projects to an Azure SQL destination of your choice. Finally, service automation via third party tools is also possible. For more information, see Azure SQL Managed Instance - Terraform command.

Can I move only a specific workload out of an on-premises cluster, and what is the impact to licensing and cost?

It's possible to only migrate the databases related to one workload toward an Azure SQL Managed Instance. Creating and operating an Azure SQL managed instance requires SQL Server licenses. Azure Hybrid Benefit provides you with the ability to reuse your licenses. Reach out to your licensing partner to review what possibilities can be used with license mobility and restructuring your current licenses.

I maintain a highly consolidated SQL Server with multiple applications running against it. Can I move it to SQL Managed Instance?

Similarly as with on-premises SQL Server, you can consolidate and run multiple databases on a single SQL Managed Instance instance, at the same time benefiting from inherent high-availability architecture as well as shared security and management. SQL Managed Instance also supports cross-database queries.

How do I migrate SQL Server Business Intelligence workloads (including Reporting Services and Analysis Services) that aren't compatible with SQL Managed Instance?

The least effort migration path is to move as-is and host the Business Intelligence components on an Azure VM. The Reporting Services database can be hosted on Azure SQL Managed Instance and Azure Data Factory provides the capability to lift and shift SSIS solutions to the cloud. When building a modern solution is part of the migration effort, Azure is providing a wide variety of services to build an Enterprise data warehouse solution.

I'm using an application from an ISV that doesn't support SQL Managed Instance / Azure. What are my options to move my application to Azure and SQL Server to Azure SQL?

Migrating your environment as-is to an Azure Virtual Machine is the safest option when the ISV or vendor isn't providing any options. However, we encourage ISVs and vendors that are working closely with Microsoft to review the options with Azure SQL Managed Instance. Azure SQL Managed Instance provides backward compatibility options through database compatibility level, guidance for Transact-SQL differences and has implemented major features to Azure SQL Managed instance.

How do I keep the compatibility of my current SQL Server database version in SQL Managed Instance?

Database compatibility level can be set in SQL Managed Instance, as described on the Azure SQL Blog.

Security

How does Azure SQL help in enhancing the database security posture?

The security strategy follows the layered defense-in-depth approach: Network security + Access management + Threat protection + Information Protection. You can read more about SQL Database and SQL Managed Instance security capabilities. Azure-wide, Microsoft Defender for Cloud provides a solution for Cloud Security Posture Management (CSPM) and Cloud Workload Protection (CWP).

Business continuity

How can I adapt on-premises business continuity and disaster recovery (BCDR) concepts into Azure SQL Managed Instance concepts?

Most Azure SQL BCDR concepts have an equivalent in on-premises SQL Server implementations. For example, the inherent high availability of SQL Managed Instance General Purpose tier can be seen as a cloud equivalent for SQL Server FCI (Failover Cluster Instance). Similarly, SQL Managed Instance Business Critical tier can be seen as a cloud equivalent for an Always On Availability Group with synchronous commit to a minimum number of replicas. As a Disaster Recovery concept, a failover group on SQL Managed Instance is comparable to an availability group with asynchronous commit. SQL Database and SQL Managed Instance HA are backed by Service-Level Agreements. For more information, see Overview of business continuity with Azure SQL Database.

How are backups handled in Azure SQL PaaS services?

You can check documentation for automated backups in SQL Managed Instance and SQL Database to learn about RPO, RTO, retention, scheduling, and other backup capabilities and features.

How is high availability (HA) achieved in SQL Managed Instance and SQL Database?

SQL Managed Instance and Database are built on top of inherent high availability (HA) architecture. This includes support for failover groups and various other features. You can choose between two HA architecture models: Standard availability model in General Purpose service tier, or Premium availability model in Business Critical service tier.

How does disaster recovery work in SQL Managed Instance and SQL Database?

See the SQL Database and SQL Managed Instance documentation. SQL Managed Instance Frequently Asked Questions provide information on DR options.

Performance and scale

How do I obtain better performance by moving on-premises SQL Server to SQL Managed Instance, SQL Database or SQL VM?

Moving from on-premises provides you with performance benefits due to the latest SQL Server database engine features, cloud scaling flexibility, and the newest generation of underlying hardware. Find out why your SQL Server data belongs on Azure. You can also read a recently published study by Principled Technologies benchmarking SQL Managed Instance and SQL Server on Amazon Web Services (AWS) RDS. It's important to ensure a proper sizing based on your requirements for CPU, memory, and storage (IOPS, latency, transaction log throughput and size). SQL Managed Instance and SQL Database also provide a choice between different hardware configurations and service tiers that provide additional means to reach target performance. Applications can also take advantage of read scale-out abilities including with named replicas and geo-replicas, and techniques such as database sharding.

How can I compare SQL Managed Instance performance to SQL Server performance?

See the Performance section of the SQL Managed Instance FAQ for guidance on performance comparison and tuning.

Migration and modernization process

I want to modernize SQL Server workloads to Azure SQL. What is the next step?

A great place to start is joining the Azure Migration and Modernization Program. When you start a migration project, a good practice is to form a dedicated migration team to formulate and execute the migration plan. If your company has an assigned Microsoft or Microsoft Partner account team, they can provide guidance regarding the migration team required skill set and overall process.