Muokkaa

Jaa

Migrate SQL Server workloads (FAQ)

  • Artikkeli

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. A range of migration tools helps making the optimal choice by providing an assessment of target service compatibility and costs.

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). Software Assurance also lets you implement free passive HA and DR secondaries using SQL VM.

In addition, every Azure SQL service provides you the option to reserve instances in advance (for terms of either one year or three years) and obtain significant additional savings. Dev/Test pricing plans provide a way to further reduce development costs. Finally, check the following article on how you can Optimize your Azure SQL Managed Instance cost with Microsoft Azure Well-Architected Framework.

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 85% savings compared to pay-as-you-go pricing in Azure SQL. In addition, make sure to check different Dev/Test pricing plans.

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, using Azure Reservations for SQL VM, SQL Managed Instance and SQL Database vCores, and using constrained vCPU capable Virtual Machines give you a wide variety of options to build a cost-effective solution.

For implementing BCDR solutions in Azure SQL, you benefit from built-in HA replicas of SQL Managed Instance and SQL Database or free passive HA and DR secondaries using SQL VM. 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. In addition, make sure to check different Dev/Test pricing plans.

If you would like to understand how you can save up to 64% by moving to Azure SQL, you can review the Environmental, Social and Governance Impact (ESG) report on The Economic Value of Migrating On-Premises SQL Server Instances to Microsoft Azure SQL Solutions. Finally, check the following article on how you can Optimize your Azure SQL Managed Instance cost with Microsoft Azure Well-Architected Framework.

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 Azure SQL Migration extension in Azure Data Studio or Data Migration Assistant 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. For an overview of compliance offerings, you can consult Azure compliance documentation.

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.

Where can I find migration guides for Azure SQL?

The following guides help you discover, assess, and migrate from SQL Server to Azure Virtual Machines, SQL Managed Instance, and SQL Database.

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 Microsoft 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 the 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 purchasing model and Database transaction unit purchasing model. You can explore Pricing - Azure SQL Database Single Database and learn about pricing options.

What subscription types are supported in SQL Managed Instance?

Check Supported subscription types for SQL Managed Instance.

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.

How do I move from SQL VM to SQL Managed Instance?

You can follow the same migration guide as for the on-premises SQL Server.

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 85% savings compared to pay-as-you-go pricing in Azure SQL, when combined with reservations savings and extended security updates. For more information, see Microsoft Azure Services Product Terms.

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. You can also use the Azure Hybrid Benefit Savings Calculator to calculate the exact savings for your SQL Server estate.

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 PAYG 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.

Do I have to pay for HA and DR replicas for Azure SQL VM?

If you have Software Assurance, you can implement high availability (HA) and disaster recovery (DR) plans with SQL Server on Azure SQL VMs, without incurring additional licensing costs for the passive disaster recovery instance. For more information, see the SQL VM documentation.

Do I have to pay for disaster recovery (DR) in SQL Managed Instance and SQL Database?

SQL Managed Instance provides the possibility to configure a license-free standby replica, which means that SQL Server licensing costs for the vCores that the secondary standby replica uses aren't charged.

Can I centrally manage Azure Hybrid Benefit for SQL Server across the entire Azure subscription?

Yes. You can centrally manage your Azure Hybrid Benefit for SQL Server across the scope of an entire Azure subscription or overall billing account. This feature is currently in preview.

If I move some of SQL Servers, my workloads to SQL Managed Instance and leave some workloads on-premises, can I manage all my SQL licenses in one place?

You can centrally manage your licenses that are covered by Azure Hybrid Benefit for SQL Server across the scope of an entire Azure subscription or overall billing account. This data can be combined with an overview maintained by your licensing partner/procurement department or obtaining licensing information by creating your own custom licensing overview. Your licenses must be used either on-premises or in the cloud, but you have 180 days of concurrent use rights while migrating servers.

How can I minimize downtime during the online migration?

The SQL Managed Instance link feature offers the best possible minimum downtime online migrations solution, meeting the needs of the most critical tier-1 applications. You can consult a full range of migration tools and technologies choose the optimal for your use scenario.

Risk free migration with a hybrid strategy

Can I keep running on-premises, while modernizing my applications in Azure?

With SQL Server 2016, 2019, and 2022, you can use the Link feature for Azure SQL Managed Instance to create a hybrid connection between SQL Server and Azure SQL Managed Instance. Data is replicated near real-time from SQL Server to Azure, and can be used to modernize your workloads in Azure. You can use the replicated data in Azure for read scale-out and for offloading analytics.

You can keep running the hybrid link for as long as needed: weeks, months, years at a time, there are no restrictions on this.

Yes, you can use your replicated data in Azure to test and validate your migration strategy (performance, workloads, and applications) before migrating to Azure.

Can I reverse migrate out of Azure SQL and go back to SQL Server if necessary?

With SQL Server 2022, we offer the best possible solution to seamlessly move data back with native backup and restore from SQL Managed Instance to SQL Server, completely de-risking the migrations strategy to Azure.

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 Azure SQL Migration extension in Azure Data Studio or Data Migration Assistant. Both tools provide 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.

Where can I find migration guides to Azure SQL?

The following guides help you discover, assess, and migrate from SQL Server to Azure Virtual Machines, SQL Managed Instance, and SQL Database. You can consult Azure Database Migration Guides, which also contains guides for migrating to another database targets.

Which migration tools can I use?

You can use the Azure SQL migration extension for Azure Data Studio for SQL Server assessment and migration, or choose among other migration tools.

How do I minimize downtime during the online migration?

The SQL Managed Instance link feature offers the best possible minimum downtime online migrations solution, meeting the needs of the most critical tier-1 applications.

How can I optimize the costs once I migrate to Azure SQL?

Cost Optimization guidelines of Microsoft Azure Well-Architected Framework (WAF) provide methodology to optimize costs for every Azure SQL service. You can also find out more about WAF cost optimization highlights for SQL Managed Instance.

Related content