Azure Data Migration Service Review

The Data Migration Service (DMS) is a new Azure service still in preview. DMS will help you migrate existing on-premises SQL Server, Oracle, and MySQL databases to Azure SQL Database, Azure SQL Database Managed Instance or SQL Server on an Azure virtual machine.

Since it is now in public preview, for the time being, it is “free” to use.

It has several advantages for migrating data, in that once the data is migrated to the target, it is possible to run a continuous synchronization, or synchronize as needed to keep the target DB up to date. Best of all, only changes (deltas) are synchronized from the source to target on subsequent synchronization.

The easiest way to test DMS, is to stand up SQL Server running on an Azure VM. If the target DB will be on premise, you will need a VNET in place to Azure.

There is a useful migration guide as well, Database Migration Guide <https://datamigration.microsoft.com/>

Here are the high-level steps involved:

1. Upgrade (or install latest) Database Migration Assistant (DMA) to v3.3

2. Create blank Azure SQL DB

3. Open firewall rules on server for Azure SQL DB

4. Run DMA assessment (New Project)

5. Run DMA Migration (New Project)

a. Generate Schema only

b. Deploy Schema

6. Register DMS Service in Azure

7. Using DMS:

a. Create a new DMS Service

b. Schedule a data synchronize task

c. Run task

From the Azure Portal, create an empty Azure SQL DB, open the firewall rules (as needed) for the SQL Server container. I named mine DMA2, this will be your TARGET DB.

To prepare the SOURCE server:

In Azure, I created a SQL Server 2016 Standard Instance on Server 2016 (used a standard template).

On the target server, used a sample database from Adventure Works 2012

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks2012

Restored Adventure Works 2012 database

Download SQL Data Migration Assistant v3.3

https://www.microsoft.com/en-us/download/details.aspx?id=53595

DMA – Start a new project, Migrate Schema only (using source DB from SQL 2016, on Server 2016), click Next

clip_image002

Choose the TARGET DB, click Next

clip_image004

Allow default settings to be selected, click Generate SQL Script

clip_image006

If desired, click Save to capture the script. Further, review any potential issues. Click Deploy Schema.

clip_image008

You should see the schema sucessfully deploy

clip_image010

Register Microsoft.DataMigration Provider

https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql#register-the-microsoftdatamigration-resource-provider

From the Azure portal, browse to Subscriptions > Resource Providers > search on ‘data’ > locate Microsoft.DataMigration and click ‘Register’

clip_image012

Once Microsoft.DataMigration shows registered you can proceed:

clip_image014

Add the Database Migration Service

From the Azure portal Marketplace, search on ‘data migration service’

clip_image016

Click Create

clip_image018

Enter unique Service Name (for this example, used DMSMigration2), choose pricing tier, then Create.

From the new Data Migration Service, choose New Migration Service

clip_image020

Enter the Source Server name (FQDN) or IP address

clip_image022

Select the source DB name

clip_image024

Enter the TARGET Azure SQL DB, SQL Server container name, click Save

clip_image026

Click Configure Required Settings, then choose Do not validate my database(s), click Save

clip_image028

Review the summary dialog, then click Run Migration

clip_image030

Migration in progress

clip_image002[4]

Migration complete

clip_image004[4]

Advertisements
This entry was posted in Azure SQL Server, Migration, SQL Server 2016. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.