SQL Data Sync Best Practices

Updated: December 2, 2014

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:

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.

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.

Primary Keys

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.

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.

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.

Frequency

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.

For example:

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.

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.

Under certain circumstances unregistering a database with a client agent can cause synchronizations to fail.

Scenario:

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."

Solution:

Avoid the situation entirely by never registering a database with more than one agent.

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.