How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service

The Azure Database Migration Service is a fully managed service designed to enable seamless migrations from multiple database sources to Azure Data platforms with minimal downtime. This service streamlines the tasks required to move existing third party and SQL Server databases to Azure. Deployment options include Azure SQL Database, Managed Instance, and SQL Server in Azure VM at Public Preview.

Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service

Create an Instance of Azure DMS – preparation steps

  • In the menu on the left side of the Azure portal, click All services
  • On the All services blade under GENERAL, click Subscriptions
Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service
  • On the Subscriptions blade, click your Hosting subscription
  • On the Hosting blade in the left pane under Settings, click Resource providers
  • In the Search box in the right pane, enter migration
  • If the Resource provider had not been previously registered, the option would have been Register instead of Re-register and Unregister.

Create an Instance of Azure Data Migration Services

  • In the menu on the left side of the Azure portal, click Create a resource
  • On the New blade in the Search box, enter azure database migration service
Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service
How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service 11
Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service
How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service 12

and press Enter On the Everything blade in the results list, click Azure Database Migration Service On the Azure Database Migration Service blade, click Create On the Create Migration Service blade under Service Name, enter ServiceName

  • Select your Subscription from the dropdown menu
  • Select Resource group the dropdown
  • Select Location dropdown menu
Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service
  • Select Virtual Network
  • Select Pricing Tier
Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service

Note: You may need to open your Windows Firewall to allow the Azure Database Migration Service to access the source SQL Server, which by default is TCP port 1433. If you’re running multiple named SQL Server instances using dynamic ports, you may wish to enable the SQL Browser Service and allow access to UDP port 1434 through your firewalls so that the Azure Database Migration Service can connect to a named instance on your source server. Lastly, if you’re using a firewall appliance in front of your source databases, you may need to add firewall rules to allow the Azure Database Migration Service to access the source database(s) for migration, as well as files via SMB port 445.

Create Storage SAS URI for DMS Migration Project

In the Favorites menu, click All resources

  • Click the storage account
  • On the Storage_Account blade, click Storage Explorer (preview)
  • On the Storage_Account- Storage Explorer (preview) blade flyout, expand BLOB CONTAINERS.

Note: When creating a blob container in this situation, retrieve it’s SAS URI by using the steps in the article Manage Azure Blob Storage resources with Storage Explorer, and be sure to select all permissions (Read, Write, Delete, List) on the policy window while creating the SAS URI. This detail provides the Azure DMS with access to your storage account container for uploading the backup files used for migrating databases to Azure SQL Database Managed Instance.

  • Right click the blob container backup, select Get Shared Access Signature
Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service
  • On the Shared Access Signature blade flyout, under permissions select Read, Write, Delete, and List permissions
Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service
  • Click Create

Copy and paste the SAS URL: into notepad on your computer. You will need this later in the lab. Note: This SAS URI will be used later in the lab for the SQL Data Migration Project

Create and Run a Migration Project

  • In the Favorites menu, click All resources
  • On the All resources blade, click Database_Migration_Service_Name
  • On the Database_Migration_Service_Name blade, click New Migration Project
  • On the New migration project blade under Project name, enter My-DMS-migration

In the Source server type dropdown menu, for this post we select SQL Server

Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service

In the Target server type dropdown menu, for this blog we selected Azure SQL Database managed Instance

Baking Clouds - How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service

In the Choose type of activity, leave the settings of Offline data migration Click Create and run activity On the Migration source detail blade under Source SQL Server instance name

In the Authentication type dropdown menu, select SQL Authentication

Note: Managed Instances support Authentication and Authorization through integrating Azure Active Directory. Authentication supports; SQL Authentication, which uses a username and password; Azure Active Directory Authentication, which uses identities managed by Azure Active Directory and is supported for managed and integrated domains, and more. Authorization supports the same capabilities as SQL Server 2017.

  • Check the boxes next to Encrypt connection and Trust server certificate Note: SSL connections that are encrypted using a self-signed certificate does not provide strong security. They are susceptible to man-in-the-middle attacks. You should not rely on SSL using self-signed certificates in a production environment or on servers that are connected to the internet. Note: Azure SQL Database Managed Instance combines advanced security features provided by Azure cloud and SQL Server Database Engine. Managed Instance provide additional security isolation from other tenants in the Azure cloud. Security isolation includes:
  • Native virtual network implementation and connectivity to your on-premises environment using Azure Express Route or VPN Gateway.
  • SQL endpoint is exposed only through a private IP address, allowing safe connectivity from private Azure or hybrid networks.
  • Single-tenant with dedicated underlying infrastructure (compute, storage).

In addition, SQL Database Managed Instance provides a set of advanced security features that can be used to protect your data:

  • Managed Instance auditing tracks database events and writes them to an audit log file placed in your Azure storage account. Auditing can help maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.
  • Data encryption in motion – Managed Instance secures your data by providing encryption for data in motion using Transport Layer Security.
  • Threat Detection complements Managed Instance auditing by providing an additional layer of security intelligence built into the service that detects unusual and potentially harmful attempts to access or exploit databases.
  • Dynamic data masking limits sensitive data exposure by masking it to non-privileged users.
  • Row-level security enables you to control access to rows in a database table based on the characteristics of the user executing a query (such as by group membership or execution context).
  • Transparent data encryption (TDE) encrypts Azure SQL Managed Instance data files, known as encrypting data at rest. TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. You can protect all your databases in Managed Instance with transparent data encryption.
  1. Click Save.
  2. Enter Target server name

In the Authentication type dropdown menu, select SQL Authentication In the User Name, Password Click Save

Select source databases blade and Click Save

In Select logins blade, select none of the users and Click Save

On the Configure migration settings blade dropdown menu, select I will let Azure Database Migration Service create backup files

On the Configure migration settings blade, Backup settings
On the Configure migration settings blade, Storage account settings

SAS URI, This is the SAS URI you created earlier in the lab. Open Notepad and copy it

Note: This is the SAS URI you created earlier in the lab

Click Save On the Migration summary blade under Activity name, enter My-SQL-Migration-Activity

  1. On the Migration summary blade, select Validation option
  2. On the Choose validation option blade, select Do not validate my database(s) and click Save
  3. Click Run migration
  4. On the My-SQL-Migration-Activity blade, you can monitor the status of the migration
  5. Click Refresh
  6. Click the Databases to see the detailed migration status.
  7. Click Refresh until the STATUS of the migration is Completed

Navigate the SQL Managed Instance Database

  1. In the Favorites menu, click All resources
  2. On the All resources blade, click sqlmi-shared-01 SQL managed instace
  3. On the sqlmi-shared-01 blade, you can see a database named rg582135-adventure-works, here you can manage your newly migrated database

For more information: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-get-started

How to migrate a SQL database to a Azure SQL Database Managed Instance using the Azure Database Migration Service
Scroll to top