Migrate SQL Server to Azure SQL Database Using DMS – Introduction

It’s common for users nowadays to execute SQL server migration from one server to another. However, when users want to migrate SQL server to Azure SQL database using DMS manually, then it gets a bit tricky. The reason is the involvement of various manual procedures as well as their complications.

In this detailed tutorial, we’re going to explain this complicated process in an easy manner for sure. In addition, the modern solution is also mentioned here to migrate SQL server to Azure without any long & complex procedure. If you guys want to get rid of the manual method, the modern approach is the experts’ first preference. Let’s dive in & start from the modern solution & then begin the manual solution step by step.

All Ways to Migrate SQL Server to Azure SQL Database – Select the Best

Before we start learning the manual method, it’s important for users to be aware of the alternative if possible. For this,  we must mention experts’ first preference to execute such complex database migration. Just download the SQL Server to Azure SQL Database Migration Tool in your system.

Download Now Purchase Now

This solution is way faster & safer than the manual method.  However, knowing the manual method to migrate SQL server to Azure SQL database using DMS is also good to know. If you are interested in the Manual Solution, below are the Pre-Requisites to start with:

• Users must have the SQL server 2016 or later in their system to rely on this method.
• It’s crucial for users to enable the TCP or IP protocol error-free. (Disabled by Default)
• There should be the latest Data Migration Assistant version installed in the system.
• Restore the AdventureWorks2016 database to your SQL server instance as well.
• Create or be prepared with a database in your Azure SQL for the migration.
• Creation of Microsoft Azure Virtual Network for Azure DB Migration Service is required.
• Configuring the Windows Firewall for access to the database engine is indeed important.
• Ensure that the Network Security Group outbound security rules of your virtual network don’t block port 443 for service bus, storage & Azure Monitor.

These are primarily the key aspects that users need to keep in mind. For a detailed list of prerequisites, users can visit Microsoft’s Official site as well. They have a separate page for each pre-requisite in depth.

Also Read: Upgrade SQL Server 2014 to 2019 Version

Assess On-Premises DB to Migrate SQL Server to Azure SQL Database Using DMS

Before we dig directly for the gold, we need to make sure our equipment is all good. This means that users need to assess their database before they go for the migration task. Users need to follow the six simple steps in order to assess the databases.

1. Select the New (+) icon, and then Select the Assessment project type in the data migration assistant to begin.
2. Enter a Project Name here >> Select Database Engine from the drop-down. Now, Select Azure SQL Database in the target server type. Then Create a Project.
3. In the Options Screen, Select the Next button to move closer to migrate SQL server to Azure DB.
4. Under the Select Sources screen, there is a Connect to a Server Dialog box. Provide connection details & Select Connect option.
5. Under the dialog box saying Add Sources, Select AdventureWorks2016, Select Add & then Select Assessment option.
6. The final step here is to review the result of the assessment for database migration blocking issues once.

Time to Migrate the Sample Schema

Users need to take in use the Database Migration Assistant to migrate the schema to Azure SQL Database after assessing the database. Carefully follow these nine steps to migrate SQL server to Azure SQL database using DMS. This schema migration can provide users with an estimated idea of the real migration.

1. Select the New (+) icon, and then Select the Assessment project type in the data migration assistant just like in the previous section.
2. Enter the Project name in the source server type, Select SQL Server & then Select Azure SQL Database in the target server type.
3. Now, select only Schema under the migration scope & then continue.
   
   
4. Select the Create option to create the project now.
5. Enter source connection details in the DMA & then Select Connect & Select the AdventureWorks2016 database.
   
   
6. Select the Next button. Under Connect to a target server option, enter the details required.
   
   
7. Click on Next to Go to the Select-Object screen & specify schema objects in the AdventureWorks2016 database.
   
   
8. Click on the Generate SQL Script option & review the script for any errors present.
   
   
9. Select the Deploy Schema option to deploy the schema to the Azure SQL Database safely. 
   

Resource Provider Registration Process

The registration of Microsoft.DataMigration resource provider is necessary for users to create the first instance of the database migration services. This way users can migrate SQL server to Azure SQL database without any errors.

1. First of all, Sign in to the Azure Portal & then Search & Select Subscription.
   
   
2. Select the Subscription as per your requirements & then Select the Resource Providers carefully.
   
   
3. Search the Migration option & then simply Register for Microsoft.DataMigration option.
   
   

Follow the below steps as mentioned in the respective images along with the instructions to migrate SQL server to Azure. Now, users can move ahead for instance creation.

Azure Database Migration Service Instance Creation to Migrate SQL Server to Azure

Step-1. On the home page or in the Azure portal menu, Click on Create a Resource option. Search as well as Select the Azure Database Migration Service here.

Step-2. Click on the Create button in the Azure Database Migration Service screen.

Step-3. Select the “Continue to use Azure Database Migration Service” option here.

Step-4. Select these basic Details in the Create Migration Service basics screen:

• Subscription.
• Create a new resource group or choose an existing one.
• Specify a name for the Azure Database Migration Service instance.
• Select the location for instance creation.
• Choose Azure as the service mode.
• Select the pricing tier.
• Click on the Next: Networking button.
  

Step-5. Adjust Settings in the Create Migration Service screen & Click on the Review + Create button.

Step-6.  Users will get to see such a screen after some time that shows the instance is ready to use.

Create A Migration Project in Microsoft Azure

Now, there are just five steps to creating a project to migrate SQL server to Azure SQL database using DMS method.

Step-1. Select All Services in the Azure portal menu first. Now, Search & Select Azure Database Migration Services.

Step-2. Now, Select the Instance that you created in the previous section.

Step-3. Click the New Migration Project option here to continue.

Step-4. Enter the following details here to continue:

• Specify a name for the project to migrate SQL server to Azure SQL database.
• Select SQL Server in the source to continue now.
• Select Azure SQL database in the destination.
• Select Data Migration as the migration activity type.

Step-5. Now, at last, users just need to Select Create & Run Activity option to create the project & run the process of database migration.

Also Read: How to Host Tally ERP on Azure Server Safely

Source Details to Migrate SQL Server to Azure SQL Database Using DMS

1. Now that users have arrived at the Select Source screen, they need to just enter the connection details for the source SQL Server instance. (Use Fully Qualified Domain Name or IP Address)
2. Click the Trust Server Certificate box if you don’t have installed a trusted certificate.
   
   
3. Select the Next: Select Databases option here.

Now, users need to select the databases for migration in depth to migrate SQL server to Azure SQL DB. For this task, they need to follow the below steps carefully.

1. There may be a list of databases present. Here, users need to select the databases that they want to migrate.
2. The second task is to review the expected downtime. If that is acceptable without any issues, Click on the Next: Select Target option to migrate SQL server to Azure SQL database.
   

Migrate SQL Server to Azure After Specifying Target

1. Provide the Authentication Settings to your Azure SQL Database on the Select Target Screen. 
2. Click the Next: Map to target databases button & map the source & target destination for database migration.
   
   
3. Click on the Next: Configuration migration settings option, expand the table lists & review the list of affected fields.
   
   
4. Select the Next: Summary option to move ahead.
   
   

Run the Migration At Last

This is the final phase to migrate SQL server to Azure SQL database using DMS in just a few simple steps.

Step-1. Select the Start Migration button.

Here, users can see the acidity with pending status. Just wait for the completion.

Step-2. Select Refresh to update your display here until the status is shown as completed.

Bringing It All Together

We tried our best to explain this complicated operation in short to users. However, if users still face difficulties to migrate SQL server to Azure SQL database using DMS, they can opt for the automated solution. This manual method is even hard to provide the perfect solution to users due to the involvement of several technicalities. Therefore, users that are not proficient in these technicalities, may find it difficult to get the solution. In that case, an automated tool is the best answer.