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 then HVR will not be able to add supplemental logging on the base table since the connection we have is read-only. I.e. to successfully use AdaptDDL you would 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.sqlthat 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 we will need to create a job on the primary to call sp_repldone on a regular basis. The procedure sp_repldone 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 take a Transaction Log Backup.
Grants for Log Based Setup – The HVR User should be granted a db_owner role for the source database, but not sysadminrole. The User needs to be created on the Primary since this is a database level permission. Always On will then replicate the user credentials to the Target.
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' 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
, as HVR will get it from the tlog backup. Note sp_repldone does not truncate anything, the backup does that. The procedure sp_repldone just moves the truncation point.
- Configure the Capture action with /LogTruncate = LOGRELEASE_TASK
- Rerun Initialize from the HVR console and uncheck 'Supplemental Logging'