Objectives:
To migrate data from an On-premises SQL Sever to Azure SQL Database.
To show a quick and summarized walk through and practical insights of this specific process.

Background: I have a .NET Web app using the sample Adventure Works database all running in an on-premises dev server.

1. Install the Data Migration Assistant Tool into the on-premises SQL Server machine.
2. Run the assessment and understand any warnings, errors and recommendations
3. Create the target Azure SQL Database
4. Run the migration steps
5. Update application config connection string to the target database

The .NET web app displaying Orders Data. Note at the bottom showing the DB Server Name and database name. I will migrate the data to Azure SQL DB and update the application’s database connection string to Azure SQL DB.

Viewing the SQL Server and the AdventureWorks database using SQL Server Manage Studio:

1. Data Migration Assistant Tool
   Install, run and start a new Assessment into a server that can connect to the source SQL Server. In my case, I just installed on the SQL Server host.
   
2. Connect to SQL Server host as the source.
   Review both assessment options.

• The SQL Server feature parity category provides a comprehensive set of recommendations, alternative approaches available in Azure, and mitigating steps to help you plan the effort into your migration projects.
• The Compatibility issues category identifies partially supported or unsupported features that reflect compatibility issues that might block migrating on-premises SQL Server database(s) to Azure SQL Database. Recommendations are also provided to help you address those issues.

Remediate any before migration and/or plan to test after data migration. You can restart the assessment to get updated results.

3. Create Azure SQL Database

An empty SQL Database in Standard pricing tier @ 10 DTU.

Note to configure the firewall settings to have the appropriate security access for the migration assistant tool, SQL Management Studio and any other tools for troubleshooting. I find this are to time consuming to get things right depending on your architecture as there are many options. For initial reading visit https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure

With SQL Management Studio, I connect to this new Azure SQL Database. I like to create a dedicated user account for the migration. I use the following TSQL script

-- Creates the login
Use master
CREATE LOGIN roytest
WITH PASSWORD = 'TTEEESST88';
GO
Use advworks -- application database name
-- Creates a database user for the login created
CREATE USER roytest FOR LOGIN roytest;
GO
-- Add user to the database owner role or a more least privilege role
EXEC sp_addrolemember N'db_owner', N'roytest'
GO

4. Run the migration with the Data Migration Assistant tool

Select the database tables and start data migration. Consider increasing performance capacity of target Azure SQL to speed up the migration process. You can decrease the pricing tier after migration.

Migration Completed

5. Validate database migration by updating the web application connection string

Run and test the web application. You can see this application shows the updated database connection.

Contrasting Remarks

Comprehensive considerations: What I have shown seems simple and straight forward, but there are many more considerations when you assess, analyze and plan for a data migration. Such are the details with various database products and its versions, the database server features being used, the complexity of the database schema, objects and data types, security, the amount of data, network connectivity, downtime considerations, known limitations and much more. I have linked below the resources heading some of the documents that would help guide.

Data Migration Assistant vs Azure Data Migration Service: There are other approaches other than using the Data Migration Assistant tool. You can use the Data Migration Service that can operate in the Azure cloud. 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 (online migrations). The key difference with this service is it needs connectivity to your on-premises SQL Server. It can be through a direct public endpoint, which is usually not recommended due to security best practices. Network connectivity can be through a site to site, express route or point to site connection as the Data Migration Service is deployed to an Azure VNet/subnet.
Azure Data Migration Service does not do any assessments. You need to rely on the Data Migration Assistant tool to do that. If your migration are only a few databases, you might as well use the Data Migration Assistant tool. The Migration Service is well suited to migrate at large scale and in parallel.

Another migration approach I have done in past projects is to migrate with backup and restore of the .bacpac file. You can read the documentation for details. The situation in my case was for a large financial institution and there were many firewall and network connectivity from various on-premises sources. Overall it was just easier and less friction to do manually as the single database migration requests were on an on demand basis. The restore would be done in a jump or bastion VM server and would have to run custom SQL scripts for security hardening and setup. Running SQL scripts is not supported (from what I can see at the moment) in the Data Migration Assistant and Service, so would have to use SQL Management Studio anyways.

In the end, this goes to show that there are a few migration approaches and much analysis and planning involved.

References:

• https://github.com/RandomFractals/AdventureWorks
• Overview of Data Migration Assistant
• Azure Database Migration Service documentation
• Choosing your database migration path to Azure