Migrate Databases to Azure SQL Managed Instance

This tutorial guides you through the process of migrating your on-premises SQL Server databases to Azure SQL Managed Instance. We will cover common migration scenarios and best practices.

Note: This tutorial assumes you have a basic understanding of SQL Server and Azure.

Scenario Overview

This migration process involves several key steps:

Assessment: Evaluate your existing SQL Server environment for compatibility with Azure SQL Managed Instance.
Migration: Transfer your databases to Azure SQL Managed Instance using suitable tools.
Cutover: Redirect your applications to the migrated databases.
Validation: Ensure the migrated databases and applications function correctly.

We will focus on a common scenario: migrating a database from an on-premises SQL Server instance to Azure SQL Managed Instance using the Azure Database Migration Service (DMS).

Prerequisites

Before you begin, ensure you have the following:

An active Azure subscription.
An Azure SQL Managed Instance instance created.
An on-premises SQL Server instance with the databases you wish to migrate.
Appropriate network connectivity between your on-premises environment and Azure (e.g., VPN or Azure ExpressRoute).
SQL Server Management Studio (SSMS) installed.
Azure Database Migration Service (DMS) instance provisioned.

Important: Ensure your on-premises SQL Server version is supported for migration. Refer to the official Azure documentation for the latest compatibility information.

Step 1: Migrate your database using Azure DMS

Azure Database Migration Service (DMS) is a fully managed service designed to enable seamless migrations from multiple database sources to Azure data platforms with minimal downtime.

1.1 Create a Migration Project in DMS

1. Navigate to your Azure Database Migration Service instance in the Azure portal.

2. Click on + New Migration Project.

3. Enter a Project name (e.g., OnPremSQLToManagedInstance).

4. Select SQL Server as the Source server type.

5. Select Azure SQL Managed Instance as the Target server type.

6. Choose the Migration activity type: Online data migration (for minimal downtime) or Offline data migration.

7. Click Create and run project.

1.2 Configure Source and Target Details

1. On the Source details page, provide the connection details for your on-premises SQL Server instance:

Source SQL Server name: Your server name or IP address.
Authentication type: SQL Authentication or Windows Authentication.
Username and Password: Credentials for connecting to the source SQL Server.

2. On the Target details page, provide the connection details for your Azure SQL Managed Instance:

Target SQL Managed Instance name: Your managed instance name.
Login and Password: Credentials for connecting to the managed instance.

3. Click Save.

1.3 Select Databases

Choose the databases you want to migrate from the list of available databases on your source SQL Server.

Click Save.

1.4 Configure Migration Settings

Review and configure any specific settings, such as table selection or data transformation rules if applicable.

Click Save.

1.5 Run Migration

1. On the Summary page, review all the configuration details.

2. Click Start migration.

You can monitor the migration progress in the Azure portal. The status will update as data is transferred and the process completes.

Step 2: Verify Migration

Once the migration is reported as successful, it's crucial to verify the data and application functionality.

2.1 Connect to Azure SQL Managed Instance

Use SQL Server Management Studio (SSMS) to connect to your Azure SQL Managed Instance using its fully qualified domain name (FQDN) and your provided credentials.

Example connection string: your_managed_instance_name.your_dns_suffix.database.windows.net

2.2 Data Validation

1. Execute sample queries against the migrated databases to compare row counts and data integrity with the source databases.

2. Run consistency checks and any custom validation scripts you might have.

2.3 Application Testing

1. Update your application's connection strings to point to the Azure SQL Managed Instance.

2. Perform thorough testing of your application's features, ensuring all functionalities work as expected.

3. Monitor application performance and identify any potential bottlenecks.

Step 3: Clean up

After successfully verifying the migration and ensuring your applications are running smoothly against Azure SQL Managed Instance, you can perform cleanup operations.

Decommission source SQL Server: Once confident, you can stop or decommission your on-premises SQL Server instance.
Remove migration resources: If you used specific resources for migration that are no longer needed (e.g., backup locations), clean them up to avoid incurring unnecessary costs.
Delete Azure DMS instance: If you no longer need Azure Database Migration Service for future migrations, delete the DMS instance.

Tip: Consider keeping your on-premises database available for a short period as a fallback before completely decommissioning it.