New Cluster Testing - Best Practice

Question

We've finished setup of a 4-node SQL Server 2005 cluster. We are using Windows 2008 R2 as the underlying OS.

We are looking for suggestions on a set of tests we could perform to test failover of the SQL Instances?

Answer 1

Not even close to comprehensive, but I'd start with: 1. Pull the ethernet cable(s) for your public ip interface on your primary/active node. Confirm the failover. 2. Pull the SAN fibre cable(s) for your active node. Confirm the failover. 3. Pull the power cable(s) for you active node. Confirm the failover.

Those represent the main types of failure that MS Clustering will compensate for in the first place...

I think I'd have my real/prod db detached or offline while I played these games.*

Answer 2

The link that Thomas provided in his comment on the question is a good resource of some scenarios to test. Bob also provided some tests that are good, many of which are included in the blog post linked.

I would say in addition to those great lists of "what" to check, you also want to look at various application scenarios to test failover during. I've seen a lot of clusters get built and then get tested from the server team/DBA team side - but the application teams were never involved.

What happens to your applications during that failover? Now it really mostly looks like a restart to the application (effectively that is what the failover is.. Service goes down on Node A.. Service goes up on Node B.. SQL does what it does when SQL is shut down and restarted or when it crashes and comes back up.. DBs go through recovery on the other side of the restart, connections are all dropped where they are, etc.) So it may seem pointless to test, but it is good to see what kind of process the users will experience and to understand what processes the application owners and helpdesk folks, etc. need to do when that failover happens.

You should ask questions like:

1. Is there some component that needs to be reset or restarted after a database restart?
2. Do you have to follow a very specific order of operations for a SQL Server shutdown/restart during maintenance windows? That probably looks like the application or middleware servers going down first, then the database. In a cluster failover you get the DB going down first. What does this mean for you and your company?
3. Do your third party software package vendors support installations on a cluster? They should, it isn't much different but they may have guidance of things to consider during a failover.
4. Do your apps automatically try to reconnect a certain number of times? If not, can they? This may be a good thing to consider in your clustered environment to save some time in the reconnection and getting back to work post failover.

And when you do some of these tests, have your application running (not live production...) with users or test scripts performing work during the failover. What happened? See anything that needs to be taken care of?