Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool
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.
In this screen click the “+”to start the new project. It will bring the below screen. By default Assessment Project type is selected.
Detecting the compatibility issues using Assessment Type project
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:
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
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.
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.
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.
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.
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.
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
Now once you click on the icon below “+”, you will get the below screen where you can see all the assessments.
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.
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.
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
For this article, I have chosen the database AdventureWorks2012. Click Next to continue.. It will bring us to the target server selection screen.
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
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
Related Post
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
Related Posts
How to migrate SQL Database to Azure SQL Database using SSMS deploy
Understanding Azure SQL Database – Introduction
Azure – Creating an Azure SQL Database
About The Author
vivekjohari
I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com
Thanks for writing the complete migration steps with Snapshots, this is handy.
Please give details of other migration topics from SQL to AZURE
Hi Rohit,
Thanks for your feedback.
Except DMA tool which I discussed in this article, there are multiple ways to migrate the database from SQL to Azure like
1) By exporting the SQL database backup as a .bacpac file and then import it on Azure using SSMS export/import. You can read it step by step from my below article
How to migrate SQL Database to Azure SQL Database using SSMS Export/Import
2) Using the SSMS deploy wizard. You can read it step by step from my below article
Migrating SQL Database to Azure SQL Database using SSMS Deploy
3) Using the Transaction Replication method.
Thanks for the response
Nice tutorial
thanks for a nice article! just an other option that can be considered for Azure migration is MnMTK https://www.ispirer.com/microsoft-azure-sql-database-migration