SQL High Availability Options

There are several editions of SQL server which offering different levels of capability and pricing.  Some of the features used to make SQL highly available are version dependent.  Options are:

 

Uses the backup mechanism to provide a "Warm Standby" copy of the SQL database which could be set live with some application reconfiguration.

 

Data to be made resilient is "Published" – 2nd server would subscribe to the data and update itself with changes published by the main server.  Again would require manual intervention to fail onto the backup server – a "Warm Standby"

 

Similar to log shipping but failures can be detected and failovers can be made automatically.  Could be considered a "Hot Standby"

 

This is the only method to implement true "high availability".  A SQL Server failover cluster is implemented on a Windows Server failover cluster.

 

It should be noted that there will be hardware considerations for all of the above methods (for example,  Increased load requiring more powerful hardware).  More advanced options may need special hardware (for example, a high performance shared disk resource (SAN)).

 

When considering which technology to employ it should be noted none of the methods replace a properly planned and functional data backup system.  A properly designed strategy may contain a mix of different methods to protect against data loss.

 

See the link below for more detailed information on the options available for configuring SQL fault tolerance and high availability.

 

 

Note there are 3^rd party products available that can offer similar functionality, for example Doubletake.  CallScripter has no recommendations in this area.