In this article, we will discuss a number of questions that you may face when being interviewed to a Microsoft Azure Data Engineer position. These questions will focus on the process of migrating the SQL Server databases from the on-premises instances to the Microsoft Azure database services.
Before discussing the interview questions and answers, it is better to show briefly what the difference between the database administrator and the Microsoft Azure Data Engineer positions is. Simply, when moving from the on-premises SQL Server databases administration to administrating Azure database platforms, known as the Data Engineer, you will extend your responsibilities to administrate the unstructured data and new data types such as streaming data. It requires also learning how to use new set of tools, platforms, additional technologies like HDInsight and Cosmos DB, and languages such as Hive or Python. For more information about how to start your path as a data engineer, check the Data Engineer certification path.
Q1: You are working as a data engineer in a company. You are requested to migrate four databases to Microsoft Azure. These databases contain cross-database queries that retrieve data from the other databases. What is the target Azure data platform that you will use to meet your requirements?
SQL Server on Azure Virtual Machine is the best option that helps in migrating multiple databases that can interact together using cross-database queries.
Q2: As a data engineer in an international company, you are requested to migrate 18 OLTP databases to Microsoft Azure. During most of the weekdays, the databases utilization is not high. But during few days from each week, a huge number of records will be loaded to these databases, requiring additional resources to load and process the data. What is the target Azure data platform that you will use to meet your requirements?
Azure SQL Database Elastic Pools is the best choice for the hosting databases with unstable workload in Azure.
Q3: Before migrating your database to Microsoft Azure, you need a tool to draw an inventory for you SQL servers, the servers configurations and the services installed in each server. Which tool will meet your requirements?
The Microsoft Assessment and Planning Toolkit.
Q4: You are working as a data engineer in an international company. You are using the Microsoft Assessment and Planning Toolkit to generate an inventory for your on-premises servers before migrating the database hosted in these servers to Microsoft Azure. When you reviewed the inventory, it is found that some servers are missed from the list. What could be the reason behind missing these servers?
The servers could be missed from the list because the Remote Administration is not enabled on these servers or these servers are not within the scope of the used discovery method.
Q5: You are trying to use the Data Migration Assistant tool to assess more than 35 databases hosted in an on-premises SQL Server instance before migrated it to the Microsoft Azure database platform, but the assessment process timed out. How could you mitigate that timeout issue?
You should change the assessment parallelDatabases option from the dma.exe.config configuration file from 8 to 1.
Q6: You are trying to use the Data Migration Assistant tool to assess your databases that are hosted in an on-premises SQL Server instance before migrated it to the Microsoft Azure database platform, but the assessment process failed with a permission error. How could you mitigate that permission issue?
Add the user who is running the Data Migration Assistant tool to the sysadmin SQL Server fixed role.
Q7: Working as a data engineer in an international company that has a Microsoft Enterprise Agreement including all SQL Servers hosted in your network, you plan to migrate these SQL Server databases to a SQL Server instance hosted in an Azure VM. What is the suitable license mode for the SQL Server on Azure VM platform that meets your scenario?
Bring Your Own License (BYOL) mode.
Q8: You are trying to migrate a large SQL Server database that is hosted in an on-premises server to a SQL Server instance that hosted in an Azure VM. You plan to use the Backup and Restore migration method and the expected size of the compressed backup file is 1.5TB. which disk volume type should be used to host the generated backup?
As the maximum size of the VM disk is 1 TB, you should use stripped disk volume for any backup file larger than 1 TB.
Q9: You are working as a data engineer in a company. You are requested to migrate two business-critical SQL Server databases that are hosted in a SQL Server 2008 R2 and SQL Server 2012 instances to Microsoft Azure with the minimal possible downtime. Which method will you use to meet your requirements?
You can use the Transactional Replication method to migrate the database hosted in the SQL Server 2008 R2 instance and the Always On Availability Group for the database hosted in the SQL Server 2012 instance to Microsoft Azure with minimal downtime.
Q10: You are preparing a plan to migrate your SQL Server databases to Microsoft Azure. Before migrating it, you need to assess these databases for any compatibility or migration blocking issue. Which tool will you use to meet your requirements?
The Data Migration Assistant tool.
Q11: You are trying to restore a backup file taken two weeks ago from the Azure SQL Database, deployed using Standard service tier. Will you be able to restore that backup?
Yes, as backup file that is automatically taken from Azure SQL Database can be kept up to 35 days.
Q12: You are asked to migrate your SQL Server databases hosted in on-premises servers to Azure SQL Database Managed Instance. What is the lowest compatibility level for the databases that will be considered in that migration project?
SQL Server 2008 with compatibility level 100 is the lowest supported version in Azure SQL Database Managed Instance.
Q13: You are drawing a plan to migrate your SQL Server databases to Microsoft Azure. In your plan, you need to decide which option can be used to create a private and secure connection between your on-premises infrastructure and the Azure datacenters. Which networking option will meet your requirements?
ExpressRoute is the best option for building a private and secure connection between on-premises and Azure.
Q14: You are planning to migrate your SQL Server database from an on-premises SQL Server instance to Azure SQL Database. While preparing for the migration process, you find that the SQL Agent Jobs that are scheduled on the source database and should be scheduled on the database after the migration. How you could mitigate that issue in Azure?
As the SQL Agent jobs are not supported in Azure SQL Database, you can use the Azure Automation and Elastic Database jobs to schedule your tasks in Azure.
Q15:While trying to migrate you databases from an on-premises SQL Server instance to an Azure SQL Database Managed Instance using Backup/ Restore from URL, you find that you are not able to connect to the provisioned Azure SQL Database Managed Instance using the SQL Server Managed Studio installed in your machine, in order to restore the database backup. How could you mitigate that issue?
As the Azure SQL Database Managed Instance is securely isolated, there are two methods that can be used to connect to it: configuring a point-to-site VPN connection from on-premises to Azure MI or installing a new Virtual Machine in the same VNET of the Azure SQL Database MI under another subnet.
Table of contents
| It is time to specify your Microsoft Certifications path |
| Data Engineer Interview Questions and Answers: SQL Workload Migration to Microsoft Azure Database Platforms |
| How to prepare for the Exam AZ-900: Microsoft Azure Fundamentals |
| How to prepare for the Exam DP-300: Administering Relational Databases on Microsoft Azure |
| How to prepare for the Exam DP-200: Implementing an Azure Data Solution |
| How to prepare for the Exam DP-201: Designing an Azure Data Solution |
| How to prepare for the Exam AZ-104: Microsoft Azure Administrator |
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021