Problem Summary: HVR SQL Server Requirements for Capturing from Always On AG Target
Tag: When you capture from the standby node of the AlwaysOn cluster, HVR will not be able to add supplemental logging on the base table since the connection is read-only. That is, to successfully use AdaptDDL, you will have to manually enable the supplemental logging on the read-write node.
When you run HVR Initialize against the read-only target copy, HVR will realize that it cannot create the CDC tables. HVR will instead create a script on the source host called supp_log_add.sql that has the commands in it to create the supplemental logging on the source. The CDC tables are not used by Microsoft, nor will they ever contain any data. The only reason they are created is so that Microsoft will log the Primary Key during updates. This will NOT cause any additional overhead on the Primary source nor is it similar to running Microsoft's native CDC Replication.
- HVR will have to create the CDC tables on the Primary.
- The end user will have to create a separate job to move the truncation point which has to run on the Primary.
If you are not using CDC tables or any other kind of replication, then you will need to create a job on the Primary to call sp_repldone on a regular basis. The sp_repldone procedure will move the truncation point within the transaction log so that the log does not continue to grow. This can be executed every time you perform the transaction log backup.
Grants for Log-Based Setup
Step 1: the HVR user should be granted the db_owner role for the source database, but not the sysadmin role. The user needs to be created on the Primary since this is database-level permission. Always On will then replicate the user credentials to the target.
Step 2: Upgrade the HVR user to sysadmin prior to step 6.
Installation and Configuration:
- Create a location and channel to connect to the SQL Server Always On target.
- Run the Initialize process. The Initialize will fail and will produce a script called 'supp_log_add.sql' located in the HVR config directory.
- Run the 'supp_log_add.sql' script against the Primary node.
- Create a job to run sp_repldone to move the truncation point.
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1.
It's not a big deal if this process will truncate something that HVR has not read yet, as HVR will get it from the tlog backup. Note: sp_repldone does not truncate anything, the backup does that. The sp_repldone procedure just moves the truncation point.
- Configure the Capture action with /LogTruncate=LOGRELEASE_TASK.
- Rerun HVR Initialize from the HVR GUI with the Supplemental Logging option unchecked.