Migrating a SQL Server Database from AWS EC2 to Azure SQL Database by using the Microsoft Data Migration Assistant (DMA)

Amazon Web Services EC2 is a great Infrastructure as a Service (IaaS) platform, and many organizations use it to host SQL Server instances. Using an IaaS platform provides for easy migration scenarios from on-premises deployments, and for many people it’s a perfect first step into cloud. However, many companies soon realize that they are ready to take next step – to move their workloads and databases to a Platform as a Service (PaaS) cloud service. This is realm in which Microsoft Azure SQL Database truly shines.

Microsoft Azure SQL Database (formerly SQL Azure, Windows Azure SQL Database) is a cloud-based database service from Microsoft that offers data-storage capabilities. The aim is for users just to communicate with a T-SQL endpoint rather than having to manage database storage, files, and high availability. Azure SQL Database obviously has number of limitations, such as a lack of support for cross database queries, SQL Broker, etc., so some databases require preparation prior to the actual migration. However, for customers who are ready to migrate, the Data Migration Assistant (DMA) can be a viable option for performing such a migration from AWS EC2 IaaS.

In this tutorial, I have a SQL Server on Windows instance in EC2 that is running the well-known, venerable AdventureWorks2016 database. The image below shows this among my other servers in the EC2 console:

With an AWS elastic IP assigned to that Windows computer, I can easily connect to the default SQL instance via my local SSMS:

Note that previously I had to set up the proper inbound rules for this machine with both AWS EC2 Security Groups and Windows Firewall.

I created a database called SmallSQLTarget that is based on the S3 tier.

I also had to go to the security group for the server and open inbound ports for SQL Server traffic again (port 1433). A smart way to do it is to filter by the client IP of my workstation on which I run my SSMS and will run Data Migration Assistant tool.

Now I can make sure that I can connect to my target Microsoft Azure SQL Database from my client machine via SSMS as well:

Now, let’s proceed with the migration. To accomplish this, you need to download and install the Microsoft Data Migration Assistant (DMA) from https://aka.ms/get-dma. After you install the tool, you can create a new project. I will start with and assessment project to ensure that I don’t have any critical incompatibilities that preclude my migration to PaaS platform.

DMA checks for compatibility issues and feature parity in the database to determine what, if anything, needs to be done to migrate any features in the SQL Server instance to PaaS.

We can then provide credentials to our AWS EC2 based instance and pick a database after connecting to the source:

Now that we added the source, let’s start the assessment. DMA can assess any SQL Server database up to versions 2016. When the assessment is complete, you receive a nice report that shows the potential issues to consider before you migrate. The report should look similar to the following:

The assessment indicated that there were no migration blockers associated with the database, so now let’s perform the migration.

Select the new (+) icon to create new a migration project.

Let’s connect again to the instance on EC2 and select the database to migrate.

Next, connect to the Microsoft Azure SQL Database target by specifying the server address and credentials; selecting the target database, in this case SmallSQLAzure, and then selecting Next.

DMA will perform a quick assessment and present you with a list of the schema objects that you can migrate, with highlighting\marking to identify any objects that may have issues.

After you specify the objects that you wish to migrate and select Generate SQL script, DMA will generate a SQL DML script for these objects. You can then save that script for analysis and changes, and you can apply that script via SSMS on your own. You can also select Deploy Schema to apply the script via the tool.

After schema deployment finishes, we can proceed with data migration by selecting Migrate data.

Next, pick the tables that you want to migrate, and then select Start data migration.

Migration of data for smaller database such as AdventureWorks takes few minutes. Everything transfers other than two temporal tables, which are quite different from regular database tables. SQL Server 2016 introduced support for system-versioned temporal tables as a database feature, which brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. There are other methods available for migrating temporal tables to Azure SQL Database, and I will post more information about this in upcoming blog posts.

Well, we are pretty much done. Next, check that the data has migrated to the target by performing few queries in SSMS:

For more information on the Microsoft Data Migration Assistant see the following resources.