Auto VDB Restart for the SQL Server AG virtual database
Sometimes, the SQL Server AG virtual database might become Inactive when there is an engine reboot or the disk (iSCSI mounts) is unavailable due to network partitioning. In such cases, the Auto VDB Restart option is useful in bringing the AG virtual source back to the RUNNING state.
The Auto VDB Restart can only fix an AG virtual database provisioned with the backupBased parameter set to true because it works on the primary replica database.
To enable Auto VDB Restart, set the following attributes while provisioning:
set source.allowAutoVDBRestartOnHostReboot=true How to restart the SQL Server AG virtual source when backupBased=false?
In case of an event like an engine reboot, where secondary replicas go into a NOT SYNCHRONIZED state, try the following approaches:
Follow the instructions in the Microsoft blog, which states that restarting the SQL Server instance is the only solution to correct the states of secondary replicas. Additionally, resume data movement on the secondary replica after restarting the SQL Service.
Set the backupBased parameter to true and attempt to disable and enable the VDB. In this approach, it is mandatory to recreate the AG virtual database, which is a time-intensive operation.
SQL Server AG VDB quota and refresh policies
Users can configure a quota policy for an AG virtual source but not for the replica sources. However, the quota set under the policy applies to the aggregate storage taken by all the replica sources.
VDB refresh policy is also supported for AG virtual databases. Refer to the CLI cookbook-creating a policy page to set up a policy via CLI.