Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool
Contents
- Introduction
- Microsoft Database Migration Assistant (DMA) tool
- Migrate SQL Server database To Azure SQL Instance using DMA tool
- Detecting the compatibility issues using Assessment Type project
- Migrating the database using the Migration project type
- Verifying the migrated database on Azure SQL Instance
- Summary
Introduction
In my previous articles on migrating On premise SQL Database to Azure SQL Database, I have already discussed two migration methods using SSMS Deploy & SSMS Export/Import Methods and their step by step process. Now, we will discuss the 3rd method of migrating the On Premise SQL Database to Azure SQL Database. In this article we learn migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool.
Microsoft Database Migration Assistant (DMA) tool
Microsoft Database Migration Assistant (DMA) tool helps in detecting compatibility issues & features which are not supported or partially supported by the Target server (Azure SQL Instance). This issues can impact database functionality on the target server. DMA has 2 project types. First is Assessment Project type. This section helps in getting the report on compatibility issues and the SQL Server features which are not supported or partially supported on the target server ( Azure ). The second project type is Migration which migrates the database from On premise SQL database to Azure SQL database. It not only allows migrating the data & Schema but also uncontained SQL objects from Source Server (On premise SQL Server) to the target server (Azure SQL database).
Download Microsoft Database Migration Assistant (DMA) tool
You can download the from the below mentioned Microsoft link
https://www.microsoft.com/en-us/download/details.aspx?id=53595
You can learn more about DMA tool from here
let start discussing the step by step process of database migration to Azure SQL Instance using (DMA) tool
Migrate SQL Server database To Azure SQL Instance using DMA tool
After downloading and installing the DMA tool, when you open it, you will get the following screen.
Figure-1 Welcome to Data Migration Assistant
In this screen click the “+”to start the new project. It will bring the below screen. By default Assessment Project type is selected.
Figure-2 Choose the project type
Detecting the compatibility issues using Assessment Type project
Figure-3 Define Project Type, Project Name, Source Server & Target Server
In the above screen, for detecting the compatibility issues, choose the Radio button Assessment (If not already selected). After choosing the project type, define the project name, source server name and the target server name. After that click the Create button. It will open the below screen:
Figure-4 Select the report options.
In the above screen we need to select the report type options. Since I want to check the compatibility issues as well as features which are either not supported or partially supported, I will go with the first option Check database compatibility as well as second option Check feature parity.
Once you select the report options click on the Next button. It will bring the below screen
Figure-5 Connect to the Source SQL Server
In the above screen, enter the server name & the required login information for connecting it. Then click on the Connect button to connect to the source SQL Server. It will take us to the below screen.
Figure-6 Select the SQL database for migration
This screen contains all the database from the source SQL Server. Here we need to select the database which we want to migrate. For this article, we choose the database AdventureWorks2012. Once we choose the database, click on the Add button which will bring up the following screen.
Figure-7 Start Assessment
In the above screen, you can see the selected SQL database, its compatibility level & its size. Now click on the Start Assessment button. It will start the assessment process as show in the below mentioned screenshot.
Figure-8 Assessment in process…
After completing the assessment process, it will bring the below screen. By default SQL Server feature parity radio option is selected. This screen show the features which are either not supported by the Azure SQL database or features which are partially supported by Azure SQL Database.
Figure-9 Review result for Unsupported & Partially Supported issues
Now, if you chose the radio button option Compatibility issues, it will the below screen. This screen will show all the compatibility issues with their details which may impact the migration process.
Figure-10 Review result for Compatibility Issues
Now click on the Export Report button and it will asked you to save the report. For example, in this case the report will be look like this
Figure-11 Assessment Report
Now once you click on the icon below “+”, you will get the below screen where you can see all the assessments.
Figure-12 All Assessments list
With this, the assessment part is complete. In the next section we will going to discuss how we can do the actual migration using the Microsoft Database Migration Assistant tool ‘s Migration project type..
Migrating the database using the Migration project type
Click on the “+” sign to start new project.
Figure-13 Select Migration Project Type
Now select the Migration Project type and defined the project name. Also select the Source Server Type, Target Server type and the scope of the migration. In this article, I have chosen the Schema & data as migration scope.
After that click the Create button. It will bring up the “Connect to source server” screen.
Figure-14 Select Source Server
In this screen, fill the required connection information of the source server as shown in the above screen and click on the Connect button. It will take us to the database selection screen as shown below
Figure-15 Select the source database
For this article, I have chosen the database AdventureWorks2012. Click Next to continue.. It will bring us to the target server selection screen.
Figure-16 Choose the Target Azure SQL Server
In this screen choose the Target Azure SQL Instance and fill the required credential information for connecting to the Azure SQL Instance. After filling the required credential information, click the Connect button. In the next screen, we need to select the database from the list of databases in the target Azure SQL Instance. Since there is no database available in the Azure SQL Instance. So we need to create it.
We can create new Azure SQL Database through multiple ways like through Azure Portal, using SQL Server Management Studio or by clicking the link Create a new Azure SQL Database from the above screen. In this article I will create the new Azure SQL database using the SQL Server Management Studio.
For creating a new azure SQL database, first connect to the Azure SQL instance using SQL Server Management studio. If you don’t know how to connect with Azure SQL Instance using SSMS, this article can help you. Once connected, right-click on the Databases node and select the option New database. It will bring the below mentioned screen
Figure-17 Create the new database
In the Database name field, define the new database name. In this article, I have define the name as AdventureWorks2012. Now click on the options tab. It will bring the below mentioned screen
Figure-18 Select setting for new Azure SQL database
If you notice in the above screen, S2 Service tier is selected by default. We can change the service tier according to our database performance & size requirements. If you want to read more about Azure service tiers, you can refer to the article ” Azure Pricing Models and Service Tiers “. In this article I have change the Service tier to S0 as shown in the below screenshot.
Figure-19 Change the Service tier for the new database
Click OK. This will create the new blank SQL database in the Azure SQL Instance. Now if we again return to the Screen where we have made the connection with the Azure SQL Instance (Figure-16) and click Connect, it will show the newly created database AdventureWorks2012. Since it is the only database in the Azure SQL Instance, it is by default selected.
Figure-20 Select the target database on Azure SQL Instance
Click on the Next button. It will bring the below screen. In this screen we need to select the schema objects which we want to migrate from source database.
Figure-21 Select the Schema objects for migration
Once the schema objects got selected, click on the Generate SQL Script. It will bring the screen Script & deploy Schema. In this screen you can see the SQL script generated for the Schema Objects.
Figure-22 Generating SQL scripts for selected schema objects
Once the SQL Script for Schema objects generated, click on the Deploy Schema button. It will start the process of deploying the selected Schema objects on the target Azure SQL Instance.
Figure-23 Deploying selected schema objects on target Azure SQL database
Once this process got completed, click on the Migrate data button. It will bring the Select tables screen. In this screen, select the tables which we wish to migrate to the Azure SQL Instance.
Figure-24 Select the tables for migration
After selecting the tables, click on the Start data migration button. It will take to the Migrate data screen. Here we can see the tables data migration process as shown in the below screenshot.
Figure-25 Selected tables data migration in progress
Once this data migration process got completed successfully, you can see the final summary & total time taken by it to complete the data migration process as shown in the below screenshot.
Figure-26 Completion of table data migration process
Verifying the migrated database on Azure SQL Instance
To check the data migration happened successfully or not, you can connect to the Azure SQL Instance using SQL Server management studio (SSMS) and expand the node Databases. It will show the database AdventureWorks2012. Now expands it. There you can see all the tables & other schema objects which shows the success of the migration process.
Figure-27 Tables in the migrated database in Azure SQL Instance
If you want to check the migrated data within the tables, you can execute the Select command to see data from any of the table as shown in the below screenshot. It will verify the success of the table data migration.
Figure-28 Verifying table data by executing Select command
Summary
In continuation on the series of articles on migrating On premise SQL Database to Azure SQL Database, this article discussed about migrating SQL Server Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool. The link of the other 2 methods has been mentioned under the Read More section. Microsoft provides this migration tool and have two project types. Assessment & Migration.
Using Assessment project type we can know the compatibility issues and the features which are either not supported or partially supported by the Azure SQL Instance before starting the actual migration process. Since these issues can have an impact during the migration process, we have the opportunity to resolve these issues and then start the actual migration process.
Migration project type is use for the actual migration process. Here we can choose the schema objects & tables, which we need to migrate and monitor the migration process.
I hope this article will help you in migrating the SQL Server database to the Azure SQL Instance. Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com. If you want you can also join our Facebook community here or follow me on Twitter
Read More
How to migrate SQL Database to Azure SQL Database using SSMS Export/Import
Migrating SQL Database to Azure SQL Database using SSMS Deploy
Understanding Azure SQL Database- Introduction
Azure Pricing Models and Service Tiers
Azure – Creating an Azure SQL Database
How to connect with Azure SQL Database
Read more from Tutorials
SQL Tutorial
Azure Tutorial
The post Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool appeared first on Technology with Vivek Johari.
