Using Management Console to resume mirroring after a DDL change occurs

You can use the Management Console to resume mirroring after a DDL change.

1. End all DML activity on all the tables that will have structural changes.

   This would mean stopping any application that is writing to the table prior to making the DDL change.

2. Ensure there are no open transactions involving a table that will be affected by a DDL change.
3. End replication for all running subscriptions in the CDC Replication instance
   • Stop all subscriptions at head of log using a controlled shutdown. The controlled shutdown must complete normally - do not shutdown immediate or shutdown abort or otherwise stop the product during the controlled shutdown sequence. If the controlled shutdown does not complete normally, then a Refresh must be performed to bring the table contents back in sync.
   • Ensure that CDC Replication has reached head of log or has no latency.
   • Wait for all subscriptions to end replicating, including the ones that are only performing Refresh.
   1. Click Monitoring > Subscriptions.
   2. If the subscriptions are available for editing, right-click one or more subscriptions and select End Replication.
   3. Depending on your version of CDC Replication, choose from the following options.
      CDC Replication version 6.5 or later:

      Normal

      CDC Replication completes in progress work and then ends replication. If a refresh is in progress, Normal will complete the refresh for the current table before replication ends.

      Normal is the most appropriate option for most business requirements and is the preferred method for ending replication in most situations.

      Immediate

      CDC Replication stops all in progress work and then ends replication. Starting replication after using this option can be slower than using the Normal. If a refresh is in progress, the refresh for the current table will be interrupted and then replication will end.

      You should ensure that all dependent source database logs are available before ending replication using the Immediate option. CDC Replication may need to reprocess all the dependent source logs when you restart the subscription. If CDC Replication is currently processing a long running transaction when you end replication with Immediate, CDC Replication may have to resume replication from the earliest open transaction in the database logs. Use the dmshowlogdependency command to determine which logs are required.

      Use this option if business reasons require replication to end faster than Normal at the expense of a slower start when you resume replication on the subscription.

      Abort

      CDC Replication stops all in progress work and then ends replication rapidly. Starting replication after using this option can be much slower than the Normal. A refresh in progress will be interrupted and the target will stop processing any data that has not been committed before replication ends.

      You should ensure that all dependent source database logs are available before ending replication using the Abort option. CDC Replication may need to reprocess all the dependent source logs when you restart the subscription. If CDC Replication is currently processing a long running transaction when you end replication with Abort, CDC Replication may have to resume replication from the earliest open transaction in the database logs. Use the dmshowlogdependency command to determine which logs are required.

      Use this option if your business reasons require a rapid end to replication and you are willing to tolerate a much slower start when you resume replication on the subscription. A sudden business requirement for an unplanned shutdown of your source system may require this option for ending replication.

      Scheduled End

      This option will process all committed database changes in the source database and then end replication at the indicated point with the Normal option.

      Now

      End replication at the current source system time in your source database log. The source system time when replication will end is set when you click OK.

      Specific Date/Time

      End replication with the Normal option at the specified date and time. CDC Replication displays the UTC offset (in minutes) of the source database.

      Specific Log Position

      End replication with the Normal option at the specified log position. CDC Replication displays the format of the log position for the source datastore. This option is only available for supported source datastores.

      This option is not available if all the tables in a subscription are currently refreshing.

      CDC Replication version 6.3:

      Controlled

      CDC Replication completes all in-progress operations and applies pending changes to the target table.

      Immediate

      CDC Replication interrupts any in-progress operations and does not apply pending changes to the target table.

   4. Click OK.
4. Ensure that logging is configured for all DDL-affected source tables
5. Apply the DDL changes to all affected source tables in your database.
6. Update the source table definitions
   1. Click Configuration > Subscriptions.
   2. Select the subscription.
   3. Select the mapped source and target tables in the Table Mappings view.
   4. Right-click the table and choose Update Table Definition > Source Table.

   This will update the source table definition, as well as remap the new columns if the names match. The table status will be reset to Mirror/Parked.

7. If DDL changes have occurred on the target instance, update the target table definitions.
   1. Click Configuration > Subscriptions.
   2. Select the subscription.
   3. Select the mapped source and target tables in the Table Mappings view.
   4. Right-click the table and choose Update Table Definition > Target Table.

   This will update the target table definition, as well as remap the new columns if the names match. The table status will be reset to Mirror/Parked.

8. Check the table mapping for correctness.

   When DDL modifies source columns, make sure the target table can accommodate the new data format (for example, going from CHAR(1) to CHAR(10) etc.). This can be done in the Table Mappings Details area.

   If necessary, manually remap the changed columns and verify the new mappings. The CDC Replication auto-mapping feature will map identical column names. However, since the auto-map is a best effort algorithm, verify and adjust your mappings to suit your business needs.

9. Change the replication method to Refresh for all of the affected subscriptions .
   1. Click Configuration > Subscriptions.
   2. Select the subscription.
   3. Select the mapped source and target tables in the Table Mappings view.
   4. Right-click the table and choose Change Replication Method.
   5. Select Refresh (Snapshot).
   6. Click OK.

   This will set the tables to Mirror/Refresh and will temporarily mark the tables as out of scope.

   Note: If the affected tables reside in multiple subscriptions, change the replication method of all the affected subscriptions to Refresh first before proceeding to next step.
10. Change the replication method back to Mirror for all of the affected subscriptions.
    1. Click Configuration > Subscriptions.
    2. Select the subscription.
    3. Select the mapped source and target tables in the Table Mappings view.
    4. Right-click the table and choose Change Replication Method.
    5. Select Mirror (Change Data Capture).
    6. Click OK.

    This will set the tables to Mirror/Refresh.

11. Mark the capture point to avoid the Refresh operation by following these steps:
    1. Click Configuration > Subscriptions.
    2. Select the subscription.
    3. Select the mapped source and target tables in the Table Mappings view.
    4. Right-click the table and choose Mark Table Capture Point for Mirroring.
    5. Verify that the table statuses have changed to Mirror/Active prior to starting replication.

    This step marks the capture point to the head of the log.

12. If there is a requirement to perform a Refresh of the table or if refreshing the table is the preferred means of recovering, you can flag the table for refresh and initiate the Refresh operation.
    1. Click Configuration > Subscriptions.
    2. Select the subscription.
    3. Select the mapped source and target tables in the Table Mappings view.
    4. Right-click the table and choose Flag for Refresh.
    5. Select Standard Refresh.
    6. Click OK.

    When the refresh has successfully completed, you can restart mirroring for the subscription.

    Note: You must perform a Refresh under the following conditions:

    • Logs need to be re-scraped from the time before the DDL change was performed; that is, the restart position is before the DDL change.
    • Tables are dropped and recreated.
13. Start mirroring for the subscriptions that were halted due to the DDL change.
    1. Click Monitoring > Subscriptions.
    2. If the subscription is available for editing, right-click one or more subscriptions and select Start Mirroring.

       Refresh Details is displayed if you have one or more tables flagged for refresh and allows you to view the refresh configuration of these tables. Continuous mirroring will not start until the refresh of these tables is complete.

    3. Select either the Continuous or Scheduled End option.
    4. Click OK to start mirroring.

       Mirroring continues until you end replication.