Issue

SQL Server Requirements for Capturing from Always On AG.

Note: When you capture from the standby node of the AlwaysOn cluster, Local Data Processing 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

Local Data Processing

Description

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

1. Local Data Processing will have to create the CDC tables on the Primary.
2. 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 Local Data Processing 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 Local Data Processing 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 will truncate something that Local Data Processing has not read yet, as Local Data Processing 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 Local Data Processing Initialize from the Local Data Processing GUI with the Supplemental Logging option unchecked.