SQL Data Sync Best Practices
Updated: August 28, 2015
Welcome to SQL Data Sync (Preview). This topic is the fifth and final of five topics you should be familiar with before you design and implement a data synchronization plan. The five preparatory topics are:
The Microsoft Azure SQL Data Sync plug-in on the Microsoft Azure Silverlight portal has been decommissioned. Going forward, use the Microsoft Azure Management portal, for Azure SQL Data Sync.
You access SQL Data Sync (Preview) via the SYNC tab under SQL Database at the Microsoft Azure Management portal. The SYNC tab is only available when you have one or more sync groups. See the topic How To: Create a Sync Group (SDS) for guidance on creating and modifying a sync group from this portal.
See the Navigation section below for links to topics you should read before you start and guidance on creating and modifying sync groups.
Install the client agent using the least privilege account with network service access.
It is best if the client agent is installed on a computer separate from your onpremises SQL Server computer.
Do not register an on-premises DB with more than one agent.
Even if syncing different tables for different sync groups.
Registering an on-premises database with multiple client agent poses challenges when deleting one of the sync groups.
- Even if syncing different tables for different sync groups.
For Sync Setup:
Create/Alter Table, Alter Database, Create Procedure, Select/ Alter Schema, Create User Defined Type.
For Ongoing Sync:
Select/ Insert/ Update/ Delete on tables selected for syncing and on sync metadata and tracking tables, Execute permission on stored procedures created by our service, Execute permission on user defined table types.
Alter on tables part of sync, Select/ Delete on Sync Metadata tables, Control on sync tracking Sync Tracking tables, stored procedures and user defined types
What to do with this information because there is only one credential for a DB in the Sync Group?
Change the credentials for different phases (e.g. cred1 for setup and cred2 for ongoing).
Change the permission of the credentials (e.g. change permission after sync is setup).
Enterprise to Cloud Scenario:
Keep your hub database close to the greatest concentration of the sync group’s database traffic to minimize latency.
Cloud to Cloud Scenario:
When all the databases in a sync group are in one data center the hub should be located in the same data center. This reduces latency and the cost of data transfer between data centers.
When the databases in a sync group are in multiple data centers the hub should be located in the same data center as most of the databases and database traffic.
Apply the above principles to more complex sync group configurations.
SQL Database instance Size
When you create a new SQL Database instance, if you select Custom Create, set the maximum size so that it is always larger than the database you deploy. If you do not set the maximum size larger than your deployed database, synchronization fails. While there is no automatic growth - you can do an ALTER DATABASE to increase the size of the database after it has been created. Of course if you must stay within the SQL Database instance size limits.
Important SQL Data Sync (Preview) stores additional metadata with your database. Be sure to account for this when you calculate space needed.
The amount of added overhead is governed by the width of your tables (narrow tables require more overhead) and the amount of traffic.
Supported limits on database dimensions
Database, table, schema, and column names
50 characters per name
Tables in a sync group
Columns in a table in a sync group
Data row size on a table
Not all tables in a database are required to be in a sync group. The selection of which tables to include in a sync group and which to exclude (or include in a different Sync Group) can impact efficiency and costs. Include only those tables in a Sync Group that business needs demand and the tables upon which they are dependent.
Each table in a sync group must have a Primary Key. The SQL Data Sync (Preview) service is unable to synchronize any table that does not have a Primary Key.
Before rolling into production, test Initial and Ongoing Sync Performance for your scenario.
SQL Data Sync (Preview) Preview provides basic database auto-provisioning.
This section discusses the limitations of SQL Data Sync (Preview)’s provisioning.
The following are limitations of SQL Data Sync (Preview) auto provisioning.
Only the columns selected are created in the destination table.
Thus, if some columns are not part of the sync group those columns are not provisioned in the destination tables.
Indexes are created only for the selected columns.
If the source table index has columns that are not part of the sync group those indexes are not provisioned in the destination tables.
Indexes on XML type columns are not provisioned.
CHECK constraints are not provisioned.
Existing triggers on the source tables are not provisioned.
Views and Stored Procedures are not created on the destination database.
Use the auto-provisioning capability only for trying the service.
For production, you should provision the database schema.
This section discusses the initial synchronization of a sync group and what you can do to avoid an initial synchronization taking longer than necessary and costing more than it should.
When you create a sync group, start with data in only one database. If you have data in multiple databases, SQL Data Sync (Preview) treats each row as a conflict that needs resolution. This causes the initial synchronization to go very slow – taking several days to several months, depending on the database size.
Additionally, if the databases are in different data centers the cost of your initial synchronization will be higher than necessary since each row must travel between the different data centers.
Whenever possible start with data in only one of the sync group’s databases.
Even though SQL Data Sync (Preview) service is currently offered without charge, SQL Database charges are applied to the data that is moved to and from SQL Database data centers. Therefore, you should synchronize tables that are stable or contain data that does not need to be refreshed frequently on a daily or weekly schedule. Tables that contain time sensitive data or data that is more volatile are better synchronized on a more frequent schedule. Analyze your business needs and create your Sync Group and schedules appropriately.
As data passes into and out of a data center there may also be ingress and egress charges.
If you attempt to synchronize a Sync Group that has not completed a synchronization the attempt does not even start. There is no visible indication that the synchronization did not take place. Therefore, it is good practice to schedule synchronizations so that each synchronization has time to complete prior to attempting another synchronization.
If you schedule your sync group to sync every five minutes, T0, T0+5, T0+10 ..., but it takes the group six minutes to complete a synchronization then your synchronizations take place at T0, T0+10, T0+20 and so on. T0+5 and T0+15 fail because synchronizations T0 and T0+10 haven't completed in time.
A synchronization loop results when there are circular references within a sync group so that each change in one database is replicated through the databases in the sync group circularly and endlessly. You want to avoid synchronization loops as they degrade performance and can significantly increase your costs.
For more detailed information on synchronization loops with examples of designs that result in them and how to redesign to avoid them see the topic Understand and Avoid Synchronization Loops.
A sync group or a database within a sync group can become out-of-date. When a sync group’s status is “out-of-date” is stops functioning. When a database’s status is “out-of-date” data can be lost. It is best to avoid these situations rather than have to recover from them.
A database’s status is set to out-of-date when it has been offline for 45 days or more. You avoid the out-of-date status on a database by ensuring that none of your databases are offline for 45 days or more. See the topic A database has an "Out-of-Date" status for how to recover from a database that has an out-of-date status.
A sync group’s is set to out-of-date when any change within the sync group fails to propagate to the rest of the sync group for 45 days or more. You can avoid the out-of-date status on a sync group by regularly checking the sync group’s history log and ensuring that all conflicts are resolved and changes successfully propagated throughout the sync group databases.
Reasons a sync group may fail to apply a change include:
Schema incompatibility between tables.
Data incompatibility between tables. See the topic SQL Database Data Types supported by SQL Data Sync.
Inserting a row with a null value in a column that does not allow null values.
Updating a row with a value that violates a foreign key constraint.
You can prevent out-of-date sync groups by:
Update the schema to allow the values contained in the failed rows. See the topic Q: I just changed my schema. How do I get the change into my sync group?.
Update the foreign key values to include the values contained in the failed rows.
Update the data values in the failed row to be compatible with the schema or foreign keys in the target database.
See the topic A sync group has an "Out-of-Date" status for how to recover from a sync group that has an out-of-date status.
Under certain circumstances unregistering a database with a client agent can cause synchronizations to fail.
Sync group A was created with an SQL Database instance and an on-premise SQL Server database which is associated with local agent 1.
The same on-premises database is registered with local agent 2 (this agent is not associated with any sync group).
Unregistering the on-premises database from local agent 2 removes the tracking/meta tables for the sync group A for the on-premises database.
Now, the sync group A operations fail with the following error –
"The current operation could not be completed because the database is not provisioned for sync or you do not have permissions to the sync configuration tables."
Avoid the situation entirely by never registering a database with more than one agent.
To recover from thus situation:
Remove the database from each sync group it belongs to.
Add the database back into each sync group you just removed it from.
Deploy each affected sync group (which provisions the database).
- Remove the database from each sync group it belongs to.
Changes can fail to propagate due to many reasons. Some causes would be:
Trying to insert null in non-nullable columns.
Violating foreign key constraints.
Sync Group shows it is in a warning state.
Details are in the Portal UI Log viewer.
If the issue is not resolved for 45 days, your database becomes outofdate.
Note Those changes will never propagate. The only way to recover is recreate your sync group.
Regularly monitor your Sync Group and Database health through the portal and Log UI.
Do not attempt to remove a database from a sync group and then edit the sync group without first deploying one of the changes.
First, remove a database from a sync group. Then deploy the change and wait for de-provisioning to complete. Once this has completed, you may edit the sync group and deploy the changes.
If you attempt to remove a database and then edit a sync group without first deploying one of the changes, one or the other operation will fail and the UI may get into an inconsistent state. However, if this happens, you may refresh the UI to restore the correct state.
SQL Data Sync (Preview) is a feature of SQL Database. From the Azure Management portal you can perform all tasks necessary to create, deploy, and modify a sync group.
How to create a sync group
There are six steps to creating a sync group from the Azure Management portal. Details on each step can be found by following these links.
How to modify a sync group
You can modify a sync group’s schema by adding/removing tables or columns in the sync group; or by altering a column’s width or data type. Details can be found by following the links.