SQL Data Sync FAQ
Updated: July 23, 2015
This article covers the most frequently asked questions about SQL Data Sync (Preview).
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.
|SQL Data Sync (Preview) is available only as a Preview and is meant only for product feedback for future releases and should not be used in production environments.|
A. You can contact us at the forums with any questions or comments about SQL Data Sync (Preview).
A. See the topic What's New in SQL Data Sync.
A. See the topic System Requirements for SQL Data Sync for minimum system requirements for using SQL Data Sync (Preview).
A. No. SQL Data Sync (Preview) is available only as a Preview and is meant only for product feedback for future releases and should not be used in production environments.
A. No. SQL Data Sync (Preview) does not currently support synchronizing with mobile devices.
A. During the Preview, there is no charge for the SQL Data Sync (Preview) service; however you still accrue data transfer charges for data movement in and out of your SQL Database instance. For more details on this pricing, visit: http://www.microsoft.com/windowsazure/pricing/. It is possible that with the release of SQL Data Sync (Preview) v.1 there will be a charge for the service. Watch this space and the topic Plan for Optimization for the latest information on pricing.
A. Currently there is a SQL Data Sync (Preview) service located in each Azure data center.
A. Yes. You must have a SQL Database account to host the Hub Database.
A. Yes, in order to track incremental data changes, SQL Data Sync (Preview) adds a change-tracking table for each table that is being synchronized when synchronization is configured. During this configuration, it also adds triggers to your base tables as well as some stored procedures for gathering and applying changes. It is highly recommended that you test SQL Data Sync (Preview) on a non production database to ensure that it does not have an adverse effect on your existing applications.
A. When you set up a synchronization group, the service requests credentials for your SQL Database servers. These credentials are stored in encrypted format within the SQL Data Sync (Preview) system database.
A. If the sync schema tables are not already created in the destination database, SQL Data Sync (Preview) creates them with the selected columns. However, this does not result in a full fidelity schema due to:
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.
Because of these limitations, we recommend that for production environments you provision the full fidelity schema yourself and not depend on the schema provisioned by SQL Data Sync (Preview). The auto provisioning feature of SQL Data Sync (Preview) works well for trying out the service.
A. Yes. SQL Data Sync supports the below scenarios with collation:
If the selected sync schema tables are not already in your hub or member databases, upon deploying the sync group, the service will auto create the corresponding tables and columns with the same collation settings as the selected in the empty database(s).
If the tables to-be-synced are already created in both of your hub and member databases, SQL Data Sync requires the primary key columns are set with the same collation between hub/member databases to successfully deploy the sync group. No collation restriction on the non-primary key columns.
A. Federation Root Database can be used in the SQL Data Sync (Preview) Service without any limitation. The Federated Database endpoint cannot be added to the current version of SQL Data Sync (Preview). If you have a pressing requirement, state your scenario at the SQL Database MSDN forum.
A: For SQL Data Sync (Preview) Preview limits, see the topic Known SQL Data Sync Limits.
A. Only one instance of the UI can be run.
A. You must delete all sync groups for a client agent before you delete the client agent.
A. The SQL Data Sync (Preview) service communicates with SQL Server databases via the client agent. This security feature prevents direct communication with databases behind a firewall. When the SQL Data Sync (Preview) service communicates with the agent it does so using encrypted connections and a unique token or “agent key”. The SQL Server databases authenticate the agent using the connection string and agent key. This design provides a high level of security for your data. For more information in SQL Data Sync (Preview) security measures see the article Data Security in SQL Data Sync.
A. The "Out-of-Date" status results from the database being offline 45 or more days. See A database has an "Out-of-Date" status in the Troubleshooting Guide for how to bring the database back up to date.
A. If you restore lost or corrupted databases from a backup, there may be non-convergence of your data in the sync group(s) that the databases belong to.
A. While it is possible to use SQL Data Sync (Preview) to create a backup of your data it is not recommended. SQL Data Sync (Preview) does not version synchronizations. Therefore, you cannot backup and restore to a specific point in time.
Additionally, SQL Data Sync (Preview):
Does not back up other SQL objects, such as stored procedures.
Does not "restore" quickly.
Database Copy for SQL Database instances (How To: Backup Up Your Database) and your well-established procedures for SQL Server databases.
A. Not necessarily. Given a sync group with a hub and three spokes, A, B, and C, the synchronizations are Hub/A, Hub/B, and Hub/C. If after the Hub/A sync a change is made to A, that change is not written to either B or C until the next sync task.
A. SQL Data Sync (Preview) Preview is not backward compatible with CTP2. See the article How to Upgrade a Client Agent for complete instructions on how to upgrade.
A. Once you install an client agent the only way to change the service account is to uninstall it and install a new client agent with the new service account.
A. A synchronization loop occurs when a circular reference is formed between two or more sync groups. In a synchronization loop a change created in one sync group is written out to the databases in other sync groups and ultimately written back as a new change to the database where the change originated, beginning the entire process again. Since it is circular, once the loop is started it never ends unless you break the loop by removing the circular references. For more detailed information and examples see the topic Understand and Avoid Synchronization Loops.
A. If you remove a database from a sync group or remove a sync group and one or more databases fail to deprovision, you can manually deprovision the database. See the topic Manually Deprovision a Database for details.
A. SQL Data Sync (Preview) Preview supports dynamic schema changes.
A. An agent key can only be used once by an agent. It cannot be reused when you remove then reinstall a new agent, nor can it be used by multiple agents. If you need to create a new key for an existing agent you must be sure that the same key is recorded with the client agent and with the SQL Data Sync (Preview) service.
A. To immediately invalidate or retire an agent regenerate it's key in the portal but do not submit it in the Agent UI.Regenerating a key invalidates the previous key irrespective if the corresponding agent is online or offline.
A. If you would like to run the local agent from a different computer than it is currently on, please follow steps below:
Install the agent on desired computer.
Log onto the SQL Data Sync (Preview) portal and regenerate agent key for the new agent.
Use the new agent’s UI to submit the new agent key.
Wait while the client agent downloads the list of on-premise databases that were registered earlier.
Provide database credentials for all databases that display as unreachable. Of course, these database must be reachable from the new computer on which agent is installed.
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.
Before you start
Before you begin to design and implement your synchronizations, you should be familiar with these topics.
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.