SALES: 1-800-867-1380

SQL Data Sync (Preview)Troubleshooting Guide

Updated: October 31, 2013

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

 

SQL Azure Data Sync Icon

The Troubleshooting Guide covers current issues that are known to the SQL Data Sync (Preview) team. If there is a work-around for an issue it is also explained.

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

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

My client agent won't work

Description/Symptoms

You get the following error messages when you attempt to use the client 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 Data Sync (Preview) Preview.

The most likely cause is:

  • You are running Windows 8 Developer Preview, or

  • You have .NET 4.5 installed.

Solution/Workaround

Make sure that you install the client agent on a computer that is not running Windows 8 Developer Preview and that the .NET Framework 4.5 is not installed.

My client agent will not work after I cancel the Uninstall

Description/Symptoms

The client agent does not work even though you canceled its uninstallation.

Cause

This is due to the fact that SQL Data Sync (Preview) client agent does not store credentials.

Solution/Workaround

There are two solutions to try:

  • First, use services.msc to re-enter your credentials for the client agent.

  • Second, uninstall this client agent and install a new one.
    If you need to you can download and install the latest client agent from Download Center.

My database isn’t listed in the agent dropdown

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)

Three steps to add a SQL Server Database


Figure 1

Cause

Solution

The solution depends upon the cause.

The database is not registered with the client agent

First, make sure that the database you are looking for is registered with the client agent. See following steps to register a database with the client agent.

  1. Launch SqlAzureDataSyncAgent from the \bin directory in its install location.
    The default install location is C:\Program Files (x86)\Microsoft SQL Data Sync.

  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)

The client agent and sync group are in different data centers

Second, you must have both the client agent and the sync group in the same data center. You can accomplish this by:

The client agent’s list of databases not current

The local agent downloads the list of associated databases only on the first submission of the agent key, not on subsequent agent key submissions. Thus, databases registered during an agent move do not show up on the original agent instance.

Solution: stop then re-start the client agent service.

Client 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


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/Workaround

The fix is quite simple; update the agent's password to your current server password.

  1. Locate the SQL Data Sync (Preview) client 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 “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 Data Sync (Preview) Agent Preview.

  3. Right-click the entry and select Stop.

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

  5. In the SQL Data Sync (Preview) 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 Data Sync (Preview) Agent Preview service, then click Start.

  10. Close the Services window.

I 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 antivirus 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), and then remove the subdirectories as well.

ImportantImportant
Wait until the synchronization completes before you delete any files.

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 client agent is offline.

    Be sure that the client agent is online then try again.

  • The client agent is uninstalled or missing.

    If the client agent is uninstalled or otherwise missing:

    1. Remove agent XML file from the SQL Data Sync (Preview) installation folder if the file exists.

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

  • The SQL Data Sync (Preview) 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 Data Sync (Preview) 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 Database 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.

Sync fails on the portal UI for on-premises databases associated with the client agent

Description/Symptoms

Sync fails on the SQL Data Sync (Preview) portal UI for on-premises databases associated with the agent. On the local computer running the agent, you will see System.IO.IOException errors in the Event Log, stating that the disk has insufficient space.

Solution/Workaround

Create more space on the drive on which the %TEMP% directory resides.

I can't unregister an on-premises SQL Server database

Cause

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

Solution/Workaround

SQL Server Database
To unregister an on-premises SQL Server database select the database and click Force Delete.

If this doesn’t remove the database from the sync group:

  1. Stop then restart the client agent host service.

    1. Click the Start menu.

    2. Enter services.msc in the search text box.

    3. In the Programs section of the results pane double-click Services.

    4. Find and right-click the service SQL Data Sync (Preview).

    5. If the service is running stop it.

    6. Click Start.

    7. Check whether the database is no longer registered. If it is no longer registered you are done. Otherwise proceed with the next step.

  2. Open the client agent UI (SqlAzureDataSyncAgent).

  3. Click Edit Credentials and supply the credentials for the database so it is reachable.

  4. Proceed with unregistration.

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.

Cannot Complete Operation Dialog


Key Submission Failure Dialog

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

  • The SQL Data Sync (Preview) Windows service is running.

  • The service account for SQL Data Sync (Preview) Preview Windows service has network access.

  • The client agent is able to contact Locator Service.
    Please check that following registry key has value "https://locator.sync.azure.com/LocatorServiceApi.svc"

    • On x86 computer: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL Azure Data Sync\LOCATORSVCURI

    • On x64 computer: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\SQL Azure Data Sync\LOCATORSVCURI

