SQL Database - West Europe

Update timeline

1. postmortem Jul 21, 2022, 01:30 PM UTC

   Watch our 'Azure Incident Retrospective' video about this incident: https://aka.ms/AIR/3TBL-PD8 What happened? Between 03:47 UTC and 13:30 UTC on 21 Jul 2022, customers using SQL Database and SQL Data Warehouse in West Europe may have experienced issues accessing services. During this time, new connections to databases in this region may have resulted in errors or timeouts. Existing connections would have remained available to accept new requests, however if those connections were terminated and then re-established, they may have failed. New connections to the region and related management operations began failing from 03:47 UTC, partial recovery began at 06:12 UTC, with full mitigation at 13:30 UTC. Although we initially did not declare mitigation until 18:45 UTC, a thorough impact analysis confirms that failure rates had returned to pre-incident levels earlier. No failures that occurred after 13:30 UTC were directly as a result of this incident. During this impact window, several downstream Azure services that were dependent on the SQL Database service in the region were also impacted - including App Services, Automation, Backup, Data Factory V2, and Digital Twins. Customers that had configured active geo-replication and failover groups would have been able to recover by performing a forced-failover to the configured geo-replica - more information can be found here https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview?view What went wrong, and why? For context, connections to the Azure SQL Database service are received and routed by regional gateway clusters. Each region has multiple gateway clusters for redundancy - traffic is distributed evenly between the clusters under normal operations, and automatically rerouted in case one of the clusters becomes unhealthy. Each gateway cluster has a persisted cache of metadata about each database in the system, that is used for connection routing. These caches are used for scaling-out gateway nodes, to avoid contention on a single source of metadata. There are multiple caches per gateway cluster and each node will fetch data from any of the caches that is available. The West Europe region has two gateway clusters, and each of these clusters has two persisted metadata caches. An operator error led to an incorrect action being performed in close sequence on all four persisted metadata caches. The action resulted in a configuration change that made the caches unavailable to the regional gateway processes. This resulted in all regional gateway processes in West Europe becoming unable to access connection routing metadata, leading to the regional incident from 03:47 UTC. New connections would have failed as the gateways were not able to read routing metadata, but connections that were already established would have continued to work. Management operations on server and database resources would also have been impacted, as some workflows also rely on connection routing. A secondary impact of the issue was that our internal telemetry service in the West Europe region became overloaded with queries. This caused the telemetry ingestion to fall behind by a few hours and telemetry queries were also timing out. The telemetry issues contributed to delays in automatically notifying impacted customer subscriptions via Azure Service Health. As some customers were receiving automatic notifications of impact within 15 minutes, we assumed that the notification pipeline was working as designed. It was later in the event when we understood that communications were not reaching all impacted subscriptions. As a result, we broadened our communications to every customer in the region and published an update on the Azure status page. Additionally, Automatic failover for anyone who had setup failover groups with auto-failover configuration was also impacted due to telemetry issues (manual failover was not impacted). How did we respond? This regional incident was detected by our availability monitors, and we were on the investigation bridge within 13 minutes of customer impact. We understood the issue to the action that was performed erroneously and determined a way to reverse it. Another option would have been to rebuild entirely new caches - but it was determined that this rebuild would take much longer than fixing the caches in-place, so we proceeded to formulate the method to revive the caches in-place. On applying this initial mitigation, the caches came back up, which resulted in a partial recovery of the incident at 06:18 UTC. While success rates improved significantly at this point (~60%) the recovery was considered 'partial' due to two reasons. Firstly, a timing issue in applying mitigation caused gateways in one of the two clusters to cache incorrect cache connection strings. Secondly, the metadata caches were not receiving updates for changes that happened while the caches were unavailable. The first issue was mitigated by restarting all the gateway nodes in the cluster, which needed to be done at a measured pace to avoid overloading the recovering metadata caches. As the restarts progressed, we saw success rates continue to improve, steadily reaching 97% around 07:58 UTC, once all restarts had completed. At this point connections to any database that had not undergone changes (i.e., service tier updates) during the incident would have been successful. The last step was to determine which persistent cache entries were stale (missed updates) and refresh them to a consistent state. We developed and executed a script to refresh cache entries, with the initial refreshes being done manually while the script was being developed. The success rate recovered to 99.9% for the region at 11:10 UTC. We then proceeded to identify and mitigate any residual issues, and also started the process to confirm recovery with customers and downstream impacted Azure services. Based on login success rate telemetry, the incident mitigation time was determined to be 13:30 UTC. Mitigation communications were sent out to all impacted customers at 19:16 UTC, after a thorough validation that no residual impact remained. How are we making incidents like this less likely or less impactful? We are implementing a number of service repairs as a result of this incident, including but not limited to: Completed: • Programmatically blocking any further executions of the action that led to the metadata caches becoming unavailable. In progress: • Implementing stronger guardrails on impactful operations to prevent human errors like the one that triggered this incident. • Implementing in-memory caching of connection routing metadata in each gateway process, to further increase resiliency and scalability. • Implementing throttling on telemetry readers to prevent ingestion from falling behind. • Removing dependency of automatic-failover on telemetry system. • Investigating other service resiliency repairs as determined by our internal retrospective of this incident, which is ongoing. How can our customers and partners make incidents like this less impactful? Customers who had configured active geo-replication and failover groups would have been able to recover by performing a forced-failover to the configured geo-replica. More guidance for recovery in regional failure scenarios is available at: https://docs.microsoft.com/en-us/azure/azure-sql/database/disaster-recovery-guidance How can we make our incident communications more useful? We are piloting this "PIR" format as a potential replacement for our "RCA" (Root Cause Analysis) format. You can rate this PIR and provide any feedback using our quick 3-question survey: https://aka.ms/AzPIR/3TBL-PD8