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
Restored Adventure Works 2012 database
Download SQL Data Migration Assistant v3.3
DMA – Start a new project, Migrate Schema only (using source DB from SQL 2016, on Server 2016), click Next
Choose the TARGET DB, click Next
Allow default settings to be selected, click Generate SQL Script
If desired, click Save to capture the script. Further, review any potential issues. Click Deploy Schema.
You should see the schema sucessfully deploy
Register Microsoft.DataMigration Provider
From the Azure portal, browse to Subscriptions > Resource Providers > search on ‘data’ > locate Microsoft.DataMigration and click ‘Register’
Once Microsoft.DataMigration shows registered you can proceed:
Add the Database Migration Service
From the Azure portal Marketplace, search on ‘data migration service’
Enter unique Service Name (for this example, used DMSMigration2), choose pricing tier, then Create.
From the new Data Migration Service, choose New Migration Service
Enter the Source Server name (FQDN) or IP address
Select the source DB name
Enter the TARGET Azure SQL DB, SQL Server container name, click Save
Click Configure Required Settings, then choose Do not validate my database(s), click Save
Review the summary dialog, then click Run Migration
Migration in progress