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.

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

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.

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

3. 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.

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.

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

5. 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.

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

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

b. SelectView/Selectto view the assessment results.

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

d. SelectGet Azure recommendationto open the recommendations pane.

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

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:

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

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

4. SelectCloseto close the recommendations pane.
5. 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.

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.

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.

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

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

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.

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.

b. Afterall, steps are successfully validated,selectDone.

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

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.

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.

2. 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.

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

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

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.

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.