SQL Data Sync FAQ
This article covers the most frequently asked questions regarding SQL Data Sync. If you have a question that is not covered here please ask it in the forum and we will add the most frequently asked questions to this topic.
 |
Important! This wiki topic may be obsolete. This wiki topic is no longer updated by Microsoft. We moved the content to the MSDN Library where we keep it current. To find the latest version of this topic on MSDN click here. |
Q: What is new in the latest version of SQL Data Sync?
A: The latest version of SQL Data Sync is the Preview version - SQL Data Sync Preview.
The new capabilities and features for the Preview release include:
- SQL Data Sync is now hosted on the Windows Azure Management portal.
- New, task oriented UI.
- The synchronization direction is settable for each spoke database in a sync group.
- The synchronization datasets are more granularly defined than before - down to specific rows in a table.
- The Conflict resolution policy for a sync group is settable.
We recently released a minor upgrade to the Preview Data Sync Agent. You can download the upgraded Agent from the Download Center.
Improvements included in the upgrade include:
- Log-ins with either username@server or just username are accepted
- Column names with spaces are now supported
- Columns with a NewSequentialID constraint are converted to NewID for SQL Databases in the sync group
- Administrators and non-Administrators alike are able to install the Data Sync Agent
- A new version of the Data Sync Agent is now available on the Download Center
We made some minor improvements in the new Data Sync Agent. If you already have a Preview version of the Data Sync Agent it will continue to work.
For additional information on the new features in the Preview release see this MSDN article.
Q: Does SQL Data Sync work on my system?
A: SQL Data Sync supports the following:
SQL Server 2005 SP2 and later versions.
It works best with SQL Server 2008 R2 and later as support for SQL Database was added to SQL Server Management Studio in SQL Server 2008 R2. If you don't have SQL Server 2008 R2 you can download a free trial from SQL Server 2008 R2 trial.Windows 2008, Windows Vista, and Windows 7 operating systems.
An x86 or x64 computer.
Q: Can I continue using the CTP2 version?
A: Not for long.
The CTP2 website will be retired and unavailable a few weeks after the introduction of the Preview version. See the topic [[SQL Data Sync How to Upgrade]] for direction on how to upgrade to the Preview version.
Q: Is a SQL Database account required? How do I get a SQL Database account?
A: Yes. Your must have a SQL Database account to host the Sync Group's Hub database.
Please visit http://www.microsoft.com/windowsazure/sqlazure/ and sign up.
Q: How much does SQL Data Sync Service cost?
A: During the CTPs there is no charge for the Data Sync Service, however you will still accrue data transfer charges for data moving in and out of your SQL Database. For more details on this pricing please visit: http://www.microsoft.com/windowsazure/pricing/. It is possible that with the release of SQL Data Sync v.1 there will be a charge for the service. Watch this space and the topic [[SQL Data Sync Cost Considerations]] for the latest information on pricing.
Q: Will SQL Data Sync make changes to my database?
A: Yes, in order to track incremental data changes SQL Data Sync will add change tracking table for each table that is being synchronized when synchronization is configured. During this configuration, it will also add 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 on a non-production database to ensure that it does not have an adverse effect on your existing applications.
Q: Where is SQL Data Sync located?
A: Currently we have one SQL Data Sync service located in the South Central US Windows Azure data center.
Q: Who do I contact if I have question about using SQL Data Sync?
A: You can contact us at the forums (http://go.microsoft.com/fwlink/?linkid=230831) with any questions or comments about SQL Data Sync.
Q: How do you access my 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 system database.
Q: Can I use this SQL Data Sync in production?
A: No. SQL Data Sync is currently available only as a Preview and is meant only for product feedback for future releases and should not be used in production environments.
Q: How do I manually deprovision my database for Sync?
A: A standalone executable is installed with the LocalAgentSetup Msi that you can use to de-provision the SQL Data Sync objects that are created when you add a database or table to your synchronization service.
| Usage: | ||||
| CPT2 Preview |
Deprov.exe <connectionstring> [/f] DeprovisioningUtil.exe <connectionstring> [/f] |
|||
| <connectionstring> | Specifies the connection string of the Sql Server or SQL Database to be deprovisioned. | |||
| [/f] | Deprovision without warning. | |||
| Examples: | ||||
| SQL Server | CTP2 | deprov "Server=(local);Database=NorthWind;Trusted_Connection=True" | ||
| Preview | deprovisioningutil "Server=(local);Database=NorthWind;Trusted_Connection=True" | |||
| SQL Database | CPT2 | deprov "Server=tcp:rwbhdyp2ym.database.windows.net; Database=NorthwindAzure; User ID=<yourID>; Password=<yourPassword>; Trusted_Connection=False;" /f | ||
| SQL Database | Preview | deprovisioningutil "Server=tcp:rwbhdyp2ym.database.windows.net; Database=NorthwindAzure; User ID=<yourID>; Password=<yourPassword>; Trusted_Connection=False;" /f | ||
Q: Should I use SQL Data Sync to backup and restore my databases?
A: No. SQL Data Sync does not support versioning so you cannot restore to a specific point in time. See the topics Backing Up Your Data and Restoring Your Data for additional information.
Q: How many SQL Data Sync servers can I create per Windows Azure subscription?
A: SQL Data Sync permits one Data Sync server per subscription. However, a single Data Sync server can host multiple sync groups that encompass multiple SQL Server and SQL Databases.
Q: What are the other limits I should know about?
A: SQL Data Sync Preview has the following limits:
| Instance | Maximum |
|---|---|
| Endpoints across all sync groups (not counting the hub) |
30 This number is independent of the number of sync groups. |
| Members in a sync group | 5 |
| Sync groups a member can belong to Including the hub database. |
5 |
| Number of sync groups for a subscription | 1000 This is not possible from the current UI. |
Q: What are the supported limits on database dimensions?
| Dimension | Limit |
| Database size | 20 GB |
| Number of rows | 5 million |
| Database, table, schema, and column names | 50 characters |
| Tables in a sync group | 100 |
| Columns in a table in a sync group | 1000 |
Q: I just changed my schema. How do I get the change into my sync group?
A: SQL Data Sync Preview does not support dynamic schema changes. The only way to get your new schema into the sync group is to remove the current sync group and create a new one with the new schema. See the topics [[SQL Data Sync Remove a Sync Group]] and SQL Data Sync Create a Sync Group for additional information.
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 that same key is recorded with the local agent and with the Data Sync service.
- Navigate your browser to the Windows Azure management portal.
- Click Data Sync in the left pane.
- In the left pane select the agent that needs a new key.
- In the Agent portion of the ribbon click Create new Key.
- Copy the new key to your clipboard - click Copy.
- Go to where you installed the agent and launch .
- Click .
- Paste the agent key from your clipboard into the text box.
- Close the agent UI.
Q: What is a "synchronization loop" and how do I avoid one?
A: A synchronization loop is akin to an infinite loop. Synchronizations occur repeatedly even if there are no changes in either database. To avoid a synchronization loop ensure that your sync groups do not overlap - that is, this is no table or row that is referred to in a circular manner within or across multiple sync groups.
Q: Why is my database status is "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: How do I upgrade to the latest version?
A: SQL Data Sync Preview is not backward compatible with CTP2. See the article How to Upgrade for complete instructions on how to upgrade from CTP2 to SQL Data Sync Preview.
Q: Why do I need a client agent?
A: The Data Sync Service communicates with SQL Server databases via the Client Agent. This security feature prevents direct communication with databases behind a firewall. When the Sync 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 security measures see the article Data Security.
Feedback
This release was provided in order to gather feedback from our customers. Now that you have previewed what the SQL Data Sync team is doing, please let us know what you think of our direction, and tell us about your experiences. You can send us your thoughts in any of the following ways:
- Send us a note on http://twitter.com/syncfx.
- Post a comment to our blog at http://blogs.msdn.com/sync.
- Post a thread to our forum at http://social.msdn.microsoft.com/forums/en-US/ssdsgetstarted/threads/
See Also
- [[SQL Data Sync Overview]]
- [[Windows Azure SQL Database Overview]]