Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (2023)

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. engine. PaaS capabilities built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical for your business. It support modern cloud applications on an intelligent, managed service that includes serverless compute. Azure SQL Database is always running on the latest stable version of the SQL Server database.

Customers running SQL Server workloads on Amazon RDS for SQL Server were looking to migrate to Azure SQL Database. The reason being Azure SQL Database can provide additional benefits not available in Amazon RDS for SQL Server. The benefits were discussed in previous post Part 1.

Currently, you can migrate Amazon RDS for SQL Server to Azure SQL database using any of the following methods:

Migration option

Comments

Import/Export Service (BACPAC)

It is the easiest to use and is integrated with SQL Server Management Studio. However, it requires additional storage and downtime because data is exported at the source, possibly copied to another location and then imported at the destination. We encourage using this method to import/export databases lesser than 150GB because it takes a long time to process databases with many objects.

Data Migration Assistant

Migrates schema and data.

Good for medium-sized deployments (i.e., small number of databases).

Azure Migration extension for Azure Data Studio

Azure Data Studio, a cross-platform tool now with the Azure SQL Migration Extension, you assess, get right-sized recommendations, and migrate to different Azure SQL targets offline or online. For Azure SQL DB, it supports only offline Migration

In this blog post, I will demonstrate how to use the Azure Migration extension of Azure Data Studio to migrate AWS RDS SQL Server database to Azure SQL Database offline.

The Azure SQL migration extension for Azure Data Studio enables you to assess, get right-sized Azure recommendations and migrate your SQL Server databases to Azure.

Prerequisites

Before you get started, you must complete the following prerequisites:

  • Prepare and configure the Source RDS SQL Server Instance following the documentation forcreating a Microsoft SQL Server DB instance and connecting to it
  • Prepare your target Azure SQL Database running on Azure for migration
  • Create a SQL Server database in RDS targeted for migration
  • Setup a self-hosted integration run time inside a windows VM as SQL Server instance is located outside of Azure network, you need to set up a self-hosted integration runtime to connect to it.
  • If you don’t want data to be transferred over public Internet, you can achieve higher security by transferring data over a private peering link between AWS Direct Connect and Azure Express Route
  • Download and install Azure Data Studio.
  • Install the Azure SQL Migration extensionfrom Azure Data Studio Marketplace.
  • Have an Azure account that's assigned to one of the following built-in roles:
    • Contributor for the target instance of Azure SQL Database
    • Reader role for the Azure resource group that contains the target instance of Azure SQL Database
    • Owner or Contributor role for the Azure subscription (required if you create a new instance of Azure Database Migration Service)
    • As an alternative to using one of these built-in roles, you canassign a custom role.
  • Create a target instance ofAzure SQL Database.
  • Make sure that the SQL Server login that connects to the source SQL Server instance is a member of the db_datareader role and that the login for the target SQL Server instance is a member of the db_owner role.
  • Migrate the database schema from source to target by using theSQL Server dacpac extensionor theSQL Database Projects extensionin Azure Data Studio.
  • If you're using Azure Database Migration Service for the first time, make sure that the Microsoft.DataMigrationresource provider is registered in your subscription.
  • It is important to mention that you do not need an Azure account to complete the database assessment or Azure recommendation steps in the migration wizard. An Azure account is only needed when configuring the migration steps.

Solution Architecture

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (1)

Steps to Migrate Data from RDS SQL Server to Azure SQL Database offline using Data Migration Extension of Azure Data Studio.

  1. Connect to RDS SQL Server instance and create a database targeted for migration.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (2)

2. Create tables and populate data in your source RDS SQL Server database that will drive the data migration.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (3)

(Video) How to plan Microsoft SQL Server migration to Azure with Azure Migrate

  1. As a next step we shall create target database in Azure SQL Database, As you can see here, there are no backup files required for this migration (refer Solution Architecture diagram). We will continue using Azure Data Studio, and Self-hosted integration runtime in the on-premises machine. But because this is logical migration, we must create the database schema in our target first.Refer documentation on how to migrate database schema from source to target by using theSQL Server dacpac extensionor theSQL Database Projects extensionin Azure Data Studio.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (4)

Open the Migrate to Azure SQL wizard in Azure Data Studio

To open the Migrate to Azure SQL wizard:

  1. In Azure Data Studio, go toConnections. Select and connect to your on-premises instance of SQL Server. You also can connect to SQL Server on an Azure virtual machine.
  2. Right-click the server connection and selectManage.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (5)

  1. In the server menu underGeneral, selectAzure SQL Migration.
  2. In the Azure SQL Migration dashboard, selectMigrate to Azure SQLto open the migration wizard.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (6)

  1. On the first page of the wizard, start a new session or resume a previously saved session.

Run database assessment, collect performance data, and get Azure recommendations

  1. InStep 1: Databases for assessmentin the Migrate to Azure SQL wizard, select the databases you want to assess. Then, selectNext.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (7)

  1. InStep 2: Assessment results and recommendations, complete the following steps:

    a. InChoose your Azure SQL target, selectAzure SQL Database.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (8)

b. SelectView/Selectto view the assessment results.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (9)

c. In the assessment results, select the database, and then review the assessment report to make sure no issues were found.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (10)

d. SelectGet Azure recommendationto open the recommendations pane.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (11)

(Video) How to migrate SQL Server databases to Azure

e. SelectCollect performance data now. Select a folder on your local computer to store the performance logs, and then selectStart.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (12)

f. Azure Data Studio collects performance data until you either stop data collection or you close Azure Data Studio.

After 10 minutes, Azure Data Studio indicates that a recommendation is available for Azure SQL Database. After the first recommendation is generated, you can selectRestart data collectionto continue the data collection process and refine the SKU recommendation. An extended assessment is especially helpful if your usage patterns vary over time.

