Question: We’re configuring database mirroring for high availability for some of our databases and we’re trying to decide whether to configure automatic failover. We know why automatic failover may be desirable but can you tell me if there are any drawbacks to having automatic failover configured?
Answer: There are a few reasons why you may choose not to configure automatic failover – I’ll touch on the major ones here.
Firstly, you may find that the available bandwidth and latency between the principal and mirror servers does not allow synchronous mirroring to be configured without having a detrimental effect on your workload performance. This is because synchronous mirroring means that transactions cannot commit on the principal until all transaction log records up to and including the commit log record have been sent across to the mirror and written to the mirror’s log drive. If this has a noticeable effect on the transaction time on the principal, you may not be able to use synchronous mirroring and so cannot use automatic failover.
Related: An Overview of SQL Server High Availability Options
Secondly, if the “application ecosystem” is more than one database, and all the databases are required to be available for the application to work properly, automatic failover should not be configured. If it was, one of the databases could fail over to the mirror and then application connections to either the principal or mirror servers would not permit the application to work correctly. When there are multiple databases involved I usually tell my clients to shy away from using database mirroring and use failover clustering with SAN replication – that’s a long discussion that’s beyond the scope of this blog though.
Thirdly, if a failover occurs, that entails application downtime (from very minimal to potentially a lot, if the mirror is significantly behind the principal in replaying transaction log records). Once the failover occurs, at some point you usually want to fail back to the original principal again, which entails more application downtime. Care must be taken to account for this when deciding whether to use automatic failover or not – especially if you have very stringent uptime requirements.
Related: Should You Choose Database Mirroring?
Lastly, depending on your customer base and the kind of workload you have, you may find that having customers connect to the mirror server after an automatic failover gives them a poor experience – for example, if the mirror server cannot cope with the same workload as the principal, or the public network links to the mirror are slower than those to the principal.
As you can see, you need to carefully consider the pros and cons of allowing automatic failover to occur when configuring database mirroring. And above all else, test your solution once it’s in production to make sure that the application continues to work after a failover – automatic or otherwise.