Troubleshooting Azure SQL Data Sync Failure: SQL Error 8106 During Bulk Insert

Azure SQL Data Sync is widely used to maintain consistency across distributed databases in hub–member topologies. However, synchronization may occasionally fail due to schema mismatches between participating databases — even when everything appears correctly configured at first glance.

In this post, we’ll walk through a real-world troubleshooting scenario involving a Data Sync failure caused by a schema inconsistency related to an IDENTITY column, and how it was mitigated.

Sample Error:

sync_7726d6cb22124c0f901192c434f49106bd618f8ab16343b2adc03250f8367ff4\3953fb7d-1dba-4656-8150-83153d5d019b.batch. See the inner exception for more details. Inner exception: Failed to execute the command 'BulkInsertCommand' for table 'schema.table_name'; the transaction was rolled back. Ensure that the command syntax is correct. Inner exception: SqlException ID: e19b3677-d67e-4c8e-bc49-13d3df61ad0e, Error Code: -2146232060 - SqlError Number:8106, Message: SQL error with code 8106 For more information, provide tracing ID ‘92e76130-f80a-4372-9a48-ec0ede8b0288’ to customer support."   

Scenario Overview

A synchronization operation began failing for a specific table within an Azure SQL Data Sync group. The failure was observed during the sync process when applying changes using a batch file.

The error surfaced as part of a failed BulkInsertCommand execution on a synced table, causing the transaction to roll back.

Further investigation revealed the following SQL exception:

SqlError Number: 8106
Table does not have the identity property. Cannot perform SET operation.

Initial Troubleshooting Steps

Before identifying the root cause, the following actions were taken:

• The affected table was removed from the sync group.
• A sync operation was triggered.
• The table was re-added to the sync group.
• Sync was triggered again.

Despite performing these steps, the issue persisted with the same error.

This indicated that the failure was not related to sync metadata or temporary configuration inconsistencies.

Root Cause Analysis

After reviewing the table definitions across the sync topology, it was discovered that:

The synchronized table had an IDENTITY column defined on one side of the topology (Hub or Member) but not on the other.

This schema mismatch led to the sync service attempting to apply SET IDENTITY_INSERT operations during the bulk insert phase — which failed on the database where the column lacked the identity property.

Azure SQL Data Sync relies on consistent schema definitions across all participating databases. Any deviation — particularly involving identity columns — can interrupt data movement operations.

Mitigation Approach

To resolve the issue, the following corrective steps were applied:

1. Remove the affected table from the sync group and save the configuration.
2. Refresh the sync schema.
3. Recreate the table to include the appropriate IDENTITY property.
4. Add the corrected table back to the sync group.
5. Trigger a new sync operation.

These steps ensured that the table definitions were aligned across all sync participants, allowing the synchronization process to proceed successfully.

Best Practices to Avoid Similar Issues

To prevent identity-related sync failures in Azure SQL Data Sync:

• ✅ Ensure table schemas are identical across all participating databases before onboarding them into a sync group.
• ✅ Pay special attention to:
  • IDENTITY properties
  • Primary keys
  • Data types
  • Nullable constraints
• ✅ Always validate schema consistency when:
  • Adding new tables to a sync group
  • Modifying existing table definitions

Final Thoughts

Schema mismatches — especially those involving identity columns — are a common but often overlooked cause of Data Sync failures. By ensuring consistent table definitions across your hub and member databases, you can significantly reduce the risk of synchronization errors and maintain reliable data movement across regions.