My company has a failover pair of SQL Server 2005 instances, which provide database availability for a UL-licensed alarm/call center, where uptime and availability are critical for life safety.
Here's an example line from the connectionStrings of one of the business-critical applications (sanitized for our protection):
<add name="MyCompany.MyApp.Properties.Settings.MyDbConnectionString" connectionString="Data Source=Principal-DB;Failover Partner=Mirror-DB; Initial Catalog=MyDb; Integrated Security=True; Persist Security Info=True;" providerName="System.Data.SqlClient" />
About half an hour ago, this was the scenario as we believe it to be:
- DBA reports need for hardware maintenance on Principal-DB server to all users at a specified time, and recommends they close and restart client applications if they experience database connection issues.
- DBA fails over from Principal-DB to Mirror-DB. Client applications are unaffected.
- DBA powers down Principal-DB server.
- Some client applications begin to timeout on pooled connections (as would be expected). Users experiencing these errors close and restart as recommended by the DBA.
- Those client applications now fail to start altogether, due to a failure to receive a response from the database.
- Hilarity ensues.
Our theory was that, because Principal-DB was not in a state to respond to connection requests at all, when it would normally refuse such connections quickly if it were in the restoring state, the client applications ended up waiting for the entire allotted connection timeout (default, 20 seconds) for the primary server to respond, then returning the timeout error without ever attempting to connect to the listed failover partner.
The quick fix was to push an update to the client application containing an App.config that swapped the Data Source and Failover Partner instances, so Mirror-DB was now the server to which the app attempted to connect first. When we fail back to Principal-DB, we will have to undo this change with another application update.
I need a more permanent fix. This was not our expected behavior for a failover pair, and it cannot be allowed to happen again. There has to be a way to configure the client application so that it will correctly try to connect to the failover partner in this circumstance before returning an error.