Recently I was talking with a few SQL Server professionals about what failover strategy they employ? The replies were quite different; This conversation emphasized to me why products such as SQL Server have so many failover strategies because each method has different characteristics.
The key driving factor behind the failover method you choose is, "how much time can you afford to be without an active version of SQL Server?" Second to the duration of failover time is the question of cost? How much can you afford to implement a failover strategy? That cost may be based on initial purchase of hardware and software. A more realistic cost also includes the cost for maintaining the process as your system use changes. What does it take to maintain changes in your database implementation?
SQL Server has built in strategies. Third party vendors have strategies that exist outside the Microsoft solutions, either extending or replacing native failover strategies.
SQL Server 2000 and later supports Database Clustering allowing multiple servers (nodes) to work cooperatively to host database instances. One node is active at a time for any instance. It also supports Log Shipping and database replication. Each of these have different behaviors regarding how long it takes to failover, and work that must be performed manually, or happens automatically. We’ll talk about that in a later editorial.
SQL Server 2005 introduced Database Mirroring. This failover strategy is executed at the database level. Each database must be mirrored independently.
SQL Server 2012 introduced always on wich uses a number of existing failover strategies with additional extensions assuring there is no single point of failure in your database services. It follows an Azure like stragy of databases assuring that there is always an operating backup to your database server capable of taking over the load should you primary database stop functioning for any reason.
For the next couple of days I’ll take a very shallow look at these different techniques with the intention of providing an overview of each for those who are not familiar with database failover. You are then encouraged to learn more about those methods which you find most useful.
Cheers,
Ben