Microsoft Certified Azure Data Engineer Associate · Free Practice Question Medium
Question 51
Scenario: You have been contracted by Wayne Enterprises, a company owned by Bruce Wayne with market value of over twenty seven million dollars. Bruce founded Wayne Enterprises shortly after he created the Wayne Foundation and he became the president and chairman of the company.
Bruce has come to you because his IT team needs advice on the configuration and synchronization of data between an on-premises Microsoft SQL Server database to Azure SQL Database.
Recently, ad-hoc and reporting queries are being overutilized on the on-premises production instance and your expert advise is required on the following points.
Requirements:
Execute an initial data synchronization to Azure SQL Database (minimize downtime)
Execute bi-directional data synchronization after initial synchronization
A synchronization solution must be created and implemented and Bruce and the team look to you as the Azure expert. Which synchronization method should you advise the team to use?
-
A
Transactional replication
-
B
Backup and restore
-
C
Azure SQL Data Sync
-
D
SQL Server Agent job
-
E
Data Migration Assistant
Reveal correct answer
Correct answer: C
Explanation
SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple databases, both on-premises and in the cloud.
Data Sync is based around the concept of a sync group. A sync group is a group of databases that you want to synchronize.
Data Sync uses a hub and spoke topology to synchronize data. You define one of the databases in the sync group as the hub database. The rest of the databases are member databases. Sync occurs only between the hub and individual members.
The Hub Database must be an Azure SQL Database.
The member databases can be either databases in Azure SQL Database or in instances of SQL Server.
The Sync Metadata Database contains the metadata and log for Data Sync. The Sync Metadata Database has to be an Azure SQL Database located in the same region as the Hub Database. The Sync Metadata Database is customer created and customer owned. You can only have one Sync Metadata Database per region and subscription. Sync Metadata Database cannot be deleted or renamed while sync groups or sync agents exist. Microsoft recommends to create a new, empty database for use as the Sync Metadata Database. Data Sync creates tables in this database and runs a frequent workload.
A sync group has the following properties:
The Sync Schema describes which data is being synchronized.
The Sync Direction can be bi-directional or can flow in only one direction. That is, the Sync Direction can be Hub to Member, or Member to Hub, or both.
The Sync Interval describes how often synchronization occurs.
The Conflict Resolution Policy is a group level policy, which can be Hub wins or Member wins.
Server instances.
With Data Sync, you can keep data synchronized between your on-premises databases and Azure SQL databases to enable hybrid applications. Here are the main use cases for Data Sync:
Hybrid Data Synchronization: With Data Sync, you can keep data synchronized between your databases in SQL Server and Azure SQL Database to enable hybrid applications. This capability may appeal to customers who are considering moving to the cloud and would like to put some of their application in Azure.
Distributed Applications: In many cases, it's beneficial to separate different workloads across different databases. For example, if you have a large production database, but you also need to run a reporting or analytics workload on this data, it's helpful to have a second database for this additional workload. This approach minimizes the performance impact on your production workload. You can use Data Sync to keep these two databases synchronized.
Globally Distributed Applications: Many businesses span several regions and even several countries/regions. To minimize network latency, it's best to have your data in a region close to you. With Data Sync, you can easily keep databases in regions around the world synchronized.
Compare Data Sync with Transactional Replication
A. Transactional replication is not the best choice for bidirectional data synchronization in this scenario. While it can be used for replicating data changes from on-premises SQL Server to Azure SQL Database, it may not be the most efficient solution for maintaining data consistency and minimizing downtime.
B. Backup and restore is a method used for creating backups of databases and restoring them to another environment. It is not suitable for bidirectional data synchronization or maintaining data consistency between on-premises SQL Server and Azure SQL Database.
C. Azure SQL Data Sync is the correct choice for this scenario as it allows for bidirectional data synchronization between on-premises SQL Server databases and Azure SQL Database. It can be used to minimize downtime during the initial data synchronization and to maintain data consistency between the two environments.
D. SQL Server Agent job is a tool used for scheduling and automating administrative tasks in SQL Server. While it can be used to automate data synchronization tasks, it may not provide the bidirectional synchronization capabilities required in this scenario. Azure SQL Data Sync would be a more suitable choice for this specific requirement.
E. Data Migration Assistant is primarily used for assessing and migrating on-premises SQL Server databases to Azure SQL Database. It is not designed for bidirectional data synchronization or ongoing data consistency between the two environments.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
