I have a two node SQL Server failover cluster with availability group. This is used for SharePoint. SharePoint rebuilds the indexes where necessary every night. This routinely causes failover. I can't get this resolved with the infrastructure team, so I want to mitigate. Since the index rebuild is scheduled, I am considering:

  1. Suspend secondary node for duration (1 hour)
  2. Change failover from automatic to manual for duration (1hour)

Anything else? Which is best? I know it's not ideal and the root cause should be fixed but I am out of options.

Thanks Andrew

  • Have you tried flipping it to async? – Erik Darling Oct 8 '17 at 12:35
  • If you have a failover during index rebuild, you will have a failover during peak load. Working around this issue is not a good idea. – David Browne - Microsoft Oct 8 '17 at 13:01
  • Have not tried async. Sounds risky as failover is manual with data loss. – Andrew Gaskell Oct 8 '17 at 18:37
  • 1
    Don't use the index jobs SharePoint offers. Among other things that SharePoint releases, there are better options for managing the indexes in those databases. – user507 Oct 9 '17 at 2:08
  • 2
    ola.hallengren.com/… – user507 Oct 9 '17 at 9:02

The AG usually fails over when the secondary and witness can't talk to the primary for a length of time. (I'm simplifying that a little, but for more technical details, check out MS's documentation on cluster quorum.)

To fix this, I'm going to suggest a few different things. You can do one, two, or all of 'em - just start with the things that are easiest for you, and add more as necessary.

First, leave some breathing room between index rebuilds. If you're using the built-in maintenance plans to rebuild indexes, consider switching to Ola Hallengren's maintenance scripts instead. (They do backups, checkdb, and index rebuilds, but for now, let's only focus on your index rebuilds.) They operate via a stored procedure, and one of the parameters for the IndexOptimize stored proc is Delay. By default, there's no delay between index rebuilds, but you would want to add a 5-10 second delay. This will give your IO subsystem and network some time to clear the air.

That by itself may not be enough, so keep reading.

Next, consider tweaking the default AG timeouts. In sections 7-8 of this Microsoft article, they explain how the default timeouts may be too quick for low-quality network connections. In the comments, you also noted that you're hitting lease timeouts, so check out these additional resources:

  • Microsoft support post: How It Works: SQL Server Always On Lease Timeout - in which they write, "If the lease timeout is expiring you likely have a system wide, impacting event taking place that needs to be resolved. If you are experiencing lease timeouts troubleshoot the entire computer you are likely encountering a broader issue than just a SQL Server lease timeout." My money's on this - since you're seeing it during index rebuilds, I'm guessing you have an issue that makes SQL Server unresponsive during that time. For community help with that, follow the instructions in Getting Help with a Slow SQL Server, especially the last section around including sp_Blitz's results in Markdown format in your question.
  • Improvements in 2012/2014 patches for lease timeouts - KB #3112363 adds better diagnostic data for lease timeouts in 2012 SP3 and 2014 SP2. This is only going to give you more troubleshooting data - it's not going to fix the timeouts. (The link I posted above is more about fixing the timeouts long term by fixing the underlying performance problem.)

Then, consider adding network capacity. I'm guessing your servers have just one 1Gb Ethernet port each, and it's getting saturated during your index rebuilds. You could consider upgrading to teamed cards with load balancing, or to 10Gb cards, or to separate heartbeat network cards. I'm not usually a big fan of separate heartbeat networks because they increase complexity, but that might be the cheapest/easiest solution here. (Start with teaming first though.)

The above steps will let you keep your automatic failover AND your index rebuilds. However, I'd be a bad database guy if I didn't suggest that maybe rebuilding every index, every night, is a little bit overboard. Not only is it causing your AG problems, but it's also inflating your tranaction logs, causing those backups to take longer too. For more on that, watch my GroupBy session, Why Defragmenting Your Indexes Isn't Helping.

  • Thank-you for the response Brent. I am not using the built in maintenance plans of SQL Server because this is SharePoint. SharePoint has a set of timer jobs that include handling the index rebuilds and its recommended to let SharePoint handle it. For this reason, unless I disable the timer job for index fragmentation and use a custom one, I cannot change the interval between successive database defragmentation attempts. – Andrew Gaskell Oct 8 '17 at 18:34
  • We already have a separate heartbeat network on separate NICs. Index rebuild does not occur every night for every database. It depends on the level of fragmentation found and this is handled by a built in sp called by the SharePoint timer job. I have however just increased the timeouts from 10secs to 30secs. Any suggestions on a max value that I can use would be appreciated. – Andrew Gaskell Oct 8 '17 at 18:35
  • @AndrewGaskell if you've got a separate heartbeat network, then there's something else going on. You'll need to go through the cluster logs to figure out what triggered the failover. For example, I've seen one case where the sysadmins had set priority boost on for the SQL Server, and that caused a problem with some proprietary storage software they were running, and that software couldn't keep up, causing SQL Server to lock up. – Brent Ozar Oct 8 '17 at 21:04
  • Looking in AlwaysOn_health Externded events I can see that the lease expires shortly before failover. Checking FailoverClustering Diagnostic Logs, I can see that based on the timestamps, the lease expiry was preceeded by the diagnostics heartbeat being lost. These are my settings for the AG: LeaseTimeout: 40000, HealthCheckTimeout: 60000, VerboseLogging: 0, FailureConditionLevel: 3 – Andrew Gaskell Oct 9 '17 at 8:24
  • @AndrewGaskell OK, great! I updated the answer with more details about fixing the lease timeouts. – Brent Ozar Oct 9 '17 at 10:49

Couple of things to add on Brent's suggestions :

  • Switch to Async when starting Index maintenance routine and monitor the log_send_queue_size. If it exceeds certain threshold (you have to test it as per your environment) then do not proceed with rebuild instead wait for it to go down and then resume index maintenance. You can also put some time limit and carry on the operation. Make sure to switch back to SYNC - your original setting.
  • Use MAXDOP =1 when rebuilding indexes and ONLINE=ON options. Also in newer versions, you can leverage WAIT_AT_LOW_PRIORITY, MAX_DURATION,ABORT_AFTER_WAIT options.
  • Use high performance power plan to help for the single threaded redo process on replicas.

Make sure to check max memory as I have encountered that due to memory pressure, the AG redo thread dies leaving AGs not syncing causing all sort of issues.

Doing Index rebuilds, should not cause AG failover unless you are running out of worker threads or hitting an unknown bug in sql server.

I agree with Brent that doing Index rebuilds daily tells you to look into adjusting fill factor and investigate if it is really necessary to do it ?

Ref: Recommendations for Index Maintenance with AlwaysOn Availability Groups

  • These are great suggestions @Kin, but would require me to disable the built in timer jobs for SharePoint that handle the defragmentation and do this myself. Thats OK if this is the only option. I am waiting on Brent and Shawn for responses as well. Thank-yoiu. – Andrew Gaskell Oct 9 '17 at 8:23

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.