A Technical Guide to Migrating On-Premises SQL Server Databases to Azure SQL Databases

Azure Database Offering

The Microsoft Azure SQL family has a rich set of database offerings, each suitable for a different type of workload and pricing tier There are many reasons to migrate to the cloud, including…

• Capital vs operation expenditure. Enterprises and businesses bear only the operation costs of running their required workloads on the cloud. Simultaneously, a cloud provider benefits from an economy of scale and can pass those savings on to consumers.
• Short release cycle. Cloud providers make it extremely easy for end users to create any required workload from a web interface near-instantaneously. This feature of quickly creating, testing, and releasing resources in production is a hallmark of any cloud provider.
• Scale for urgent/immediate capacity needs. Cloud has built-in scalability—horizontally or vertically. Behind the scenes, monitoring systems track different metrics, and when a current set of resources can no longer handle the workload automatically it will scale out/up. Similarly, when demand slows, the system automatically scales in/down.
• Hardware refresh from outdated systems to cutting-edge data centers. Cloud providers have state-of-the-art data centers and, whenever any hardware update is required, they will handle it while ensuring the configured workloads function properly with zero downtime.
• Global reach. With help of cloud power, web applications, databases, and more can be easily accessible via the internet.

Considerations

Key factors to consider when you’re evaluating migration options:

• The number of both database servers and the databases in each server
• The sizes of the databases
• The amount of downtime a business can reasonably accept during the migration process

Deployment Models

Application workload and usage pattern are the main factors in deciding which Azure SQL deployment model should be used.

The models:

• A single database represents a fully managed database with an SLA of 99.95% that is suitable for most modern cloud applications and microservices
• An elastic pool is a collection of single databases with a shared set of resources—for example, CPU or memory—which is suitable for combining databases in a pool with predictable usage patterns that can effectively share the same set of resources. SLA for elastic pools is 99.995% and each database could be up to 100TB.
• Managed Instance is designed for customers seeking to shift many apps from an on-premises environment or IaaS, self-built, or ISV-provided environment to a fully managed PaaS cloud environment with as low a migration effort as possible and complete isolation of customer instance with native VNet support. SLA for this type of offering is 99.995%.

SQL on VM is suitable when full control over the database server with nearly 100% SLA is required.

It allows you to move on-premises databases without any changes and supports database sizes up to 256TB.

Purchasing Models

To choose between vCore, database transaction unit (DTU), or serverless purchasing models consider:

• The vCore model allows you to pick the number of vCores for Azure SQL Database—making it the simplest choice when translating from on-premises SQL Server.

In the vCore-based purchasing model, costs will depend upon…

• Service tier
• Hardware configuration
• Compute resources (vCores and memory)
• Reserved database storage
• Actual backup storage

• The DTU model abstracts the underlying compute, memory, and I/O resources to provide a blended DTU.
• The serverless model is suitable for workloads that require automatic scaling and compute resources billed per second of usage. Though the serverless tier automatically pauses databases during inactivity, storage is still billed. When activity resumes, so does the database.

Service Tiers

Service tiers are designed for different types of applications and include…

• The general purpose and standard service tier offers a balanced budget-oriented option, suitable for delivering applications in the middle and lower tiers. Redundancy is built into the storage layer to help recover from failures. It’s designed for most database workloads.
• The business critical and premium service tier is for high-tier applications that require high transaction rates, low-latency I/O, and a high level of resiliency. Secondary replicas are available for failover and to offload read workloads.
• The hyperscale service tier is for databases that have growing data volumes and need to automatically scale up to 100 TB in database size. It’s designed for very large databases.

Migration Tiers

• Azure Migrate: Azure Migrate is a Microsoft service that helps an enterprise assess how on-premises workloads will perform and how much they will cost to host. It helps in planning the migration process.
• Data Migration Assistant: This desktop tool from Microsoft provides seamless assessments of SQL Server and single-database migrations to Azure SQL Database (both schema and data).
  It can accommodate downtime during the data migration process and reports compatibility issues as well as SQL server feature parity. It also tracks the status of migration per database object, including the number of rows migrated. Its processes should be performed in off hours. (Supported sources: SQL Server (2005 to 2019) on-premises or Azure VM; AWS EC2 & AWS RDS; GCP Compute SQL Server VM.)
• Azure Database Migration Service: This Azure service can migrate SQL Server databases to Azure SQL Database through the Azure portal or automatically through PowerShell. To ensure connectivity with the source DB server it uses Azure VPN. Designed for large migrations in terms of the number of databases or size of the database, you can migrate single databases or at scale. It can run in both online and offline modes, with minimal downtime. Migrations at scale can be automated via PowerShell. The time to complete migration depends on database size and the number of objects in the database. It requires the source database to be set as read-only. (Supported sources: SQL Server (2005 to 2019) on-premises or Azure VM; AWS EC2 & AWS RDS; GCP Compute SQL Server VM.)

Migrate SQL Database Using Azure Migrate

Prerequisites:
Install DMA (Data migration assistant V5.6) tool.

• Download the sample AdventureWorksLT2017.bak file and restore it on the local on-prem database server.
• Create Azure Migrate project.
• Create a Database in Azure.

Login to your Azure portal and search “Azure Migrate.”

Click “Azure Migrate” and choose “Databases (only)” for migration.

Post-Migration

In the post-migration stage, it is important to perform a very basic sanity test, which is a schema comparison between the source and destination database.

Compare the schema of the source on-premises database with the cloud-based destination database.

Here, one change has been found—related to an extended property only—which has no impact on migration.

Happy migration—and, remember, regardless of where you are on your journey, OZ will help you bring to bear the power of the cloud to transform your organization, save money, increase scalability, gain competitive advantage, and improve business efficiency and operations. Find out more about our cloud services and assessment offerings here.