Microsoft Spotlight: Automatic Failover in Microsoft SQL Server
9/11/13 9:00 AM Laura Pelkey
Microsoft 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. Situations in which this is useful involve disaster recovery scenarios especially.
Using automatic failover requires that it remain synchronized with the primary database at all times so that information is always up to date. This means that a daily or even hourly automated or manual backup procedure will not be sufficient. Instead, each transaction taking place in the primary database must be immediately mirrored on the mirrored server. A managed IT support service can prove helpful in verifying these updates are being performed.
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.
How much downtime can your servers handle? ...Ready to test your theory? Download the free Checklist below to start prepping your business for downtime with disaster recovery.