In the selectedAzure SQL Databasetarget, selectView detailsto open the detailed SKU recommendation report:

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (13)

g. In the selectedAzure SQL Databasetarget, selectView detailsto open the detailed SKU recommendation report:

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (14)

  1. InReview Azure SQL Database Recommendations, review the recommendation. To save a copy of the recommendation, selectSave recommendation report.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (15)

  1. SelectCloseto close the recommendations pane.
  2. SelectNextto continue your database migration in the wizard.

Configure migration settings

InStep 3: Azure SQL targetin the Migrate to Azure SQL wizard, complete these steps for your target Azure SQL Database instance:

a. Select your Azure account, Azure subscription, the Azure region or location, and the resource group that contains the Azure SQL Database deployment.

b. ForAzure SQL Database Server, select the target Azure SQL Database server (logical server). Enter a username and password for the target database deployment. Then, selectConnect. Enter the credentials to verify connectivity to the target database.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (16)

(Video) Migrate SQL Server to Azure | Data Exposed

c. Next, map the source database and the target database for the migration. ForTarget database, select the Azure SQL Database target. Then, selectNextto move to the next step in the migration wizard.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (17)

Use an existing instance of Database Migration Service or Create a Database Migration Instance

InStep 4: Azure Database Migration Servicein the Migrate to Azure SQL wizard, create a new instance of Azure Database Migration Service or reuse an existing instance that you created earlier. In this example, I’m using an existing instance of Database migration instance.

To use an existing instance of Database Migration Service:

  1. InResource group, select the resource group that contains an existing instance of Database Migration Service.
  2. InAzure Database Migration Service, select an existing instance of Database Migration Service that's in the selected resource group.
  3. SelectNext.

Note: To create a new Database migration Service. Refer this documentation link.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (18)

InStep 5: Data source configuration, complete the following steps:

  1. UnderSource credentials, enter the source SQL Server credentials.
  2. UnderSelect tables, select theEditpencil icon.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (19)

3. InSelect tables for <BikeStores>, select the tables to migrate to the target. TheHas rowscolumn indicates whether the target table has rows in the target database. You can select one or more tables. Then, selectUpdate.

You can update the list of selected tables anytime before you start the migration.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (20)

4. AfterselectingRun validation, aRunning validationpanelappearson the right side of Azure Data Studio:

a. The pre-migration validation runs automatically, verifying multiple settings in the migration configuration.On theRunning validation panel, underthe Validation steps,the process goes throughcheckingIntegration runtime connectivity,Source database connectivity, andTarget database connectivity.

(Video) Azure Migration Using Data Studio Online and Offline With Integration Run Time and Blob Storage.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (21)

b. Afterall, steps are successfully validated,selectDone.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (22)

c. Review your table selections, and then selectNextto move to the next step in the migration wizard.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (23)

Start the database migration

InStep 6: Summaryin the Migrate to Azure SQL wizard, review the configuration you created, and then selectStart migrationto start the database migration.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (24)

Monitor the database migration

  1. In Azure Data Studio, in the server menu underGeneral, selectAzure SQL Migrationto go to the dashboard for your Azure SQL Database migrations.

UnderDatabase migration status, you can track migrations that are in progress, completed, and failed (if any), or you can view all database migrations.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (25)

  1. Select a database name to open the table view. In this view, you see the current status of the migration, the number of tables that currently are in that status, and a detailed status of each table.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (26)

  1. When all table data is migrated to the Azure SQL Database target, Database Migration Service updates the migration status fromIn progresstoSucceeded.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (27)

  1. Connect to target database on Azure SQL Database and verify the migrated data. You've completed the migration to Azure SQL Database.

Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio (28)

Summary

In this blog post, we have discussed usage ofAzure Data Studiofor migrating data from Amazon RDS for SQL Server Instance to Azure SQL Database.

(Video) Discover & Assess SQL Server Data Estate Migrating to Azure SQL Using Azure Migrate | Data Exposed

The Azure SQL migration extension for Azure Data Studio enables you to assess, get right-sized Azure recommendations for Azure migration targets, and migrate databases offline from on-premises SQL Server, SQL Server on Azure Virtual Machines, or any virtual machine running in the cloud (private, public) to Azure SQL Database.

To learn more about Azure SQL Database Offline migrations, see Tutorial: Migrate SQL Server to an Azure SQL Database offline using Azure Data Studio with DMS.

Videos

1. Azure SQL Databases: Connecting in Azure Data Studio
(Sigma Coding)
2. Azure Database Migration Techniques
(vamsy chiranjeevi)
3. Migrating offline data with Azure SQL Database
(Microsoft Developer)
4. How to Accelerate Oracle to Azure Database Migration | Data Exposed
(Microsoft Developer)
5. Migrating an On Premises Microsoft SQL DB to Azure
(Cirrus Data Solutions Inc.)
6. Deep Dive with Data Migration Tools and Azure Database Migration Service (DMS) - Charles Christian
(PASS Data Community Summit)
Top Articles
Latest Posts
Article information

Author: Maia Crooks Jr

Last Updated: 06/01/2023

Views: 6012

Rating: 4.2 / 5 (63 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Maia Crooks Jr

Birthday: 1997-09-21

Address: 93119 Joseph Street, Peggyfurt, NC 11582

Phone: +2983088926881

Job: Principal Design Liaison

Hobby: Web surfing, Skiing, role-playing games, Sketching, Polo, Sewing, Genealogy

Introduction: My name is Maia Crooks Jr, I am a homely, joyous, shiny, successful, hilarious, thoughtful, joyous person who loves writing and wants to share my knowledge and understanding with you.