Introduction

Migrating an on-premises SQL Server database is an important task and should be planned to perfection. An ideal migration methodology should be like the one shown in the following diagram:

Figure 2.1: Migration methodology

Finding the Migration Benefits

You should first analyze and find out the benefits of migrating an on-premises SQL database to an Azure SQL database. Migration involves a lot of time, effort, and cost, and it shouldn't be done just for the sake of having a cloud database.

Finding the Blockers

The next step is to find out the compatibility issues that may stop you from migrating to Azure SQL Database.

Selecting a Service Model

The next step is to find out whether the database will be deployed individually, in an elastic pool, or as a SQL managed database instance. This is important as the service model will affect the overall pricing, service tier, performance, and management of the Azure SQL Database.

Selecting a Service Tier

The next step is to...

DTU Pricing Models

Database Transaction Units

The amount of resources (CPUs, I/O, and RAM) to be assigned to an Azure SQL Database in a particular service tier is calculated in Database Transaction Units (DTUs).

DTUs guarantee that an Azure SQL Database will always have a certain amount of resources and a certain level of performance (offered under a particular DTU model) at any given point of time, independent of other SQL databases on the same Azure SQL server or across Microsoft Azure.

The ratio for the aforementioned resources was calculated by Microsoft by running an Online Transaction Processing (OLTP) benchmark.

The DTU amount signifies how powerful an Azure SQL Database is. For example, if a workload of, say, 5 queries takes 80 seconds on the Basic tier with 5 DTUs, then it'll take around 4 seconds on the Standard S3 tier with 100 DTUs.

There are four pricing tiers available in the DTU-based pricing model:

Note

The DTU is the measure of Azure SQL Database performance. This is discussed...

Choosing Between a vCore Pricing Model and DTU-based Service Tiers

When choosing between vCore and DTU-based pricing tiers, consider the following.

Licensing

A vCore pricing model provides up to 30% cost savings by using existing on-premises SQL Server Standard or Enterprise licenses with software assurance. Therefore, if you are migrating an existing on-premises SQL Server infrastructure, consider opting for a vCore pricing model.

Flexibility

A DTU-based model bundles up the compute, IOPs, and storage under DTUs and provides a pre-configured range of varying DTU amounts for different types of workloads. It's therefore best suited for when you need a simple pre-configured option.

A vCore model provides flexibility when selecting compute and storage options and is therefore best when you want more transparency with control and over the compute and storage options.

Consider a scenario where you have a database with high compute requirements and low storage requirements; say, 125 DTUs with...

Azure Database Migration Services

Azure Database Migration Services, or DMS, is a fully managed Azure service that enables seamless migrations from multiple data sources to Azure databases.

Here are some examples of migrations that DMS can do:

• Migrate on-premises SQL Server to Azure SQL Database or SQL managed instance. Supports both online and offline migrations.
• Migrate Azure SQL Database to SQL database managed instances.
• Migrate an AWS SQL Server RDS instance to Azure SQL Database or SQL managed instance.
• Migrate MySQL to Azure Database for MySQL.
• Migrate PostgreSQL to Azure Database for PostgreSQL.
• Migrate MongoDB to Azure Cosmos DB Mongo API.

  Note

  Online migration is a migration with zero downtime or near-zero downtime during the cut-over to the migrated database.

Exercise: Migrating a SQL Server Database to Azure SQL Database Using Azure DMS

Note

In the exercise, the source database is on a SQL server on Azure VM. To migrate an on-premises database, site-to...

Summary

Migrating to an Azure SQL database is an important task and should be planned to perfection. This lesson talked about a migration strategy that you should follow when migrating from an on-premises database to an Azure SQL database:

1. Find the migration benefits.
2. Find the blockers.
3. Select a service model.
4. Select a service tier.
5. Select the main region and the disaster recovery region.
6. Select a migration tool.

This lesson also talked about how to choose a service tier and covered different migration tools to migrate the data and the schema from an on-premises SQL Server database to an Azure SQL database. In the next lesson, we will learn how to perform manual and automatic backups that are unique to Azure SQL databases.