Share via

SQL Azure Data Sync - Troubleshooting Guide

  • Article

The Troubleshooting Guide covers current issues that are known to the SQL Azure Data Sync team. If there is a workaround for an issue it is also explained.

We will add to and update this article as new issues are discovered and/or workarounds change.

 

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.

 

My local agent won't work

Description/Symptoms
You get the following error messages when you attempt to use the local agent.

"Sync failed with exception There was an error while trying to deserialize parameter www.microsoft.com/.../05:GetBatchInfoResult. Please see InnerException for more details.

Inner exception message: Type 'Microsoft.Synchronization.ChangeBatch' is an invalid collection type since it does not have a default constructor."

Cause
This is an issue with the SQL Azure Data Sync Preview.
The most likely cause is:

  • You are running Windows 8 Developer Preview, or
  • You have .NET 4.5 installed. 

Solution/Work Around
Make sure that you install the local agent on a machine that is not running Windows 8 Developer Preview and that the .NET Framework 4.5 is not installed.

Top

Loss of Registered Endpoints in AgentConfigData.xml

Description/Symptoms
All the endpoints you added to the AgentConfigData.xml file with AgentConfig.exe mysteriously disappear after you run the AgentServiceSetup.msi.

Cause
AgentServiceSetup.msi does not check for an existing AgentConfigData.xml file before creating it. When it creates a new AgentConfigData.xml file the existing file is overwritten and all the endpoints are lost. There is no issue the first time you run the .msi file since there is no AgentConfigData.xml file to overwrite.

Solution/Work Around
To avoid losing all your endpoint registrations make a backup copy of your AgentConfigData.xml file in another directory or with a different file name (AgentConfigData.xml.bkup). After the .msi file finishes, copy your backup file over the new AgentConfigData.xml file. Be sure to copy the backup over the new file before you use AgentConfig.exe to add any additional endpoints or you'll have two sets of endpoints, one in the new file and another in the backup, and you don't want that.

Top

I cannot find my database.

Description/Symptoms
When you attempt to add an existing SQL Server database to a sync group it is not listed in the dropdown. (Figure 1:2)

Figure 1

Cause
The most likely cause of a SQL Server database not appearing in the dropdown is that it is not registered with the client agent.

Solution
The solution is to register the missing database with the client agent.

  1. Launch SqlAzureDataSyncAgent from its install location.
  2. Click Register.
  3. Click the tab for the appropriate type of authentication - SQL or Windows.
  4. Fill in the server, database and, if required, credentials for the missing database.
  5. Click Test Connection.
  6. When the connection succeeds click Done.
  7. When the database appears in the status pane close SqlAzureDataSyncAgent.
  8. Return to the web UI and click Get Database List.  (Figure 1:1)
  9. Wait for the list to update.
  10. Use the dropdown to select the agent to add to the sync group.  (Figure 1:2)

Top

Agent Won't Start (Error 1069)

Description/Symptoms
If you discover that the Agent on a computer hosting SQL Server isn't running (Steps 1 through 4 at Register the SQL Server Databases to check and start the Agent). When you attempt to manually start the Agent you get an error dialog with the error message, "Error 1069: The service did not start due to a log on failure."


Error 1069 Dialog

Cause
A likely cause of this error is that the password on the local server has changed since you created the agent and gave it a log on password.

Solution/Work Around
The fix is quite simple, update the Agent's password to your current server password.

  1. Start the SQL Azure Data Sync Agent Preview service
    Windows 7 / Vista

    1. Click Start.
    2. Type “services.msc” in the text field labeled Search programs and files.
    3. In the search results click on the program called “Services.”

    Windows XP

    1. Click Start.
    2. Type “services” in the text field labeled Open.
    3. Click OK.

  2. In the Services window, scroll to the entry for SQL Azure Data Sync Agent Preview.

  3. Right-click the entry and select Stop.

  4. Right-click the entry and then click Properties.

  5. In the SQL Azure Data Sync Agent Preview Properties window, click the Log on tab.

  6. Enter your password in the Password textbox.

  7. Confirm your password in the Confirm Password textbox.

  8. Click Apply then click OK.

  9. In the Services window right-click the SQL Azure Data Sync Agent Preview service, then click Start

  10. Close the Services window.

 

