Data can be replicated one way or bidirectional. To create a sync group, Navigate to All resources page or SQL databases page and click on the database which will act as a hub database.
In the database details page, click on Sync to other databases and click on New Sync Group as shown in the below image. Enter the name of the sync group and choose the Sync Metadata Database. If you choose New database, then a new database is created on the server you choose with tables that store the sync information.
If you choose to use an existing database, all the available databases on the server are shown in the drop-down and you must select one. The tables are created in the database you selected to store sync information. Set Automatic sync On and set the frequency to sync the data changes automatically at a specified interval. Conflict resolution: Conflict occurs when the data is modified on the Azure SQL hub and member database within the same sync cycle.
Conflict resolution helps which change needs to be persisted. We have only two options available as of now unlike in SQL Server replication. We cannot have a custom conflict resolver to resolve conflict when it occurs. If you choose Hub to win as Conflict resolution, then the change from Hub database is persisted.
If you choose Member to win as conflict resolution, then the change from the member database is persisted when a conflict arises. Once the sync group is created, we should add the Azure SQL database as a member of the sync group. To add a member database, Click on Add sync members. Enter the username and password of the hub database. Enter the name of the sync member and select the database. Select the sync direction and enter the username, password of a member database as shown in the below image.
To add the tables to a sync group, click on the configure sync group as shown in the below image and select the Hub database. Click on Refresh Schema to list the tables in the hub database. Once you click on Savethe selected tables are added to the sync group and three triggers one for insert, one for delete and one for the update are created on each table to track the data changes and insert information related to the data changes in the tracking tables.
The naming convention of these tracking tables is as below.Here are the main use cases for Data Sync:. 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.
If you're using an on premises database as a member database, you have to install and configure a local sync agent.
Azure SQL Data Sync – Replicate data and schema changes between Azure SQL databases
Since Data Sync is trigger-based, transactional consistency isn't guaranteed. Microsoft guarantees that all changes are made eventually and that Data Sync doesn't cause data loss.Office 365 Education – Microsoft Classroom and School Data Sync
Data Sync uses insert, update, and delete triggers to track changes. It creates side tables in the user database for change tracking. These change tracking activities have an impact on your database workload. Assess your service tier and upgrade if needed. Provisioning and deprovisioning during sync group creation, update, and deletion may also impact the database performance. There may be up to 30 endpoints in a single sync group if there is only one sync group.
If there is more than one sync group, the total number of endpoints across all sync groups cannot exceed If a database belongs to multiple sync groups, it is counted as multiple endpoints, not one.
However, you still collect data transfer charges for data movement in and out of your SQL Database instance. For more info, see SQL Database pricing.Set up synchronization. In the browser, navigate to Azure portaland locate the SQL databases tab:. Click on the Sync to other databases command and the Data Sync page will open in which the configuration of data synchronization will be started:.
As it can be seen on this page, currently there is no any sync group and any sync agent. The sync agent needs to be installed on-premises database server. More about sync agent will be discussed later in this article. To start configuration, click the New Sync Group button and the New sync group page opens:. In the Sync Group Name box, enter the name for the new sync group e.
Under the Sync Metadata Database group, choose the New database or Using existing database radio button. For this example, the New database radio button will be used to create a new database in which will be placed sync metadata and logs.
In the Name box, enter a new name Sync Database for the sync metadata database, configure the other options on this page and press the OK button. Under the Automatic Sync section, choose whether the data synchronization should be automatic — by pressing the On button or manually — by pressing the Off button.
If the On button is pressed, the Sync Frequency section will appear. Here can be set how frequent the data synchronization will occur:. Under the Conflict Resolution, one of two options can be chosen in case a conflict occurs:. The first option is the Hub win. If this option is chosen and the confits occur, the data in the hub database overwrite the conflicting data in the member database.
The second option is Member win. In a conflict situation, the data in the member database will overwrite the data in the hub database. As an example, in the Conflict Resolution, drop down box the Hub win option will be chosen.
After creating sync group, the Add sync members section will be enabled for configuration. If the local sync agent is already installed and set on the machine, on the Select Sync Agent tab, pick the Existing agents radio button and from the combo box choose the agent.
In case where the sync agent is not set, choose the Create a new agent radio button. Additional three steps under the Select Sync Agent tab will appear:.For sync setup. For ongoing sync. For deprovisioning. Azure SQL Database supports only a single set of credentials. To accomplish these tasks within this constraint, consider the following options:.
When you create a new SQL Database instance, set the maximum size so that it's always larger than the database you deploy. If you don't set the maximum size to larger than the deployed database, sync fails. Ensure that you stay within the SQL Database instance size limits. SQL Data Sync stores additional metadata with each database. Ensure that you account for this metadata when you calculate space needed. The amount of added overhead is related to the width of the tables for example, narrow tables require more overhead and the amount of traffic.
You don't have to include all the tables that are in a database in a sync group. The tables that you include in a sync group affect efficiency and costs. Include tables, and the tables they are dependent on, in a sync group only if business needs require it. Each table in a sync group must have a primary key.
Empty tables provide the best performance at initialization time. If the target table is empty, Data Sync uses bulk insert to load the data. Otherwise, Data Sync does a row-by-row comparison and insertion to check for conflicts.
If performance is not a concern, however, you can set up sync between tables that already contain data. To minimize latency, keep the hub database close to the greatest concentration of the sync group's database traffic. Apply the preceding guidelines to complex sync group configurations, such as those that are a mix of enterprise-to-cloud and cloud-to-cloud scenarios. In this section, we discuss the initial sync of a sync group. Learn how to help prevent an initial sync from taking longer and being more costly than necessary.
When you create a sync group, start with data in only one database. If you have data in multiple databases, SQL Data Sync treats each row as a conflict that needs to be resolved. This conflict resolution causes the initial sync to go slowly. If you have data in multiple databases, initial sync might take between several days and several months, depending on the database size. If the databases are in different datacenters, each row must travel between the different datacenters.
This increases the cost of an initial sync. A sync loop occurs when there are circular references within a sync group. In that scenario, each change in one database is endlessly and circularly replicated through the databases in the sync group. Ensure that you avoid sync loops, because they cause performance degradation and might significantly increase costs.
These changes never propagate. The only way to recover in this scenario is to re-create the sync group. A sync group or a database in a sync group can become out of date. When a sync group's status is Out-of-dateit stops functioning.If there is a resolution for an issue, it's provided here.
Sync fails in the portal UI for on-premises databases that are associated with the client agent. My sync group is stuck in the processing state. I see inconsistent primary key data after a successful sync.
I see a significant degradation in performance. Column does not allow nulls. How does Data Sync handle circular references? That is, when the same data is synced in multiple sync groups, and keeps changing as a result?
Troubleshoot issues with SQL Data Sync
On the local computer that's running the agent, you see System. IOException errors in the Event Log. The errors say that the disk has insufficient space.
It doesn't respond to the stop command, and the logs show no new entries. Any of the following conditions might result in a sync group being stuck in the processing state:. If the preceding information doesn't move your sync group out of the processing state, Microsoft Support can reset the status of your sync group. In the post, include your subscription ID and the sync group ID for the group that needs to be reset. A Microsoft Support engineer will respond to your post, and will let you know when the status has been reset.
If tables that have the same name but which are from different database schemas are included in a sync, you see erroneous data in the tables after the sync. The SQL Data Sync provisioning process uses the same tracking tables for tables that have the same name but which are in different schemas.
Because of this, changes from both tables are reflected in the same tracking table. This causes erroneous data changes during sync. Ensure that the names of tables that are involved in a sync are different, even if the tables belong to different schemas in a database.
A sync is reported as successful, and the log shows no failed or skipped rows, but you observe that primary key data is inconsistent among the databases in the sync group.
This result is by design. Changes in any primary key column result in inconsistent data in the rows where the primary key was changed.
To prevent this issue, ensure that no data in a primary key column is changed. To fix this issue after it has occurred, delete the row that has inconsistent data from all endpoints in the sync group. Then, reinsert the row. Your performance degrades significantly, possibly to the point where you can't even open the Data Sync UI.
The most likely cause is a sync loop. A sync loop occurs when a sync by sync group A triggers a sync by sync group B, which then triggers a sync by sync group A. The actual situation might be more complex, and it might involve more than two sync groups in the loop.
The issue is that there is a circular triggering of syncing that's caused by sync groups overlapping one another. The best fix is prevention. Ensure that you don't have circular references in your sync groups.
Any row that is synced by one sync group can't be synced by another sync group.GitHub is home to over 40 million developers working together to host and review code, manage projects, and build software together.
For sync setup. For ongoing sync. For deprovisioning. Azure SQL Database supports only a single set of credentials. To accomplish these tasks within this constraint, consider the following options:.
When you create a new SQL Database instance, set the maximum size so that it's always larger than the database you deploy. If you don't set the maximum size to larger than the deployed database, sync fails. Ensure that you stay within the SQL Database instance size limits. Ensure that you account for this metadata when you calculate space needed. The amount of added overhead is related to the width of the tables for example, narrow tables require more overhead and the amount of traffic. You don't have to include all the tables that are in a database in a sync group.
The tables that you include in a sync group affect efficiency and costs. Include tables, and the tables they are dependent on, in a sync group only if business needs require it. Each table in a sync group must have a primary key. Empty tables provide the best performance at initialization time.
If the target table is empty, Data Sync uses bulk insert to load the data. Otherwise, Data Sync does a row-by-row comparison and insertion to check for conflicts. If performance is not a concern, however, you can set up sync between tables that already contain data. To minimize latency, keep the hub database close to the greatest concentration of the sync group's database traffic. Apply the preceding guidelines to complex sync group configurations, such as those that are a mix of enterprise-to-cloud and cloud-to-cloud scenarios.
In this section, we discuss the initial sync of a sync group.
Learn how to help prevent an initial sync from taking longer and being more costly than necessary. When you create a sync group, start with data in only one database.
If you have data in multiple databases, SQL Data Sync treats each row as a conflict that needs to be resolved. This conflict resolution causes the initial sync to go slowly. If you have data in multiple databases, initial sync might take between several days and several months, depending on the database size.The sync group is custom configured and synchronizes on the schedule you set.
Go to the Azure portal to find your SQL database. Search for and select SQL databases. The hub database is a sync topology's central endpoint, in which a sync group has multiple database endpoints. All other member databases with endpoints in the sync group, sync with the hub database.
On the SQL database menu for the selected database, select Sync to other databases. On the Sync to other databases page, select New Sync Group. The New sync group page opens with Create sync group step 1 highlighted. 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. This database is shared as the Sync Metadata Database for all Sync Groups in a selected region and you can't change the database or its name without removing all Sync Groups and Sync Agents in the region.
After the new sync group is created and deployed, Add sync members step 2 is highlighted on the New sync group page. In the Hub Database section, enter existing credentials for the SQL Database server on which the hub database is located.
Don't enter new credentials in this section. The Configure On-Premises page opens where you can do the following things:.
Select Choose the Sync Agent Gateway. The Select Sync Agent page opens. On the Choose the Sync Agent page, choose whether to use an existing agent or create an agent. Download the data sync agent from the link provided and install it on the computer where the SQL Server is located.
You have to open outbound TCP port in the firewall to let the client agent communicate with the server. In the sync agent app, select Submit Agent Key. The Sync Metadata Database Configuration dialog box opens. In the Sync Metadata Database Configuration dialog box, paste in the agent key copied from the Azure portal.
Also provide the existing credentials for the Azure SQL Database server on which the metadata database is located. If you created a metadata database, this database is on the same server as the hub database. Select OK and wait for the configuration to finish. If you get a firewall error, create a firewall rule on Azure to allow incoming traffic from the SQL Server computer. If you choose SQL Server authentication, enter the existing credentials. Provide the SQL Server name and the name of the database that you want to sync and select Test connection to test your settings.
Then select Save and the registered database appears in the list. This name is distinct from the name of the database itself. Select the database from the list. Select OK to close the Select Database page.
Then select OK to close the Configure On-Premises page and wait for the new sync member to be created and deployed. Finally, select OK to close the Select sync members page. After the new sync group members are created and deployed, Configure sync group step 3 is highlighted in the New sync group page.
Best practices for SQL Data Sync
On the Tables page, select a database from the list of sync group members and select Refresh schema. From the list, select the tables you want to sync. By default, all columns are selected, so disable the checkbox for the columns you don't want to sync.