mercredi 28 janvier 2015

Fencing a failed application node to isolate it from SQL Server


I have a multi-node server application that is backed by SQL Server 2012, often in a clustered or AlwaysOn AG/FCI HA configuration. My application monitors the health of the server nodes and implements a kind of fencing if a node is non-responsive or otherwise determined to be unhealthy. I am trying to figure out how to isolate the failed node from SQL Server since any writes from an unhealthy node could corrupt data.


The approach I'm considering is configuring all nodes to connect to the database with distinct users. If a node was deemed unhealthy, the write privileges for its database user would be revoked by one of the healthy nodes. This seems pretty clean except for the fact that individual users have to be maintained for each node. I briefly considered having one of the healthy nodes kill all database connections from the unhealthy node, but I think that approach is less desirable because it requires all nodes to have the ALTER_ANY_CONNECTION privilege and it would not play nicely with the existing database connection retry logic. Are there any other approaches or best practices that I should consider?





Aucun commentaire:

Enregistrer un commentaire