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?
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:
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?
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.*