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 application nodes (not the database node(s)) and implements a kind of fencing if an application node is non-responsive or otherwise determined to be unhealthy. I am trying to figure out how to isolate the failed application node from accessing SQL Server since any writes from an unhealthy node could corrupt data.
The approach I'm considering is configuring all application 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?