Lesson Learned #428: SqlError Number:229 provisioning DataSync in Azure SQL Database
We got a new issue when our customer found provisioning a Sync Group named 'XXX' failed with error: Database re-provisioning failed with the exception 'SqlException ID: XX-900f-42a5-9852-XXX, Error Code: -2146232060 - SqlError Number:229, Message: SQL error with code 229.'. Following I would like to share some details what is the error and the solution to fix it.Lesson Learned #353: Sync failed with Inner exception: Exception of type System.OutOfMemoryException
Our customer reported today that Data Sync failed is failing with the following inner exception: System.OutOfMemoryException error. Sync failed with the exception 'An unexpected error occurred when applying batch file C:\\XXX\\XXXX\\AppData\\Local\\Temp\\XXX\\sync_zyz\\ec53ddeb-xxxx.batch. See the inner exception for more details.Inner exception: Exception of type 'System.OutOfMemoryException' was thrown. For more information, provide tracing ID ‘XXX9’ to customer support.'Understanding Azure SQL Data Sync Firewall Requirements
Why IP Whitelisting Is Required and What Customers Should Know Azure SQL Data Sync is commonly used to synchronize data between on‑premises SQL Server databases and Azure SQL Database. While the setup experience is generally straightforward, customers sometimes encounter connectivity or configuration issues that are rooted in network security and firewall behavior. This blog explains why Azure SQL Data Sync requires firewall exceptions, what type of IP addresses may appear in audit logs, and how to approach this topic from a security and documentation standpoint—based on real troubleshooting discussions within the Azure SQL Data Sync ecosystem. The Scenario: Sync Agent Configuration Fails Despite Valid Setup A frequently reported issue occurs when the Azure SQL Data Sync Agent (installed on an on‑premises server) fails to save its configuration. The error typically indicates that a valid agent key is required—even when: The agent key was freshly generated from the Azure SQL Data Sync portal Connection tests succeed The agent has been reinstalled or the server restarted New sync groups were created Despite these efforts, synchronization does not proceed until a specific public IP address is allowed through the Azure SQL Database firewall. Why Firewall Rules Matter for Azure SQL Data Sync Azure SQL Database is protected by a server‑level firewall that blocks all inbound traffic by default. Any external client—including the Data Sync Agent—must be explicitly allowed to connect. In Azure SQL Data Sync: The Data Sync Agent runs on‑premises It connects outbound over TCP port 1433 It uses the public endpoint of the Azure SQL logical server The Azure SQL firewall must allow the public IP address used by the agent If this IP is not allowed, the agent cannot complete configuration or perform synchronization operations—even if authentication and permissions are otherwise correct. Identifying the Required IP Address In the referenced discussion, the required IP address was identified by reviewing Azure SQL audit logs, which revealed connection attempts being blocked at the firewall layer. Once this IP address was added to the Azure SQL server firewall rules, synchronization completed successfully. This highlights an important point: Audit logs can be a reliable way to identify which IP address must be whitelisted when Data Sync connectivity fails. Is This IP Address Owned by Microsoft? Can It Change? A natural follow‑up question is whether the observed IP address is Microsoft‑owned, and whether it can change. From the discussion: Azure SQL Data Sync relies on Microsoft‑managed service infrastructure Some outbound connectivity may originate from Azure service IP ranges Microsoft publishes official IP ranges and service tags for transparency However, documentation does not guarantee that a single static IP will always be used. Customers should therefore treat firewall configuration as a network security requirement, not a one‑time exception. Related Microsoft Resources While Azure SQL Data Sync documentation focuses on setup and troubleshooting, firewall requirements are often implicit rather than explicitly called out. The following Microsoft resources were referenced in the discussion to help customers understand Azure service IP ownership and ranges: Gateway IP addresses – Azure Synapse Analytics Download Azure IP Ranges and Service Tags – Public Cloud These resources can help security teams validate Microsoft‑owned IPs and plan firewall policies accordingly. Key Takeaways for Customers ✅ Azure SQL Data Sync requires firewall access to Azure SQL Database ✅ The public IP used by the Data Sync Agent must be explicitly allowed ✅ Audit logs are useful for identifying blocked IPs ✅ IP addresses may belong to Microsoft infrastructure and can change over time ✅ Firewall configuration is a security prerequisite, not an optional step Closing Thoughts Azure SQL Data Sync operates securely by design, leveraging Azure SQL Database firewall protections. While this can introduce configuration challenges, understanding the network flow and firewall requirements can significantly reduce setup friction and troubleshooting time. If you're implementing Azure SQL Data Sync in a locked‑down network environment, we recommend involving your network and security teams early and validating firewall rules as part of the initial deployment checklist.Troubleshooting Azure SQL Data Sync Groups Stuck in Progressing State
Azure SQL Data Sync is commonly used to synchronize data across Azure SQL Databases and on‑premises SQL Server environments. While the service works well in many scenarios, customers may occasionally encounter a situation where a Sync Group remains stuck in a “Progressing” state and cannot be started, stopped, or refreshed. This blog walks through a real-world troubleshooting scenario, highlights the root cause, and outlines practical remediation steps based on actual support investigation and collaboration. Problem Overview In this scenario, the customer reported that: The Sync Group was stuck in “Progressing” for multiple days Sync operations could not be started or stopped Tables could not be refreshed or reconfigured Azure Activity Logs showed operations as Succeeded, yet sync never progressed Our backend telemetry showed the Sync Group as Active, while hub and member databases were in Reprovisioning state The last successful sync occurred on XX day, after which the sync pipeline stopped making progress. Initial Investigation Findings During the investigation, several key observations were made: 1. High DATA IO Utilization Telemetry and backend checks revealed that DATA IO utilization was pegged at 100% on one of the sync member databases starting XX day. Despite no noticeable change in application workload, the database was under sustained IO pressure, which directly impacted Data Sync operations. 2. Deadlocks During Sync Processing Our backend telemetry showed repeated deadlock errors: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. These deadlocks were observed for multiple Sync Member IDs starting the same day IO saturation began. This aligned with the hypothesis that resource contention, not a Data Sync service failure, was the underlying issue. 3. Metadata Database Was Healthy The Sync metadata database was running on a serverless Azure SQL Database (1 vCore) and showed healthy resource usage, ruling it out as a bottleneck. Recommended Troubleshooting Steps Based on the findings, the following steps were recommended and validated: ✅ Step 1: Address Database Resource Constraints First Before attempting to recreate or reset the Sync Group, the focus was placed on resolving DATA IO saturation on the affected database. Actions included: Scaling up the database (DTUs / vCores) Monitoring IO utilization after scaling Ensuring sufficient headroom for sync operations This was identified as the primary remediation step. ✅ Step 2: Use the Azure SQL Data Sync Health Checker The Azure SQL Data Sync Health Checker was recommended to validate: Sync metadata integrity Table-level configuration issues Agent and connectivity status GitHub tool: AzureSQLDataSyncHealthChecker ✅ Step 3: Validate Sync Group and Agent State via PowerShell PowerShell was used to confirm: Sync Group state Last successful sync time On‑premises Sync Agent connectivity Example commands used: Get-AzureRmSqlSyncGroup ` -ResourceGroupName "ResourceGroup01" ` -ServerName "Server01" ` -DatabaseName "Database01" | Format-List Get-AzureRmSqlSyncAgent ` -ResourceGroupName "ResourceGroup01" ` -ServerName "Server01" | Select ResourceGroupName, SyncState, LastSyncTime Resolution After the customer increased the database size, DATA IO utilization dropped, sync operations resumed normally, and the customer confirmed that the issue was resolved.
