Migrate Amazon RDS for SQL Server database to Azure SQL single database using Azure DMS

Azure provides different database migration services to handle different Rs of cloud adoption rationalization.

1. Azure Migrate for Rehost (lift & shift) — both source and target servers will have the same database (E.g., on-prem SQL Server 2012 migrating to SQL Server 2012 on Azure VM)
2. Database Migration Service (DMS) for Refactor and Rearchitect — source database will be upgraded on the target server (E.g., on-prem SQL Server 2012 migrating to Azure SQL)

Note: AWS also provides a service with the same name Database Migration Service (DMS), for migrating databases to AWS data platform.

I used Azure DMS to migrate an user database in Amazon RDS for SQL Server database instance to Azure SQL single database. This process included these steps.

1. Create Amazon RDS for SQL Server database instance.
2. Load data into a user database in Amazon RDS for SQL Server database instance.
3. Perform source and target database compatibility assessment using Azure Data Migration Assistant (DMA).
4. Create Azure SQL single database instance.
5. Create Azure Database Migration Service (DMS) instance and migration project within DMS.
6. Perform database migration from Amazon RDS for SQL Server to Azure SQL using Azure DMS.

Before I get into explaining the steps involved in database migration, I just want to share my observation, there is no Source server type “AWS RDS for SQL Server” in the Azure DMS drop-down list but the Microsoft documentation is saying there is.

I created an issue in GitHub for updating the documentation.

Step 1: Create Amazon RDS for SQL Server database instance

I chose to go with “Easy Create” database creation method as it saves time in the creation process. Some of the default settings applied during database creation can be modified later.

As this instance is only for testing purposes, I just went with the username as “admin”. This is not recommended for actual workloads.

Once the database instance is created, I modified it to make it publicly accessible. This is not recommended for actual workloads.

The endpoint and port number are provided in the database portal.

Step 2: Load data into a user database in Amazon RDS for SQL Server database instance

We can access the database from SQL Server Management Studio (SSMS). These details must be entered in SSMS to connect to the Amazon RDS for SQL Server database instance.

1. Server type: Database Engine
2. Server name: <endpoint>,<port>
3. Authentication: SQL Server Authentication
4. Login: <Master username>
5. Password: <Master password>

Once we connect to the database instance, we can notice a non-system database called “rdsadmin”. Amazon RDS uses this database to store the objects that it uses to manage the database. The “rdsadmin” database also includes stored procedures needed to perform advanced tasks.

I created a new database “aws2az” for my testing, and loaded a New York taxi public dataset file into the database.

Step 3: Perform source and target database compatibility assessment using Azure Data Migration Assistant (DMA)

The Data Migration Assistant (DMA) helps upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database.

Performing the compatibility assessment before migrating to Azure SQL is very helpful and highly recommended. DMA provide the details about what needs to be done in case any compatibility issues were found.

We can get the latest version of DMA from this link.

I created an assessment project and performed the compatibility assessment between my Amazon RDS for SQL Server database and Azure SQL database. The source server name for Amazon RDS for SQL Server must be entered as <endpoint>,<port>

Step 4: Create Azure SQL single database instance

I created Azure SQL single database instance. I chose the connectivity method as “Public endpoint” for my testing. This is not recommended for actual workloads.

Step 5: Create Azure Database Migration Service (DMS) instance and migration project within DMS

Note: DMS will not create target tables. So, tables on target database instance have to be created before migration process is started.

For my testing, I created the target table manually. For actual full database migration, schema can be migrated first using DMA tool and then DMS can be used for data migration.

Step 6: Perform database migration from Amazon RDS for SQL Server to Azure SQL using Azure DMS

Migration can be started once the migration project is created within DMS. We can monitor the progress of migration activity within DMS.

You can notice in the above screenshot that migration process ran for 7.5 hours for just one table. Migration activity performance depends on various factors like

1. target database SKU (pricing tier)
2. source server performance
3. DMS SKU (pricing tier)
4. network bandwidth

For my testing, I chose “Gen5, 2 vCores” for Azure SQL database and “Standard: 1 vCores” for DMS instance. So, the migration activity ran long.

For actual migration, its recommended to upgrade Azure SQL database to Business critical tier and create DMS in premium pricing tier.

Note: You can revert to lower pricing tier for Azure SQL once the migration is complete.

Once the migration is done, data in the Azure SQL database can be checked using SSMS.

Disclaimer: The posts here represent my personal views and not those of my employer or any specific vendor. Any technical advice or instructions are based on my own personal knowledge and experience.