Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio
Published Mar 23 2023 01:45 PM 8,960 Views
Microsoft

 

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 for creating 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 extension from 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 can assign a custom role.
  • Create a target instance of Azure 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 the SQL Server dacpac extension or the SQL Database Projects extension in Azure Data Studio.
  • If you're using Azure Database Migration Service for the first time, make sure that the Microsoft.DataMigration resource 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

Ganapathivarma_3-1679591461527.png

 

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.

Ganapathivarma_1-1679601068378.png

 

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

 

Ganapathivarma_4-1679601226597.png

 

  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 the SQL Server dacpac extension or the SQL Database Projects extension in Azure Data Studio.

Ganapathivarma_5-1679601270479.png

 

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 to Connections. 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 select Manage.

 

Ganapathivarma_2-1679601083589.png

 

 

  1. In the server menu under General, select Azure SQL Migration.
  2. In the Azure SQL Migration dashboard, select Migrate to Azure SQL to open the migration wizard.

Ganapathivarma_3-1679601101918.png

 

 

 

 

  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. In Step 1: Databases for assessment in the Migrate to Azure SQL wizard, select the databases you want to assess. Then, select Next.

 

Ganapathivarma_2-1679601416406.png

 

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

    a. In Choose your Azure SQL target, select Azure SQL Database.

 

Ganapathivarma_3-1679601508421.png

 

 

b. Select View/Select to view the assessment results.

 

 

Ganapathivarma_4-1679601544091.png

 

 

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

 

Ganapathivarma_6-1679601598424.png

 

 

d. Select Get Azure recommendation to open the recommendations pane.

 

Ganapathivarma_8-1679601661733.png

 

 

e. Select Collect performance data now. Select a folder on your local computer to store the performance logs, and then select Start.

 

Ganapathivarma_9-1679601702140.png

 

 

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 select Restart data collection to 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 selected Azure SQL Database target, select View details to open the detailed SKU recommendation report:

 

Ganapathivarma_10-1679601749952.png

 

 

g. In the selected Azure SQL Database target, select View details to open the detailed SKU recommendation report:

 

Ganapathivarma_11-1679601761632.png

 

 

 

 
  1. In Review Azure SQL Database Recommendations, review the recommendation. To save a copy of the recommendation, select Save recommendation report.
 

Ganapathivarma_12-1679601783141.png

 

 

  1. Select Close to close the recommendations pane.
  2. Select Next to continue your database migration in the wizard.

Configure migration settings

 

In Step 3: Azure SQL target in 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. For Azure SQL Database Server, select the target Azure SQL Database server (logical server). Enter a username and password for the target database deployment. Then, select Connect. Enter the credentials to verify connectivity to the target database.

 

Ganapathivarma_6-1679609476256.png

 

 

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

 

Ganapathivarma_7-1679609494097.png

 

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

 

In Step 4: Azure Database Migration Service in 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. In Resource group, select the resource group that contains an existing instance of Database Migration Service.
  2. In Azure Database Migration Service, select an existing instance of Database Migration Service that's in the selected resource group.
  3. Select Next.

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

 

Ganapathivarma_0-1679609630868.png

 

 

In Step 5: Data source configuration, complete the following steps:

  1. Under Source credentials, enter the source SQL Server credentials.
  2. Under Select tables, select the Edit pencil icon.

 

Ganapathivarma_5-1679602041719.png

 

 

3. In Select tables for <BikeStores>, select the tables to migrate to the target. The Has rows column indicates whether the target table has rows in the target database. You can select one or more tables. Then, select Update.

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

 

Ganapathivarma_8-1679602115176.png

 

 

4. After selecting Run validation, a Running validation panel appears on the right side of Azure Data Studio:

 

a. The pre-migration validation runs automatically, verifying multiple settings in the migration configuration. On the Running validation panel, under the Validation steps, the process goes through checking Integration runtime connectivitySource database connectivity, and Target database connectivity.

 

 

Ganapathivarma_9-1679602129460.png

 

b. After all, steps are successfully validated, select Done.

 

Ganapathivarma_10-1679602141583.png

 

 

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

 

Ganapathivarma_11-1679602180009.png

 

 

Start the database migration

 

In Step 6: Summary in the Migrate to Azure SQL wizard, review the configuration you created, and then select Start migration to start the database migration.

 

 

Ganapathivarma_5-1679609418530.png

 

 

Monitor the database migration

 

  1. In Azure Data Studio, in the server menu under General, select Azure SQL Migration to go to the dashboard for your Azure SQL Database migrations.

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

 

Ganapathivarma_12-1679602304907.png

 

 

 

 

  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.
 
 
 

Ganapathivarma_13-1679602314737.png

 

 

 

  1. When all table data is migrated to the Azure SQL Database target, Database Migration Service updates the migration status from In progress to Succeeded.
 

Ganapathivarma_15-1679602332533.png

 

 

 

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

Ganapathivarma_16-1679602350880.png

 

Summary 

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

 

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. 

 

Co-Authors
Version history
Last update:
‎Mar 23 2023 03:15 PM
Updated by: