2 Practices to Migrate Large SQL Server Database to Azure
In this write-up, we will learn different practices to perform large data migration to Azure. Particularly there are multiple ways to perform SQL database migration to Azure Cloud. Like using SSMS Export Data-Tier Application Process, Deploy Database to Microsoft Azure SQL Database, Azure database migration service, etc. But, here we will cover the other two most proficient approaches to transfer data from SQL Server to Azure. The solutions described here will help users to migrate large SQL server database to Azure efficiently.
“Recently I was trying to export the existing SQL Server database to Azure SQL. The size of the database is 400 GB. Although, I tried the “Export Data-Tier Application process” to carry out the task. But, it takes a large amount of time for the migration due to the large size of the database. Can anyone suggest a simple method to migrate a large SQL Server database to Azure?
Two Techniques for Large Data Migration to Azure
2 methods to export a local SQL database to Azure are given as follows:
1- DMA (Data Migration Assistant)
2- SysTools SQL to Azure Migrator Tool
Quick Brief About Data Migration Assistant (DMA)
Database Migration Assistant Tool enables us to migrate an on-premises SQL Server database to Azure. It offers two types of projects i.e. Assessment and the Migration projects.
Assessment Project: It generates a report on the compatibility issues and the SQL Server features that are not supported or partially supported on the target SQL Server (Azure).
Migration Project: It helps to export the local SQL database to Azure Cloud.
Steps for SQL Server to Azure Migration
Follow these simple steps to migrate a large SQL Server database to Azure:
1- Click on the + in New tab & Select the Assessment in project type
2- Enter the Project Name, Source Server type, Target Server type & click on Create
(Note: in the Target Server type select the Azure SQL Database)
3- Choose the ‘Check feature parity’ and ‘Check database compatibility’ & click on “Next.”
4- Give server name, authentication type, and connection properties of the on-premises database server and then hit the “Connect” button
5-Choose the database which is to be migrated & click on “Add.”
6- Click the Start Assessment button
7- Now, DMA shows the partially supported features and not supported features & also shows the compatibility issues
8- Save these details by clicking on the “Export reports.”
1- Firstly, click on the + in the New tab & choose the Migration in the project type.
2- Enter the project name, Source Service type, target server type, migration scope & click Create
3- Connect to the source server by giving the server name and Authentication type & click on “Connect.”
4- Choose the single database from your source server and then click on “Next.”
5- Enter the details of the target server & SQL authentication credentials i.e. User Name and Password. Then, click on “Connect.”
6- Choose the Connection properties: Select the Encrypt connection and Trust server certificate according to your environment.
7- Choose the database from the target server & start the source database schema assessment process, click on the Next button.
8- After the process completion review the objects selected for the migration process. If any issue is found then remove those objects from the migration. Also, if there is no issue then choose the schema objects which you want to migrate and click on “Generate SQL script.”
9- Review the scripts & modify them if required. Then, click on the “Deploy Schema” option. The process will take time based on the number of objects.
10 – After the process, the user can see the status of schema deployment.
11- Click on the Migrate Data button. This will list the table details select the tables for the migration and click on the “Start data migration” button.
12- The migration time-span depends upon the number of tables. Once the migration process gets completed the status of the data migration gets displayed.
13- Lastly, for the confirmation of the data migration process, check the tables where data is migrated to the Connected Azure SQL database.
Limitations of Using DMA
The entire process of using the Database Migration Assistant to migrate large SQL Server to Azure is quite lengthy. Along with this, the steps involved in the entire process are complex and time-consuming. Therefore, in such circumstances, the best solution is to use a third-party application.
Best Option to Migrate Large SQL Server Database to Azure
SysTools SQL Server to Azure Migration Tool is the perfect solution to migrate an on-premises SQL Server database to Azure. The software just requires you to browse the MDF or NDF file for the migration process. With this utility, it is possible to migrate selected SQL Server tables, triggers, stored procedures, views to Azure SQL Cloud. Schema & With Schema & Data option is provided to export the local SQL database to Azure. With this utility database of large size can be easily migrated to Azure in a very short period.
There are various users who are searching for a way to migrate a large SQL Server database to Azure. But, they are unable to find a reliable solution for the same. Therefore, considering these things we have discussed two best possible solutions for the same. However, to carry out the migration process using the Data Migration Assistant tool is quite lengthy. Therefore, it would be the best option to select the software by SysTools to perform SQL Server on-premise to Azure migration.