SQL Data Sync (Preview) FAQ
Updated: February 12, 2014
This article covers the most frequently asked questions about SQL Data Sync (Preview). If you have a question that is not covered here, ask it in the SQL Data Sync (Preview) forum.
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.
Table of Contents
How to …
Q: If I have question about using SQL Data Sync, who do I contact?
A. You can contact us at the forums with any questions or comments about SQL Data Sync (Preview).
Q: What is new in the latest version of SQL Data Sync?
A. See the topic New in SQL Data Sync (Preview).
Q: What are the minimum requirements to use SQL Data Sync?
A. See the topic System Requirements for SQL Data Sync (Preview) for minimum system requirements for using SQL Data Sync (Preview).
Q: Can I use SQL Data Sync in production?
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.
Q: Can I use SQL Data Sync with a mobile device?
A. No. SQL Data Sync (Preview) does not currently support synchronizing with mobile devices.
Q: How much does the SQL Data Sync (Preview) service cost?
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.
Q: Where is SQL Data Sync located?
A. Currently there is a SQL Data Sync (Preview) service located in each Azure data center.
Q: Is a SQL Database account required? If so, how do I get a SQL Database account?
A. Yes. You must have a SQL Database account to host the Hub Database.
Q: Does SQL Data Sync change a 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.
Q: How do I access a SQL Database when synchronizing using the scheduled service?
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.
Q: Does SQL Data Sync fully create and provision tables?
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.
Q: Does provisioning support a collated primary key column or collated filter column?
A. If the primary key is a collated column, provisioning doesn’t create the collation in the destination.
If the primary key column or filter column is set with a collation that is different from the server instance, a compilation time error occurs during sync provision.
Q: Is federation supported in SQL Data Sync?
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.
Q: What are limits I should know about?
A: For SQL Data Sync (Preview) Preview limits, see the topic Known SQL Data Sync (Preview) Limits.
Q: How many instances of the local agent UI can be run?
A. Only one instance of the UI can be run.
Q: When can I delete a client agent?
A. You must delete all sync groups for a client agent before you delete the client agent.
Q: Why do I need a 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 (Preview).
Q: Why is my database status "Out-of-Date"?
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.
Q: What happens when I restore lost or corrupted databases?
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.
Q: Should I use SQL Data Sync to backup and restore my databases?
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.
Q: Is my data convergent after a sync?
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.
How do I…
Q: How do I upgrade to the latest version?
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.
Q: How can I change my service account?
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.
Q: How do I avoid a "synchronization loop"?
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.
Q: How do I manually deprovision a database?
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.
Q: How do I get schema changes into a sync group?
A. SQL Data Sync (Preview) Preview supports dynamic schema changes. See the topic Edit a Sync Group for information on what kinds of changes can be dynamically incorporated into your Sync Group and how to incorporate them.
Q: How do I change my agent key?
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.
See the topic How To: Change a Client Agent's Key for details on how to change an agent’s key.
Q: How do I retire a client agent?
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.
Q: How do I move a client agent to another computer?
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.
ConceptsSQL Data Sync (Preview)
SQL Data Sync (Preview) Community Resources
SQL Database Data Types supported by SQL Data Sync (Preview)
Conflict Resolution when Synchronizing
SQL Data Sync (Preview)Troubleshooting Guide
Glossary of SQL Data Sync Terms
Other ResourcesKnown SQL Data Sync Issues
Azure Management Portal
SQL Azure Data Sync [Channel 9 videos]
SQL Data Sync Forum