Setting up SQL Data Sync for Azure is an important skill for any Database Administrator (DBA), especially for the ones preparing for the DP-300: Administering Microsoft Azure SQL Solutions exam. This article will guide you on how to setup SQL Data Sync, using the latest release from Azure and offer tips for passing the DP-300 exam.
Azure 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. The need for data sync might arise if you want to separate workloads across databases, either for performance or for business purposes, while keeping data consistent across all databases.
Prerequisites
Before you set up the Azure SQL Data Sync, make sure you have the following:
- An active Azure account
- A SQL Server databases, either hosted on-premise or on Azure
- Appropriate permissions to setup Data Sync on SQL Servers
Step by Step configuration of Data Sync
Step 1: Setting up the Data Sync
The first step is to initiate the SQL Data Sync in Azure. You can do so by:
- Logging into the Azure portal
- Navigate to the SQL database you want to sync.
- In the menu panel, under the settings option click on the ‘Data Sync’ button.
Step 2: Add sync group
Upon clicking on the ‘Data Sync’ button you will be redirected to ‘Sync to other databases’ window. Now, click on the ‘New Sync Group’ button. Sync group is needed as it defines the sync topology, which includes the databases, conflict resolution setting, and other options.
You need to provide details such as:
- Sync group name.
- Hub database: The database where data from other databases is aggregated and synchronized. Confirm the database is not already a member of another sync group.
- Sync Metadata Database: Database used to store metadata and logs for the Data Sync. Select the server and database.
- Conflict resolution: Select from ‘Hub win’ or ‘Member win’.
After the details are filled in, click on ‘Create’.
Step 3: Add Sync Members
Next, add the Sync members. Under the ‘Sync members’ tab, click on ‘Add Sync Members’. You will see a new window where you need to add the type of database (Azure / On-premise), the sync direction (Bi-directional / To the Hub / From the Hub), and provide other details like server name and username. You may add as many sync members as required.
Step 4: Selecting the tables for Sync
Now comes the step of selecting which tables and columns you want to synchronize. In the tables section, you can add or remove tables and columns as per your needs.
Step 5: Sync Group Configuration
After setting up the sync members and tables, you need to configure the sync group. Under the manual sync section, you can trigger a sync manually or set up a sync schedule as needed.
Sync Group Elements
Data Sync uses the following elements:
- Sync group – A group of databases that you want to synchronize.
- Hub database – A database in the sync group that serves as the focal point of the sync. All changes in the sync group are pushed to other databases from the hub.
- Member database – A database in a sync group that syncs with the hub database. Sync can be bi-directional or in either direction.
- Sync schema – A database schema of a set of tables you want to sync.
- DMC (Data Sync Metadata Cleanup) – A stored procedure for cleaning up metadata for rows that have been hard-deleted in the user database.
By understanding these steps deeply and practicing them, questions around Azure SQL Data Sync in DP-300 exam can be tackled easily. It’s also recommended to familiarize yourself with various other advanced scenarios like filtering rows and columns, resolving conflicts, monitoring, and troubleshooting data sync, as they can be part of the exam.
The theoretical knowledge coupled with practical hands-on experience on Azure SQL Data Sync will ensure that you have a solid understanding of the concepts, thereby enhancing your chances of cracking the DP-300: Administering Microsoft Azure SQL Solutions exam.
Practice Test
True or False: Data Sync in Azure SQL Database 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.
- True
- False
Answer: True
Explanation: This is true. Data Sync allows you to synchronize data in a hybrid SQL Server environment and between Azure SQL databases.
True or False: For Azure SQL Data Sync, the sync group should contain less than two databases.
- True
- False
Answer: False
Explanation: A sync group must contain more than one database. It includes one hub database and one or more member databases.
Which of the following can be the hub database in a sync group for Azure SQL Data Sync?
- a) An Azure SQL Database hosted on a VM
- b) An on-premise SQL Server database
- c) Any SQL database
Answer: a) An Azure SQL Database hosted on a VM
Explanation: In Azure SQL Data Sync, the hub database is an Azure SQL Database.
Can you use a service like Azure SQL Data Sync to replicate data between databases for disaster recovery?
- a) Yes
- b) No
Answer: b) No
Explanation: Azure SQL Data Sync should not be used for disaster recovery as it is not designed to replicate the full schema or full database.
True or False: The member databases in a sync group for Azure SQL Data Sync can be in different regions.
- True
- False
Answer: True
Explanation: The member databases in a sync group can be anywhere. They can either be on-premises or in the cloud in different regions.
Does Azure SQL Data Sync support database schema synchronization?
- a) Yes
- b) No
Answer: b) No
Explanation: Azure SQL Data Sync doesn’t synchronize database schema, it only synchronizes data.
What should be your first step in setting up SQL Data Sync?
- a) Enable Firewall rules
- b) Register database
- c) Create a sync group
Answer: a) Enable Firewall rules
Explanation: Initially, we ensure that Azure SQL Data Sync can connect to the databases by enabling necessary firewall rules.
When using Azure SQL Data Sync, do the changes need to be manually synced every time?
- a) Yes
- b) No
Answer: b) No
Explanation: Azure SQL Data Sync allows automatic synchronizations based on the schedule you define.
True or False: Azure SQL Data Sync actively replicates identity columns.
- True
- False
Answer: False
Explanation: Azure SQL Data Sync does not actively replicate identity columns. It treats them as normal integer columns.
Can Azure SQL Data Sync service be used for databases in Azure-only environment?
- a) Yes
- b) No
Answer: a) Yes
Explanation: Azure SQL Data Sync service supports hybrid environments (Azure and on-premises databases) as well as Azure-only environments.
Which of the following operations can trigger a conflict in Azure SQL data Sync?
- a) Insert-Insert
- b) Update-update
- c) Delete-Delete
- d) All of the above
Answer: d) All of the above
Explanation: All these operations can generate conflicts in Azure SQL Data Sync.
True or False: The main use case of Azure SQL Data Sync is to distribute data to multiple locations around the world for local access and changes.
- True
- False
Answer: True
Explanation: This is accurate. This helps to reduce latency in accessing data, and such local data access can help to maintain compliance with data sovereignty requirements.
Where in Azure portal can you set up Azure SQL Data Sync?
- a) The Azure SQL Database blade
- b) The Data Factory blade
- c) The Logic Apps blade
Answer: a) The Azure SQL Database blade
Explanation: Azure SQL Data Sync is an feature of Azure SQL Database and its set up is available in the Azure SQL Database blade of the Azure portal.
True or False: Azure SQL Data Sync supports bi-directional data synchronization and filtering of data.
- True
- False
Answer: True
Explanation: Azure SQL Data Sync enables bi-directional synchronization and furthermore allows filtered synchronization to selected columns and rows.
Is it necessary to define a sync schema while setting up Azure SQL Data Sync?
- a) Yes
- b) No
Answer: a) Yes
Explanation: Defining a sync schema is a mandatory step. It specifies the tables, columns, and rows you want to sync.
Interview Questions
What is SQL Data Sync in Azure?
SQL Data Sync is a service built on Azure SQL Database that enables you to synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.
What are the key features of SQL Data Sync?
SQL Data Sync allows for both one-way and bi-directional data synchronization, scheduling of synchronizations to run on your timetable, an ability to sync with on-premises SQL Server databases and other SQL Database instances, and filtering to sync a subset of your data.
What is a Sync Group in Azure SQL Data Sync?
A Sync Group is a group of databases that you want to synchronize. A Sync Group defines the sync topology, i.e., its databases, sync direction and conflict resolution policy.
What does a Conflict Resolution Policy in SQL Data Sync specify?
A Conflict Resolution Policy in SQL Data Sync specifies how conflicts are resolved when the same row gets updated in two databases before sync.
What is the Hub Database in SQL Data Sync?
The Hub Database is an Azure SQL Database that sits in the center of a Sync Group. All data changes are synchronized with the hub and then propagated out to all other databases in the sync group.
Can SQL Data Sync be used to synchronize databases in different Azure regions?
Yes, SQL Data Sync can easily synchronize databases across different Azure regions.
Is SQL Data Sync supported in Azure SQL Managed Instance?
No, as of today, SQL Data Sync is not supported in Azure SQL Managed Instance.
What is the Member Database in SQL Data Sync?
Member Databases are the databases that receive data from or send data to the Hub Database. They can be other Azure SQL Databases, databases on an Azure SQL Managed Instance, or a SQL Server instance (on-premises or in an IaaS virtual machine).
Are there any particular permissions required to set up SQL Data Sync?
Yes, to setup SQL Data Sync, an account with the Azure SQL Server ‘dbmanager’ server role and SQL Server ‘db_datareader’ and ‘db_datawriter’ database roles are required.
What types of Sync Direction are there in SQL Data Sync?
SQL Data Sync supports three types of sync directions: To the Hub, From the Hub, and Bi-Directional.
Can you trigger a data synchronization manually in SQL Data Sync?
Yes, you can manually trigger data synchronization on a Sync Group in SQL Data Sync.
How does SQL Data Sync deal with column level conflicts during synchronization?
SQL Data Sync does not resolve column level conflicts. It treats a row as a unit for conflict detection and resolution.
Can SQL Data Sync handle schema changes done after setting up the Sync Group?
SQL Data Sync does not automatically propagate schema changes. Any schema changes will need to be handled manually.
Can SQL Data Sync be used with read replicas?
No, SQL Data Sync cannot be used with read replicas. It supports synchronizing data amongst a group of read-write databases.
What happens when a sync group is deleted?
When a sync group is deleted, all knowledge about previous synchronizations and sync group’s metadata from all the databases in the sync group is deleted. The data in the databases, however, is not deleted.
extutorials.com