Automatic Failover in Microsoft SQL Server

10/5/11 9:55 AM Gaynor Hay Servers, Microsoft

FailoverMicrosoft SQL Server 2008 and later offer a feature known as "automatic failover" designed to help guarantee database stability.  Automatic failover is useful in situations in which information in the primary or main database becomes unavailable for some reason.  In these cases, the automatic failover feature causes the secondary or mirror server to function as the primary server until primary server function can be restored. 

Using automatic failover requires that the primary database remain synchronized at all times so that its mirror is always up to date should it be needed.  This means that a daily or even hourly automated or manual backup procedure will no longer be sufficient.  Instead, each transaction taking place in the primary database must be immediately mirrored on the mirrored server.

Automatic failover can allow enterprises to continue workflow without interruption even when their activities rely heavily on access to the database and manipulation of its records.  However, in order for automatic failover to provide this benefit, it must be set up and maintained correctly, typically by IT services personnel working in a managed services model. 

One important facet of initial setup is that the physical media of the primary and secondary databases must be kept separate.  This can be accomplished by installing them on different servers on the same corporate network, but more robust protection can be achieved by making sure the two databases reside in different geographical locations as well.  In the event of a natural disaster, for example, it can be crucial to have one copy of the database located in a different city or state.  This can be accomplished by storing it in the cloud.


Whitepaper: 6 Do’s & Dont’s for Choosing the Right IT Service Provider