Cause

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

  • The client agent key on the SQL Data Sync (Preview) server and the local computer must be identical.

  • The client agent key can be used only once.

Solution/Workaround

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. Generate a new key.

    1. Navigate your browser to the Azure Platform portal.

    2. Click SQL Databases in the left pane.

    3. On the top ribbon find and click Sync Preview.

    4. At the bottom of the screen, click Manage Key.

    5. Ensure that the correct agent is named in Agent Name.

    6. In the Manage access key wizard, click Generate.

    7. To the right of the new key value, click the clipboard icon. When prompted, allow the clipboard to access the data.

  2. Apply the new key to the agent.

    1. Use Windows Explorer to navigate to your agent installation directory.

      The default installation directory is c:\program files (x86)\microsoft sql data sync.

    2. Double-click the bin subdirectory.

    3. Launch the SqlAzureDataSyncAgent application.

    4. Click Submit Agent Key.

    5. Paste the key from your clipboard in the space provided.

    6. Click OK.

    7. Close the program.

I 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/Workaround

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.

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

Solution/Workaround

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 Data Sync (Preview) Preview" service.

  4. Restart the UI.

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/Workaround

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

  • For installs: msiexec.exe /i SQLDataSyncAgent-Preview-ENU.msi /l*v

    LocalAgentSetup.InstallLog

  • For uninstalls: msiexec.exe /x SQLDataSyncAgent-se-ENU.msi /l*v

    LocalAgentSetup.InstallLog

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.

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

Cause

SQL Data Sync (Preview) removes databases that have been offline 45 days or more (as counted from the time the database went offline) from the service. If a database is offline for 45 days or more and then comes back online its status is set to "Out-of-Date".

Solution/Workaround

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

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 Add a Database to your Sync Group (SDS).


    WarningWarning
    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/223300http://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.

A sync group has an "Out-of-Date" status

Cause

A sync group can become outdated if one or more changes fail to apply for the whole retention period of 45 days.

Solution/Workaround

To avoid an "Out-of-Date" status examine the results of your sync jobs (in the UI history viewer) on a regular basis, and investigate and resolve any changes that fail to apply.

If a sync group’s status is "Out-of-Date" you need to delete the sync group and recreate it. See the topics Delete a Sync Group (SDS) and Create your Sync Group (SDS).

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.

I see erroneous data in my 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 Data Sync (Preview) 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.

I see inconsistent PK data after a successful synchronization

Description/Symptoms

After a synchronization that is reported as successful and the log shows no failed or skipped rows, you note that PK data is inconsistent among the databases in the sync group.

Cause

This is by design. Change in any primary key column results in inconsistent data in the row where the PK was changed.

Resolution/Workaround

To prevent this scenario ensure that no data in a PK column is changes.

To fix it once the situation has occurred you must drop the affected row from all endpoiints in the sync group then re-insert it.

I 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 Understand and Avoid Synchronization Loops. 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.

Client agent cannot be deleted from the portal if its associated on-premises database is unreachable

Description/Symptoms

If a local end point (database) that is registered with a SQL Data Sync (Preview) client agent becomes unreachable, the client 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.

Resolution/Workaround

Use “force delete” to delete the unreachable database.

noteNote
If after a “force delete” sync metadata tables remain use deprovisioningutil.exe to clean them up. See Q: How do I manually deprovision a database?.

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

Description/Symptoms

You are not able to delete a sync group within 3 minutes of uninstalling/stopping the associated SQL Data Sync (Preview) client agent.

Resolution/Workaround

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

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

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

    • Remove agent XML file from the SQL Data Sync (Preview) installation folder if the file exists.

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

My stored procedure causes a compile time error

Cause

A SQL Database instance or one of its columns has a different collation than the SQL Server instance.

Resolution/Workaround

Ensure that the collations of your SQL Database instance and SQL Server databases are the same.

Navigation

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.

  1. Sign in to the Azure SQL Database Management portal
    SQL Data Sync (Preview) is found as a tab under SQL Database only after you create a sync group.

  2. Install a SQL Data Sync (Preview) Client Agent

  3. Register a SQL Server Database with a Client Agent

  4. Create your Sync Group (SDS)

  5. Define your sync data (SDS)

  6. Configure your sync group (SDS)

 

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.

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft