SQL Server 2014 – Failover Cluster Performance with Many Databases

clusteringfailoversql serversql server 2014

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:

Performance during a failover: Will there be an outage during the failover operation?
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?

Best Answer

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

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)

Edition of SQL Server. Fast recovery which is enterprise edition feature could bring database online after second phase(redo) of recovery
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.
The hardware strength and network capabilities supporting the cluster. Some of this is documented here.

Related Solutions

Sql-server – Executing Powershell SMO against a cluster with elevated privileges to update service accounts

I ran into the same problem, looks like its an SMO bug (1st link) (however I have not tried the CredSSP stuff from the second link below)

http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/265ee700-182d-4a99-96f8-cada117fd3d3

http://www.ravichaganti.com/blog/?p=1230

Please let me know if that works..

Sql-server – SQL Server cluster not available after failure of the “main” server

Without looking at the cluster logs or any other form of error reporting, all I can do is guess here.

But my initial thoughts are that you may have hit the failover threshold. By default, this is going to be set to a maximum of n - 1 failures (where n is the number of nodes) over a period of 6 hours. Yes, that's a long time, and especially in a 2-node cluster that isn't very many failures (only equates to one failure). This threshold is set to prevent the Ping-Pong effect of cluster groups.

In production, this is probably a good thing. But in testing/development/non-prod it is pretty common to run into this initially perplexing problem, as you may be trying to consecutively failover. It is worth noting that these parameters are 100% configurable. All you need to do is go into the properties of the cluster group, and in the "failover" tab you will have the option to change these two parameters (Maximum failures in the specified period and Period (hours). Here is what this looks like in the Failover Cluster Manager:

enter image description here

Note: in my screenshot, the threshold is set to 2 because I have a 3-node cluster.

Likewise, this can be seen with PowerShell (accessing the FailoverClusters module).

# you may need to set your cluster group name to whatever it is named
# in your environment
#
Get-ClusterGroup -Name "SQL Server (MSSQLSERVER)" | 
    Select-Object Name, FailoverThreshold, FailoverPeriod

Best Answer

Related Solutions

Sql-server – Executing Powershell SMO against a cluster with elevated privileges to update service accounts

Sql-server – SQL Server cluster not available after failure of the “main” server

Related Question