Top

You get a "disk out of space" message

Cause
The "disk out of space" message can appear when files that should be deleted are not. This may be due to things like anti-virus software or files being open when the deletes were attempted.

Solution
The solution is to manually delete the sync files under %temp% (del *sync* /s) then remove the subdirectories as well.

Important:
Wait until the synchronization completes before you delete any files.

Top

 

I cannot delete my sync group

Description/Symptoms
You fail in your attempt to delete a sync group.

Cause/Fix
Any of the following can result in a failure to delete a sync group.

  • The agent is offline.
    Be sure that the agent is online then try again.

  • The Data Sync service is stopped.

    1. In the Start menu search on Services
    2. Click Services in the Programs section of the search results.
    3. Find the SQL Azure Data Sync Preview service.
    4. If the service status is Stopped right-click the service name and select Start from the dropdown menu.

     

  • A database is offline.
    Check your SQL Azure and SQL Server databases to be sure they are all online.

  • The sync group is provisioning or synchronizing.
    Wait until the provisioning or synchronizing process finishes then retry deleting the sync group.

 

Top

I can't unregister a database

Cause
Most likely you are trying to unregister a database that has already been deleted.

Solution/Workaround
In order to unregister a database it must exist. Therefore, to unregister a deleted database you must:

  1. Create a fake database on the same server and with the same name as the database you are trying to unregister.
  2. Open the Agent UI (SqlAzureDataSyncAgent).
  3. Click Edit Credentials and supply the credentials for the database so it is reachable.
  4. Proceed with unregistration.

Top

 

I cannot submit the Agent Key

Description/Symptoms
After you (re)create a key for an agent you try to submit that key through the SqlAzureDataSyncAgent application but the submission fails to complete.


Key Submission Failure Dialog

Before proceeding make sure that one of the following conditions is not the reason for your issue. Make sure that:

  • The SQL Azure Data Sync service is running.
  • You have network access.

Cause
The agent key uniquely identifies each local agent. The key must meet two conditions for it to work:

  • The key on the Data Sync server and the local machine must be identical.
  • The key for one Agent cannot be used for another agent - not even for a reinstall of an agent on the same machine.

