I was testing a new development tool today that connects to remote servers using a proprietary connection string builder that doesn’t allow you to specify a failover server. Since I was connecting to a mirrored database I was curious what the implications were for not specifying a “Failover Partner” in the connection string.
The answer surprised me. The long version is here, but the short version is this. If you don’t specify a failover partner on a mirrored database, everything works fine provided the Principal is up and running. If the Principal is down or has been swapped so that it’s the mirror, the connection fails.
In point of fact, if the Principal is up and running, it doesn’t matter whether you specify a Mirror in the connection string; the name of the Mirror is actually returned by Sql Server and that’s what the client will cache as the Mirror rather than what you specify in the connection string. The purpose of specifying a FailoverPartner in the connection string is to tell the client what to do when the Principal is down when you initially connect — it tells the client to try the FailOver server. If the Mirror is reachable, it’s treated as the Primary and the name of the Mirror (originally the Primary!) is returned by Sql Server itself.
There are actually seven scenarios covered in the above link; they are worth studying when you set up your error handling for mirrored databases. For example there is an interesting and rather pernicious scenario that can occur when your DBA changes things so that the Mirror is now the Primary and the new Mirror has an entirely different name — and does so without telling you to update your connection strings. You can run for weeks or months without error, until there is an actual fail-over event.
