Looking for some help with the Verification of MS SQL AOAG database members. I have a simple AOAG setup across 2 SQL Servers for DB1.
I want to offload CheckDB to an offline server on a remote site so the assigned policy Schedule snapmirrors the data and log volumes etc. to the second site and the remote serve is selected as the verification server.
What I am seeing is that the Database from the Primary node always fails verification as an NTFS storage snapshot cannot be created, the other database from the secondary node is completed ok on the same server. I have proven this by failing over the AOAG and re-running the Snapcenter Job., with the primary always failing.
The fix seems to be to specify the Tablock option on the verification tab of the SnapCenter Policy. This allows both the Primary and Secondary Database files to complete Checkdb on the remote server successfully.
I have also found that if I clone the primary DB and mount on a remote server that I can execute CheckDB from SQL manually with no issues.
Therefore, can anyone confirm if this issue with checking a Primary AOAG Database is to be expected and that Tablock should always be specified for an AOAG database when verify remotely?
Or suggest if I am missing something in my setup. Note file/folder permissions have all been checked and verified, and SQL servers are same version etc
Finally does anyone know what snapcenter skips (other than the NTFS snapshot) when the Tablock option is selected?