Hello, in this article we will see how to create an assessment project and then we will perform a migration of a database using Data Migration Assistant (DMA) from an instance of SQL Server 2017 on a local PC to Azure SQL Database.

Prerequisites:

• SQL Server 2017 installed locally.
• A Microsoft Azure subscription.
• Data Migration Assistant.
• SQL Server Management Studio

Disclaimer: Given the constant updating of Cloud Computing technologies some steps may be different at the time of reading the article, I will make the effort to keep it updated, but there could be some differences between what is shown below and the Azure console at the moment of the implementation.

Creating a local database

To start we will import a database on the local machine using a backup of a database called WideWorldImporters (https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers- v1.0) To restore a backup of an instance of SQL Server, we can use Management Studio.

1. Open SQL Server Management Studio and connect to the local SQL Server instance.



2. Right click on the Databases node and select Restore Database.



3. Select Device and click on the button …



4. In the pop-up window click on Add, Go to the location where the downloaded backup is located and select the backup copy. Click on OK 2 times.



5. Returning to the main window, the backup will be loaded, click on OK. This will start the database restore. Once you have finished, you will have the WideWorldImporters database installed on the instance of SQL Server.

Creating an Azure SQL Database

1. Enter to https://www.portal.azure.com, click on All Services, type and click on SQL Servers



2. At the top of the window click on Add, a window (also known as a blade) will appear on the right side, there fill in the following information, taking as example notation an abbreviation of the resource, the initial of the name and the last name:
   • Server name: srv-vvillar
   • Server admin login: vvillar
   • Password: <A password that meets the complexity required>
   • Subscription: <Your Azure subscription>
   • Resource group: Create new > rg-vvillar
   • Location: <Select the nearest region where users are located>
   • Advanced Data Security: Not now
   • Click on Create





3. Wait for the Go to resource button to appear, click on it and it will take us to the Azure SQL Database server.



4. Go to the option Firewalls and virtual networks, click on +Add client IP  this will add a rule to allow access to the server from our IP. Click on Save.



5. Go to the Overview option and at the top of the window click on +New database



6. Fill the following values:
   • Database name: sql-vvillar
   • Select source: Blank database
   • Want to use SQL elastic pool?: Not now
   • Pricing tier: Standard S0
   • Collation: SQL_Latin1_General_CP1_CI_AS
   • Click on OK



7. After a few minutes verify that the database is created by going to the SQL Databases option

Performing an assessment of the local database

1. Open the Data Migration Assistant program, and click on the + symbol and fill in the following values:
   • Project type: Assessment
   • Project name: assessment-vvillar
   • Source server type: SQL Server
   • Target server type: Azure SQL Database
   • Click on Create



2. Leave the default values selected Check database compatibility, Check feature parity and click on Next



3. A popup window will appear on the right side, there fill the following values:
   • Server name: <Name of the local SQL Server>
   • Authentication type: Windows Authentication
   • Click on Connect



4. Select your database and click on Add



5. Click on Start Assessment



6. The unsupported and partially supported functionalities will appear at the level of SQL Server feature parity and Compatibility issues the recommendations to apply for each, so you can make a migration without errors.



7. If we click on Export Report we can export the report in JSON format (I’m using Visual Studio to view the file but you can use others like notepad++, Visual Studio Code, Sublime Text, etc).

Migrating the local database to the Azure SQL Database

1. In Data Migration Assistant click on + and fill the following values:
   • Project type: Migration
   • Project name: migration-vvillar
   • Source server type: SQL Server
   • Target server type: Azure SQL Database
   • Migration Scope: Schema and data
   • Click on Create



2. In the next window fill the following values:
   • Server name: <name of the local SQL Server>
   • Authentication type: Windows Authentication
   • Click on Connect
   We see that our database appears in the middle of the window and then click Next



3. Now we will put the values of the database of Azure SQL Database for it we need the name of the Azure SQL Database server, in the Azure portal go to the Properties section and copy the value under Server name.



4. Return to Data Migration Assistant and fill in the following values:
   • Server name: <name of the Azure SQL Database Server that we just copied>
   • Authentication type: SQL Server Authentication
   • Username: vvillar
   • Password: <The password that you assigned when you created the Azure SQL Database>
   • Click on Connect
   The Azure SQL Database instance will appear and click on Next



5. In the next window we will choose what we want to migrate and click on Generate SQL Script



6. In the next window click on Deploy Schema and then click on Migrate Data



7. In the next window click on Start data migration





8. Go to SQL Server Management Studio and click on Connect > Database Engine



9. In the pop-up window fill the following values:
   • Server name: <Azure SQL Database server name>
   • Authentication: SQL Server Authentication
   • Login: vvillar
   • Password: <The password you assigned when you created the Azure SQL Database>
   • Click on Connect



10. So now we can connect to the Azure SQL Database that we just migrated.