TL;DR
Yesterday, the Database Reliability Engineering (DBRE) Team made some changes to our Availability Group (AG) configuration, related to ongoing work to improve database performance during AG Failovers. That issue is mentioned in many recent maintenance announcements.
As part of that work, we updated some connection strings for some databases. At the conclusion of the work, I thought all necessary restarts were completed, and all apps had picked up the new connection strings and were working correctly. I was wrong. Overnight, the remaining services picked up the new connection strings and this started causing issues.
The verbose version
First, let me talk about...
Yesterday's work
Most of the Stack Exchange Network databases share a single server (basically, all the network sites except for stackoverflow.com). Previously, all of the network sites were in a single AG with many databases. Along the way, we began seeing elevated CPU that lasted an extended time (up to 45 minutes) during AG failovers, and that high CPU caused site instability during those failover events. Neither we nor Microsoft were able to reproduce this issue in non-production environments--thus we were limited in our testing options to confirm any fixes, essentially limiting us to maintenance windows for our ability for testing changes.
Earlier this year, we determined that breaking up the single large AG into multiple smaller AGs would mitigate the performance problems. During the last database maintenance window, we carved out and tested two of the smaller AGs, and confirmed this fix. As part of this carveout, we updated connection strings to point to a new DNS A-record (the AG Listener) for the databases that were moved to a new AG. These changes should have been fully transparent, hence there was no public announcement of the work.
Yesterday afternoon, we carved out the next batch of databases from the monolithic AG into a smaller AG. At the end of that planned work, we did our smoke tests, checked for connection errors, and I declared our work complete.
The problem
Unbeknownst to us, not everything had picked up the new connection string settings. Based on our initial investigation, it appears that the remaining services picked up the new connection strings later in the evening/overnight, and some of those services had trouble reaching the database, due to a firewall rule that was not updated to allow traffic to the new AG.
This morning, we rolled back the connection string changes, and restarted all affected services, which brought an end to the problems.
The follow-up
We will be updating the missed firewall rule, and updating DBRE's procedure for these connection string changes to include additional restarts and additional tests to confirm success. Finally, we will redo the connection string updates, and then proceed with the remaining AG carveouts to complete the planned work with these new procedures in place.
