SQL Server Requirements for Capturing From Always On Availability Group (AG)

Issue

SQL Server Requirements for Capturing from Always On AG.

NOTE: When you capture from the standby node of the AlwaysOn cluster, HVR 5 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.

Environment

HVR 5

Resolution

When you run HVR Initialize against the read-only target copy, HVR 5 will realize that it cannot create the CDC tables. HVR 5 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.

Setup

HVR 5 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 user to sysadmin prior to step 6.

Installation and Configuration

1. Create a location and channel to connect to the SQL Server Always On AG.

2. Run the Initialize process. The Initialize will fail and will produce a script called supp_log_add.sql located in the HVR 5 config directory.

3. Run the supp_log_add.sql script against the Primary node.

4. 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 truncates something that HVR 5 has not read yet, as HVR 5 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.

5. Configure the Capture action with /LogTruncate=LOGRELEASE_TASK.

6. Rerun HVR Initialize from the HVR GUI with the Supplemental Logging option unchecked.