Failover test case

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jinshuang - 12 Feb 2008 22:57 GMT

I am new both for cluster and SQL, and I have built a new two nodes SQL
cluster. For testing failover, I am planing to write a long sql script and
have a client excute and write data in. During the writing, I am going to
take off network or power from node 1. can I expect the script would finish
properly since SQL failover to node 2?

Reply to this Message

Jason Massie - 12 Feb 2008 23:02 GMT

No, it will behave just like SQL restarting.

Signature

Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio

>I am new both for cluster and SQL, and I have built a new two nodes SQL
> cluster. For testing failover, I am planing to write a long sql script and
> have a client excute and write data in. During the writing, I am going to
> take off network or power from node 1. can I expect the script would
> finish
> properly since SQL failover to node 2?

Reply to this Message

Linchi Shea - 13 Feb 2008 04:31 GMT

In addition to what Jason said, if it's a strict T-SQL script, it'll just
stop where the node crashes. For the failover to be 'transparent' to the
client, the client must have error handling logic to re-establish the
connection and re-submit the query/transaction that is aborted. Also, as is
the case with a restart, any in-flight transaction (i.e. uncommitted
transaction) is lost and will be rolled back when the instance starts up on
the other node.

Linchi

> I am new both for cluster and SQL, and I have built a new two nodes SQL
> cluster. For testing failover, I am planing to write a long sql script and
> have a client excute and write data in. During the writing, I am going to
> take off network or power from node 1. can I expect the script would finish
> properly since SQL failover to node 2?

Reply to this Message

Matthew White - 14 Feb 2008 15:25 GMT

I too am looking to test a 2 node SQL cluster. I am new to SQL as well.
I've built the cluster and am ready to test. I realize that when node 1 goes
down, the current SQL connection will drop, but I need to test being able to
connect again, add something to the DB, fail the second node, connect again,
make sure the records have been added, etc. Can someone offer any good ways
to test this? I need to migrate to this cluster in 9 days and need to get it
properly tested.

Thanks so much for any input.

Regards,

Matthew White

> In addition to what Jason said, if it's a strict T-SQL script, it'll just
> stop where the node crashes. For the failover to be 'transparent' to the
[quoted text clipped - 11 lines]
> > take off network or power from node 1. can I expect the script would finish
> > properly since SQL failover to node 2?

Reply to this Message