Migrate SQL Server database from Azure SQL Managed Instance to AWS

Did you know you can migrate a Microsoft SQL Server database from an Azure SQL Managed Instance to SQL Server on Amazon Web Services (AWS) using a COPY_ONLY backup from Azure SQL Managed Instance? Using this method is straightforward, as it copies/moves all objects in a database and it supports all editions. However, keep in mind that this method only supports full backup and restore and does not support differential or transaction log backups. There are other methods you can use to capture incremental changes during the migration.
In this blog post, we will cover the various ways you can migrate a SQL Server database from Azure SQL Managed Instance to SQL Server on AWS, including the latest feature of Azure SQL Managed Instance backup portability to SQL Server 2022.

Why should you migrate from Azure SQL Managed Instance to AWS?

While Azure SQL Managed Instance is a fully managed platform that is compatible with the latest SQL Server 2022 database engine, migrating to AWS with SQL Server 2022 copy-only backup has its advantages. First, it gives you more flexibility and control over your infrastructure. You can choose the instance types, storage options, and network configurations that best suits your need. You can install additional software, apply specific performance optimization, and have more control over the overall environment. Another important aspect is cost savings. By migrating to SQL Server on Amazon Elastic Compute Cloud (Amazon EC2), you have potential to save on licensing costs and take advantage of high availability options on Amazon EC2.

We will cover three different methods of migrating from Azure SQL Server Managed Instance to AWS:

• SQL Server backup and restore (COPY_ONLY)
• AWS Database Migration Service (AWS DMS)
• SQL Server Import and Export Wizard

Solution overview

The solution architecture diagram in Figure 1 illustrates the three methods of migrating a SQL Server database to AWS:

Figure 1: Azure SQL Managed Instance to SQL Server on AWS Migration options

You have multiple options when migrating from Azure SQL Managed Instance to AWS. You can migrate to SQL Server on Amazon EC2 or Amazon Relational Database Service (Amazon RDS) for SQL Server. Table 1 compares the features of the three migration options we’re covering in this blog post. By understanding the challenges and benefits, you can make an informed decision on which method suits your needs and aligns with your overall migration goal.

                                     Table 1: Azure SQL Managed Instance to AWS Options Comparision

* Refer to AWS DMS documentation for details.

Prerequisites

•  Azure storage account
•  SQL Server source database hosted on Azure SQL Managed Instance
•  An active AWS account with SQL Server 2022 on Amazon EC2
•  Network connectivity between AWS and Azure (VPN, Private network, or internet)
•  SQL Server Management Studio (version 19.0.1 or higher)

Method 1. Migrate using SQL Server COPY_ONLY backup

You can migrate a SQL Server database from Azure SQL Managed Instance to SQL Server on Amazon EC2 using the COPY_ONLY backup from Azure SQL Managed Instance. This method offers an easy way to copy or move databases with all database objects from your Azure managed instance to a SQL Server 2022 instance (Enterprise, Developer, or Standard edition). This method of migration supports only full backup and restore and does not support differential or transaction log backups. You would need to migrate SQL Server logins from source SQL Server to target SQL Server on AWS.

Note: The Azure SQL Managed Instance COPY_ONLY backup method does not support TDE. Back up your source SQL Server database and certificates and disable TDE prior to migration. Disabling TDE can be a resource-intensive operation. Please plan this activity during off-peak hours. TDE can be enabled on your SQL Server database on Amazon EC2 post-migration.

Ensure you have connectivity between the source and destination SQL Server databases. You can connect to the source Azure SQL Server Managed Instance from Amazon EC2 to perform the backup.

Here are the steps to perform the migration:

1.1   Log into your Azure cloud portal and navigate to your Azure storage account.

1.2   Create an Azure blob storage container in your Azure storage account, as shown in Figure 2:

Figure 2: Create Container in Azure Blob storage

1.3   You need to create shared access token for the Azure storage container, as shown in Figure 3:

Figure 3: Create Shared access token for storage container

1.4   Next, connect to Azure SQL Server Managed Instance via the SQL Server Management Studio (SSMS). Use the shared access token from step 1.3 to create the credential that will allow access to Azure blob storage using the script in Figure 4:

