CODEWITHSUNDEEP
sql-servercluster-computing
Duncan
Duncan

Reputation: 10291

Developing for SQL Server Cluster

If I am developing for an environment that will make use of SQL Server clustering, what (if anything) should I be aware of?

From what I've seen, I should handle an exception on a database operation and have two options:

Is this correct? Is this all I need to do?

It's an ASP.NET app, but I don't think this should make a huge difference to the approach.

Any advice would be appreciated

Duncan

Upvotes: 3

Views: 606

Answers (1)

SqlRyan
SqlRyan

Reputation: 33914

Just retrying the connection should be enough. When an instance is in the middle of a failover and our application has a connection open, here's what happens:

  1. The connection is working
  2. SQLException: "A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"
  3. SQLException: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)"
  4. The message in step 3 will be repeated until the failover is complete.
  5. Reconnect successfully.

In this case, you'd only need to attempt to retry the connection. If your SQL server is generally pretty responsive, you can try wrapping your connection code in a class where a database call times out after 30 seconds, but inside the class, use a shorter timeout and retry a few times. That will give your application at large only one call to make, and allow the internal connections time to fail over if need be.

The transition of the network name and IP address are seamless though, so there's nothing special you'll need to do to recover if a failover occurs - just reconnect and keep chugging. Keep in mind, though, that any in-process transactions will be rolled back, so if you had anything that was running when the disconnect occurred, you'll need to resubmit it on the new node.

Upvotes: 3

Related Questions