2

We are considering implementing SQL failover clustering and I have a question about how SQL Failover clustering works with regards to performance. Specifically, we would have about 1000 dbs mounted on said cluster, and my concerns are:

  1. Performance during a failover: Will there be an outage during the failover operation?

  2. How long can I expect the failover to take?

I'm not sure how it's implemented, but I'm just imagining the analogy of shutting down a SQL server service on one machine, and once complete starting up on another machine. With all of these dbs, just recycling the SQL service takes about 10 minutes. Should I expect the same from the failover?

3

1.Performance during a failover: Will there be an outage during the failover operation?

Yes there would be some outage during cluster failover.. During failover SQL Server is stopped on current node and failedover/moved to other node. After this SQL Server is brought online on the node to which it is failed over. Sometime this is so quick that application users might not notice. But the fact that SQL Server is restarted means you should always keep in mind that there might be some outage

  1. How long can I expect the failover to take?

There are lot of factors that influence how long it would take for failover cluster to come online on other node. The factors are (but not limited to)

  1. Edition of SQL Server. Fast recovery which is enterprise edition feature could bring database online after second phase(redo) of recovery
  2. Amount of transaction log SQL Server has to rollforward and rollback to bring database online in consistent state. Because database would go through crash recovery this would always be the one influencing the time most. If database takes lot of time to recover due to large amount of uncommitted transactions gettign rolled back it would definitely increase your time.
  3. The hardware strength and network capabilities supporting the cluster. Some of this is documented here.
  • 1
    Thanks @Shanky. My question 2 was more of a relative question. I could have better phrased it as "Is the delay equivalent of starting up a SQL service when the SQL service had been already shut down." My expectation is that because the failover server wasn't running SQL up until that point, it would have to spin-up and mount each database. Since we have hundreds of dbs it typically takes a few minutes to full spin up. You answered my question... thanks! – ebol2000 Oct 7 '15 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.