CREATE CREDENTIAL [https://sqlbackup02142023.blob.core.windows.net/sqlbackup] WITH IDENTITY = 'SHARED ACCESS SIGNATURE'

Figure 4 Create Credentials

1.5    Once the credential is created, you need to run the COPY_ONLY backup command to do a full database backup to Azure blob storage using the script in Figure 5. Azure SQL Managed Instance only offers the COPY_ONLY option for the backup command.

BACKUP DATABASE [AzureSQLMIBackuptest] TO URL = 'https://sqlbackup02142023.blob.core.windows.net/sqlbackup/AzureSQLMIBackupfulll.bak' WITH COPY_ONLY

Figure 5: Backup Database with COPY_ONLY

1.6   You can connect to the destination SQL Server 2022 on Amazon EC2 using SSMS and create credentials using the script in Figure 6 for Azure SQL Blob storage (which uses the shared access token created in step 1.3):

CREATE CREDENTIAL [https://sqlbackup02142023.blob.core.windows.net/sqlbackup] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET ='sp=sssssssssxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’

1.7   Once credential is created, you should run the restore database  command on destination SQL Server on Amazon EC2, using the script in Figure 7:

RESTORE DATABASE AzureSQLMIBackuptest
FROM URL = 'https://sqlbackup02142023.blob.core.windows.net/sqlbackup/AzureSQLMIBackupfull.bak'
WITH
MOVE 'data_0' TO 'C:\MSSQL\DATA\AzureSQLMIBackuptest_data_0.mdf',
MOVE 'log' TO 'C:\MSSQL\DATA\AzureSQLMIBackuptestlog.ldf',
MOVE 'XTP' TO 'C:\MSSQL\DATA\AzureSQLMIBackuptest_xtp.xtp'

Figure 7: Restore database on target SQL Server on Amazon EC2

1.8   Next, validate that the restored database is available using SSMS on destination SQL Server on Amazon EC2, as shown in Figure 8:

Figure 8: Azure SQL Managed Instance restored on SQL Server on EC2

The SQL Server backup and restore method offers only full database backups, so this method will require some downtime depending on the size of your database.

Method 2. Migrate using AWS DMS

If you have mission-critical workloads running on an Azure SQL Server Managed Instance database that needs minimal downtime migration to AWS, you can use the AWS Database Migration Service (AWS DMS). AWS DMS, along with the SQL Server backup and restore method, can migrate SQL Server databases from Azure SQL Managed Instance to SQL Server on Amazon EC2.

You can do a full-load, Change Data Capture (CDC) migration from Azure SQL Managed Instance to Amazon RDS for SQL Server using AWS DMS with minimal downtime.

AWS DMS uses MS-Replication or MS-CDC for tracking changes. Please review AWS DMS documentation for details.

Here are the steps to migrate using AWS DMS:

2.1. First, enable CDC at the source Azure SQL Server Managed Instance database and tables,as shown in Figure 9:

Figure 9: Enable CDC for Azure SQL Server Managed Instance  source database

2.2. Then, create an AWS DMS replication instance, as shown in Figure 10:

Figure 10: Create replication instance in AWS DMS

2.3. You have to create Azure SQL Managed Instance as a source endpoint. You can test the connectivity from the source endpoint to the replication instance by selecting “Test connection” in the “Endpoint configuration” console screen. Ensure connectivity is correctly configured. Please refer to the AWS DMS documentation for details.

Figure 11: AWS DMS Endpoint configuration for Azure SQL Managed Instance as source

2.4. After the source endpoint is created, create the destination SQL Server endpoint. Next, test the connectivity from the source endpoint to the replication instance by selecting “Test connection” in the “Endpoint configuration” console screen, as shown in Figure 12. Ensure connectivity is correctly configured.

Figure 12: AWS DMS endpoint configuration for SQL Server on EC2 as destination

2.5. To create an AWS DMS task, select the source-to-target endpoint, as configured in steps 2.3 and 2.4. Select migration type of “Migrate existing data and replicate ongoing changes” for continuous replication, as shown in Figure 13:

Figure 13: AWS DMS Migration task configuration

2.6. Next step is to monitor the replication task. Figure 14 shows the AWS DMS task progress. When both databases are in sync, you may implement cutover activity to the target SQL Server on Amazon EC2 and complete the migration with minimal downtime.

Figure 14:  AWS DMS migration task status progress report from DMS replication summary pag

Figure 14:  AWS DMS migration task status progress report from DMS replication summary page

To find more details about this migration method, please read this blog post, Migrating your SQL Server database using AWS DMS.

Method 3. Migrate using SQL Server Import and Export Wizard

If you have smaller databases or you want to do a partial data migration or you have a requirement to do a data transformation during migration, you can use the SQL Server Import and Export Wizard to migrate from Azure SQL Managed Instance to SQL Server on Amazon EC2.

You need to ensure you have connectivity between the source and destination SQL Server databases. You can connect to the source Azure SQL Server Managed Instance from Amazon EC2to run through the steps.

Here are the steps to migrate using the SQL Server Import and Export Wizard:

3.1. Connect to the Azure SQL Managed Instance using SQL Server Management Studio (SSMS).

3.2. Right-click on the source database and select tasks à export data. Provide connection details for Azure SQL Managed Instance using Microsoft OLE DB Driver for SQL Server, as shown in Figure 15:

Figure 15: Choose source for SQL Server Import and Export Wizard

3.3. Next, provide connection information for SQL Server 2022 on Amazon EC2 as the target server, as shown in Figure 16:

Figure 16: Choose destination for SQL Server Import and Export Wizard

3.4. Test both source and target connections and ensure connections are working.

3.5. Select the source tables and views to migrate, ensure mapping is as expected, and click ok to migrate the data.

Figure 17: SQL Server Import and Export Wizard progress report

3.6. Validate the data post-import by connecting to the target SQL Server via SSMS. Test and validate the applications and ensure they are working as expected.

To learn more about this migration method, please check out the, Bulk copy section from Importing and exporting SQL Server data using other methods.

Summary

In this blog post, we covered three different options to migrate from Azure SQL Server Managed Instance to SQL Server on Amazon EC2. You can leverage the SQL Server 2022 native backup and restore method to migrate to SQL Server 2022 on AWS. For near real-time migration with minimal downtime, consider using the AWS DMS service. For smaller databases, or for performing partial migrations like selected objects (tables, views, etc.), you can consider using the SQL Server Import and Export Wizard.

To migrate SQL Server databases from Azure SQL Managed Instance to AWS with minimal downtime, you can also leverage CloudBasic from the AWS Marketplace.

For more information about SQL Server migrations using other methods, see Migrate an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server.

If you’re currently evaluating your migration and modernization options for your SQL Server workloads, including SQL Server 2022, please contact us. We’d be happy to help you with your SQL Server plans and initiatives.

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your migration journey today.