1

I am researching SQL Server 2016 and AlwaysOn.

One of the new features which sounds awesome on paper is "Database Health Level Detection". From my understanding if a database in a AlwaysOn Group enters a state other than ONLINE than the databases would fail over to the secondary replica.

This got me pondering a few things and I can not find an answer yet and do not have our test lab setup yet either.

If I restore a database, the state of the database will be RESTORING; will that trigger a failover event?

Also with AlwaysOn there is another feature called "Automatic Page Repair". If "Automatic Page Repair" fails, my understanding is that the database enters a Suspended state, which would make me expect the databases to failover as well.

So, is "Database Level Health Detection" only recommended if your cluster is only being used for one application? For example, one of our clusters is going to host about 50 different databases used by our Intranet; if one of those databases needs to be restored or has corruption, I would not want the other 49 to have a fail over event and a small outage, or would that not be an issue?

2

If I restore a database, the state of the database will be RESTORING; will that trigger a failover event?

No, because you can't restore a database that is in an AG. Thus, to restore it'd have to be out of the AG and wouldn't be subject to database level health detection.

... my understanding is that the database enters a Suspended state, which would make me expect the databases to failover as well.

Suspended isn't a database state - suspect is, not sure which you meant. Also, the key with this is that it may "attempt" to failover but doesn't mean it will be successful.

So, is "Database Level Health Detection" only recommended if your cluster is only being used for one application?

It works at the Availability Group level, that's up to you on how and what you put into your AGs. You might have a single app in an AG or you might have multiple. Personally, I would only put the databases that need to be together in the same AG and not co-mingle any other application databases.

For example, one of our clusters is going to host about 50 different databases used by our Intranet; if one of those databases needs to be restored or has corruption, I would not want the other 49 to have a fail over event and a small outage, or would that not be an issue?

If your entire intranet relied on all 50 databases being on the same instance, then yes I'd put them all together... whether or not you want the AG to failover if a single database (might be the most important one or the least important one) has a state change. I can't make that business decision for you. I also can't change the need to have all 50 together.

| improve this answer | |
1

From my understanding if a database in a AlwaysOn Group enters a state other than ONLINE than the databases would fail over to the secondary replica.

No. Automatic failover is triggered only if failure-condition is encountered - [1=OnServerDown, 2=OnServerUnresponsive, 3=OnCriticalServerError, 4=OnModerateServerError, 5=OnAnyQualifiedFailureConditions ] which is at server level and not at database level. The default is 3 =OnCriticalServerError

From BOL :

Damaged databases and suspect databases are not detected by any failure-condition level. Therefore, a database that is damaged or suspect (whether due to a hardware failure, data corruption, or other issue) never triggers an automatic failover.

If I restore a database, the state of the database will be RESTORING; will that trigger a failover event?

No, it wont trigger a failover. Since when you restore a database, you have to take the db out of availability group (AG), restore it and then add it back to AG with full recovery model.

with AlwaysOn there is another feature called "Automatic Page Repair". If "Automatic Page Repair" fails, my understanding is that the database enters a Suspended state, which would make me expect the databases to failover as well.

Automatic page repair - see how it works by reading Paul Randal's blog or here at BOL. Depending if corruption is repairable automatically or not, it might put your db in Not Synchronizing / Recovery Pending or Suspect (not suspended !) and AG health will be unhealthy and synchronization will be not synchronizing. This wont trigger failover.

if one of those databases needs to be restored or has corruption, I would not want the other 49 to have a fail over event and a small outage, or would that not be an issue?

You cannot restore a database that is part of AG. You have to remove it from AG and then restore it and add it back. As I said above, corruption wont trigger failover.

| improve this answer | |

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.