Solution/Work Around
If your agent is not working it is because one or both of these conditions were not met. To get your agent to work again:

  1. Navigate your browser to the SQL Azure Management Portal (https://.
  2. Click Data Sync in the left pane.
  3. On the top ribbon find and click Generate Key.
  4. Copy the new key to your clipboard.
  5. Launch the SqlAzureDataSyncAgent application located in your installation directory.
    The default installation directory is c:\program files (x86)\microsoft sql azure data sync\.
  6. Click Submit Agent Key.
  7. Paste the key from your clipboard in the space provided.
  8. Click OK.
  9. Close the program.

Top

You do not have sufficient privileges to start system services

Cause
This error occurs in two situations:

  • User name and/or password are incorrect.
  • The specified user account does not have sufficient privileges to logon as a service.

Solution/Work Around
Grant logon-as-a-service credentials to the user account.

  1. Navigate to Start > Control Panel > Administrative Tools > Local Security Policy > Local Policy > User Rights Management.
  2. Find and click the Logon as a service entry.
  3. Add the user account in the Logon as a service Properties dialog.
  4. Click Apply then OK.
  5. Close the windows.

Top

 

Local Sync Agent UI is unable to connect to the local sync service

Solution/Work Around
Try the following:

  1. Exit the UI.
  2. Open the Component Services Panel
    1. Start > Search programs and files
    2. Type in "services.msc"
    3. Double-click "Services" in the Programs section of the search results.
  3. Stop then restart the "SQL Azure Data Sync Preview" service.
  4. Restart the UI.

Top

Install, Uninstall or Repair Fails

Cause
There could be any of a number of causes for the failure. You need to look at the logs to determine the specific cause for this failure.

Solution/Work Around
To find the specific cause for the failure you experienced you need to generate and look at the Windows Installer logs. When you downloaded the MSI file is available. You can turn on logging from the command line. For sake of illustration assume that the downloaded .msi file is LocalAgentHost.msi. Generate and examine log files using the following command lines:

  • For installs:   msiexec.exe /i LocalAgentSetup.msi /l*v LocalAgentSetup.InstallLog
  • For uninstalls: msiexec.exe /x LocalAgentSetup.msi /l*v LocalAgentSetup.InstallLog

 

You can enable logging for all installations performed by Windows Installer. Microsfot KB article (http://support.microsoft.com/kb/223300 provides a one-click solution to turn on logging for Windows Installer. It also provides the location of these logs.

Top

A database has an "Out-of-Date" status

Cause
SQL Azure Data Sync cleans up your offline databases once every 45 days (as counted from the time the database went offline). If a database is offline for 45 or more days and then comes back online its status is set to "Out-of-Date".

Solution/Work Around
You can avoid an "Out-of-Date" status by making sure that none of your databases go off line for 45 or more days.

If a database's status is "Out-of-Date" you need to:

  1. Remove the "Out-of-Date" database from the sync group.

  2. Add the database back in to the sync group. See the topic SQL Server Data Sync Add SQL Server Database to the Sync Group or SQL Server Data Sync Add SQL Azure Database to the Sync Group.

    Warning:
    You lose all changes made to this database while it was offline.

        

You can enable logging for all installations performed by Windows Installer. Microsoft KB article (http://support.microsoft.com/kb/223300 provides a one-click solution to turn on logging for Windows Installer. It also provides the location of these logs.

Top

You see erroneous data in your tables

Description/Symptoms
If tables with same name but from different schemas in a database are involved in sync, you will see erroneous data in these tables after sync.

Cause/Fix
The SQL Azure Data Sync provisioning process uses the same tracking tables for tables with same name but in different schemas. As a result, changes from both tables are reflected in the same tracking table, thus causing erroneous data changes during sync.

Resolution/Workaround
Ensure that the names of tables involved in sync are different even if they belong to different schemas.

Top

You see a significant degradation in performance

Description/Symptoms
Your performance degrades significantly, possibly to the point where you cannot even launch the Data Sync UI.

Cause
The most likely cause is a Synchronization Loop. A synchronization loop occurs when a synchronization by sync group A triggers a synchronization by sync group B, which in turn triggers a synchronization by sync group A. The actual situation may be more complex, involving more than two sync groups in the loop, but the significant factor is that there is a circular triggering of synchronizations caused by sync groups overlapping one another.

Resolution/Workaround
The best fix is prevention. Ensure that you do not have circular references in your sync groups. Any row that is synchronized by one sync group cannot be synchronized by another sync group.

Top

Local agent cannot be deleted if its associated on-premise database is unreachable

Description/Symptoms
If a local end point (database) that is registered with a local SQL Azure Data Sync agent becomes unreachable, the local agent cannot be deleted.

Cause
The local agent cannot be deleted because the unreachable database is still registered with the agent and when you try to delete the agent, the deletion process tries to reach the database, which fails. This database cannot be unregistered too, because during unregistration process, local agent tries to connect to database which cannot be reachable.

Resolution/Workaround
Create a local database with the same name as the one that cannot be reached, unregister this database from local agent, and then proceed to delete the agent.

Top

Sync Group cannot be deleted within 3 minutes of uninstalling/stopping the agent

Description/Symptoms
You are not able to delete a sync group with in 3 minutes of uninstalling/stopping the associated local SQL Azure Data Sync agent.

Resolution/Workaround

  1. Remove a sync group with associated sync agents online (strongly recommended).

  2. If agent is offline but installed, bring it online on the on-premise computer, wait for the status of the agent as online in Azure portal, and then remove the sync group.

  3. If agent is offline because it was uninstalled, perform the following steps and try deleting the sync group.

    • Remove agent XML file from the SQL Azure Data Sync installation folder if the file exists

    • Install the agent on same/another on-premise machine, submit agent key from portal generated for the agent that’s showing offline

 

Top

Feedback

This release was provided in order to gather feedback from our customers. Now that you have previewed what the SQL Azure 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:

For More Information

Top