Changing the refresh order of temporal tables in a subscription

Before replication begins for a subscription, all in-scope tables that are not active are refreshed. You can determine the order in which CDC Replication refreshes the tables in a subscription by moving tables that are in-scope for replication into groups.

About this task

Each table you decide to move into a group is assigned a sequence number that CDC Replication uses to refresh each table mapping in numerical order. This is useful if you want CDC Replication to refresh your smaller tables before larger tables, or refresh your larger tables first. When refreshing temporal tables, keep in mind that while the temporal table might be small in size, it is possible for the history table to be quite large. This will result in a longer refresh period.

Any remaining table mappings that you did not add to a group are refreshed in an arbitrary order by CDC Replication last.

You can change the refresh order of tables only when the subscription isn't running.

When you set a refresh order, it is not a permanent configuration setting. It is an operational setting, since the list of tables to be included for replication is dynamic and constantly changing. You are simply choosing the order to refresh the tables that are currently in-scope. The refresh order information for your subscription will be deleted when the refresh is complete.

If you mark a table capture point on a table to which you have assigned a refresh order, the marked table will be removed from the refresh order and returned to the Ungrouped Tables list.

Procedure

  1. Ensure that you have ended any active replication on the subscription.
  2. When setting up tables that have referential integrity constraints, ensure that the target tables are empty before starting replication.
  3. Click Configuration > Subscriptions.
  4. Select a subscription.
  5. Right-click and select Refresh Order....
  6. Click Add new group button to create a group.

    Repeat this step to create as many groups as you need.

    Grouped tables are refreshed first, in the order they are specified. All other tables are refreshed after grouped tables.

  7. Select one or more tables from the Ungrouped Tables list.
  8. Select the group to which you want to move the selected tables and click Add table to grouped list button.
  9. Click OK.