2

In principle an SQL Server failover cluster presents itself as a virtual machine that applications can connect to oblivious to the fact that the SQL Server is actually a cluster of servers, hence, in principle no additional logic is required within the database access tier of the application.

My question is whether the above is true and whether there are best practice modifications to how the DB access tier operates when using a failover cluster. E.g. presumably when failover occurs there will be a delay that may cause a time-out error at the DB access tier, we are considering putting logic in that tier to re-try [some] DB calls upon a timeout occurring (we already have retry logic for DB deadlocks). This provides another level of protection from errors affecting the application.

If a failover switch occurs and results in the higher application level receiving a timeout error on a service call then that is not seamless switch over. Should we simply be setting our timeouts at a duration that allows for failover?

Thanks.

redcalx
  • 8,177
  • 4
  • 56
  • 105

1 Answers1

1

In principle an SQL Server failover cluster presents itself as a virtual machine that applications can connect to oblivious to the fact that the SQL Server is actually a cluster of servers

Ah? Really? That contradicts documentation. A cluster is basically nothing more than a moving IP address with different installation on different servers, hardly a virtual machine.

in principle no additional logic is required within the database access tier of the application.

Yes and no - a failing node DOES kill all ongoing transactions and connections, obviously, so the CLIENT must be able to react to that and retry. If the client crashes because a connection is down an does not retry, it does not help you that server is reachable again after a second or two.

Should we simply be setting our timeouts at a duration that allows for failover?

No, a connection is broken by failover as the ongoing transaction state is lost. You need to reestablish the connection and then start all Sql commands again that were issued in the transaction.

Note from a security point, clustering is bad and you should use mirroring - you have a specific risk that a failing cluster node turns the database files corrupt in which case the fail-over fails. Mirroring is more robust.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • "A SQL Server failover clustered instance appears on the network as if it were a single computer, but has functionality that provides failover from one node to another if one node becomes unavailable." -- From http://technet.microsoft.com/en-us/library/ms191309.aspx – redcalx Jul 04 '12 at 09:52
  • That is still not the same as being a virtual machine. I can have 10 IIS running computers "appear ON THE NETWORK as being one" with NLB - and sthey are not a virtual machine. All THAT says is that they use the same IP Addres (appear to the network), nothing else. – TomTom Jul 04 '12 at 11:59
  • Point taken. It's a service that is being provided by multiple distinct servers (hardware or virtual), in effect I was using the word virtual in a broader sense of the word, which admittedly is a loaded word given the context. – redcalx Jul 04 '12 at 12:31