I support a multi-instance, two node failover cluster (active-active). Both instances are running SQL 2014, on Windows Server 2008R2. The nodes each have 3/4 TB of memory and 32 cores (64 with HT). SQL is configured with a Max memory of 350GB for each instance. There are between 60 and 100 databases per instance with 3-5 TB of datafiles in per instance.
I’m having some issues with failover times that I’m trying to resolve. The issue appears to be centered around shutting down the SQL instance prior to the failover. When a manual failover is performed, we do the following to speed things up to this point.
Checkpoint all databases just prior to failover to get as many dirty pages written to disk prior to the beginning of the failover as possible. Failovers were much longer prior to adding this step
Manual failover is initiated, SQL goes into SA only mode for 1-2 minutes and then the SQL instance stops.
- It takes another 1-2 minutes to release the memory back to the OS before the failover occurs and the resource comes up on the other node.
If the SQL instance has only limited data in memory the failover happens much more quickly.
We looked at I/O and CPU metrics and don’t see any significant issues.
I’m looking for resources and ideas to help reduce the time it takes for this failover to happen. Thanks, -Luke.