0

I'm looking at 2 node synchronous AG hosted on Azure VMs. SQL server version is 2022 enterprise edition. There is file share witness as well.

Each VM has a VM level nightly backup configured so that the entire VM can be restored to previous version for example in case of OS/VM level corruption. Also IT take manual VM backup prior to patching to be able to quickly rollback patched that cause major issue.

SQL database native backups are also configured, but this aspect might not be relevant to this question.

Assuming node1 is primary and node2 is secondary, I'm looking for some information on how SQL server/AG technology will behave in the below scenarios.

Scenario 1: If node1 (primary) has some kind of OS/VM issue and IT restore the VM to yesterday's version.

Case 1 - If AG is in auto failover mode, then during VM restore of node1, AG will auto failover to node2 (due to AG/cluster timeouts). Now node2 is the new primary. Will the AG on node1 [secondary] (now restored to yesterday's version) auto sync and catch up with node2 [primary]? Will node1 think that it is the primary because as of last night it was indeed the primary (or will cluster and AG negotiate this and realise that this is not the secondary)?

Case 2 - If AG is in manual failover mode, then post VM restore of node1, what is expected behaviour? Obviously there will not be a failover because the failover mode is manual. But primary is now having data as of yesterday. How to handle this scenario? Force failover prior to VM restore? Force failover post VM restore? Either ways, post restore, the original primary will think it's the primary. Can this be handled.

Scenario 2: If node2 (secondary) is restored to yesterday's VM snapshot, then irrespective of auto/manual failover setting, there won't be a failover as this is the secondary node. But post node2 VM restore, will the AG catch-up with the primary?

1 Answer 1

1

Each VM has a VM level nightly backup configured so that the entire VM can be restored to previous version for example in case of OS/VM level corruption.

This may or may not be viable, depending on various options set in how the VM is backed up and if there is VSS integration used.

Scenario 1: If node1 (primary) has some kind of OS/VM issue and IT restore the VM to yesterday's version.

Case 1 - If AG is in auto failover mode, then during VM restore of node1, AG will auto failover to node2 (due to AG/cluster timeouts). Now node2 is the new primary. Will the AG on node1 [secondary] (now restored to yesterday's version) auto sync and catch up with node2 [primary]? Will node1 think that it is the primary because as of last night it was indeed the primary (or will cluster and AG negotiate this and realise that this is not the secondary)?

Whether it fails over or not depends. If there are just the two nodes then it won't have quorum, there will be no automatic failover.

Windows Clustering will have had its database restored over. This will have a mismatch in various items. Since there are only two nodes, depending on various situations this could cause an issue. Generally, the node will see that the other node (assuming there have been no WSFC changes since the backup) is more up to date, sync the changes, and restart the clustering items so that everything is then in sync.

SQL Server may have some issues, depending on various items that we have no information. If were any changes to the AG, logins, etc., all will be lost. This may cause an issue in connecting or finding the primary, but generally if the clustering synced up without issue then the AG will at least connect. Unless the database(s) are entirely idle, they won't have a common recovery fork and will not be able to synchronize again. It'll require restoring the log backups, etc., or seeding the database again.

Case 2 - If AG is in manual failover mode, then post VM restore of node1, what is expected behaviour? Obviously there will not be a failover because the failover mode is manual. But primary is now having data as of yesterday. How to handle this scenario? Force failover prior to VM restore? Force failover post VM restore? Either ways, post restore, the original primary will think it's the primary. Can this be handled.

If you didn't fail over already to the secondary, you'll want to do this, otherwise there will be data loss of whatever the VM restore point was... which is also assuming VSS was properly used in the backup. Otherwise the backup in so far as the databases for SQL Server are concerned are garbage.

If you fail over, the same thing as Case 1's answer applies.

If you didn't fail over, there are various outcomes based on how the cluster responds. In the best case scenario, the databases aren't corrupt. Depending on the cluster databases, if the cluster brings the AG online on Node1 and all the metadata checks out (it may or may not, again, various factors) and SQL brings the AG online, then the secondary will attempt to sync and most likely end up in reverting. Since you didn't fail over, this will result in data loss.

Scenario 2: If node2 (secondary) is restored to yesterday's VM snapshot, then irrespective of auto/manual failover setting, there won't be a failover as this is the secondary node. But post node2 VM restore, will the AG catch-up with the primary?

The cluster and AG may or may not go down, based on quorum settings.

Depending on various factors already stated in Case1, it'll most likely connect to the primary, see that's it's very far behind, not be able to negotiate a common point and need to be reseeded or otherwise caught up as previously described.

2
  • I will update my question to add that there is file share witness, so quorum is always positive in all scenarios.
    – variable
    Commented Mar 27 at 14:32
  • It may be, again, depends on the current environmental variables of the time. Commented Mar 27 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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