Problem Summary:  HVR SqlServer Requirements for Capturing from Always On AG Target

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #21612
    Donna Zehl
    Keymaster

     

    Description:

    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.

     

     

    Setup

    1. HVR will have to create the CDC tables on the Primary.
    2. The enduser 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.

     

    Requirements

    Grants for Log Based Setup – The HVR User should be granted a db_owner role for the source database, but not sysadmin role. 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:

    1. Create a Location and Channel to connect to the SQL Server Always On Target
    2. Run the Initialize process

    The Initialize will fail and will produce a script called ’supp_log_add.sql’ located in the HVR config directory

    1. Run the ’supp_log_add.sql’ against the Primary node
    2. 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 procedure sp_repldone just moves the truncation point.

    1. Configure the Capture action with /LogTruncate = LOGRELEASE_TASK
    2. Rerun Initialize from the HVR console and uncheck ’Supplemental Logging’
    #23067
    SMarella
    Participant

    How does the ADAPT DDL work in this context with cdc supplemental logging

    #23073
    Mark
    Keymaster

    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.

    #23862
    SMarella
    Participant

    Thanks Mark

Viewing 4 posts - 1 through 4 (of 4 total)
  • You must be logged in to reply to this topic.

© 2019 HVR

Try now Contact us