Is MS SQL server clustering capable of performing a seamless fail over between nodes? That is, where the clients wouldn't notice anything had changed except perhaps a short delay during the transition.
Add a comment
|
3 Answers
only way i know of achieving this is with the use of hyper-v and using 'live migration'. This allows for 'stateful' failover of having the virtual machine transition from one physical node to another. You will need to use this in conjunction with over HA options as you'll still get down-time when you need to patch the OS of your VM. Database mirroring may be a good candidate, but this is not 100% transparent in fail-over.
answered Mar 6, 2010 at 14:10
AlwaysOn (available in SQL 2012 and later) provides a three-server arrangement (primary, secondary, listener). When used with SQL Server Native Client 11.0 (SNAC 11.0), it allows client applications to automatically and transparently reconnect to the availability group after a failover. The configuration is much simpler than it ever used to be with clustering, dynamic routing rules, all that stuff.
As Paul Randal has explained, the recovery may not be instantaneous. However, clients that are idle during the failover theoretically would not notice anything but a delay on the next query.
High availability mirroring, where you have a 3 server setup, with a principal, mirror and a witness is most of the time a very fast failover, however, as Paul Randal blogged recently, that is not guaranteed to be the case. See this post (link below) and (can't post the other one, not enough rep). There is no absolute 100% seamless failover, but on the SQL Server level, mirroring is your best bet since it only fails transactions in progress and clients automatically connect to the other node. As Nick said, running on VM and using live migration is likely your best bet for HA, but does not allow for much flexibility in changing configuration of your environment, f.ex changes to your OS.
