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?

  • What determines healthy for this problem? If the nodes are unavailable, AG/FCI are the best routes for manually or automatically isolating the problem nodes and moving traffic to the remaining nodes. Commented Jan 28, 2015 at 17:48
  • @JohnM I just edited the question to clarify that I am referring to the health of the application nodes only, not any of the node(s) that may be hosting SQL Server. I am not aware of any features of AG/FCI that would allow me to isolate specific application nodes.
    – Dan
    Commented Jan 28, 2015 at 18:02
  • 2
    It feels like doing this through SQL is not the ideal way, but I'm struggling to think of a better one. You can try using an IP block table using logon triggers to specify which host you don't want logging in. Commented Jan 28, 2015 at 18:12
  • @JohnM That's a possibility for preventing new connections from the failed application node to SQL Server, but I don't think it would prevent the failed application node from writing to the database over any existing open connections.
    – Dan
    Commented Jan 28, 2015 at 18:45
  • Generate and execute a set of kill commands for the user(s) from host xyz in conjunction with the trigger - not very pretty but it would work. Commented Jan 28, 2015 at 18:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.