Update CDC-Enhanced Change Tracking

Changes to how PCS tracks data changes while using change data capture (CDC) must be done both in SQL Server Management Studio and from within PCS using the Field and UDF Customizations and Options windows. It is recommended that change tracking modifications are made during the initial configuration of PCS or while performing system maintenance to avoid any impact to PCS users.

Complete the following steps to update enhanced change tracking using CDC:

1. Review the PCS Job Service queue to ensure that all current jobs have finished.

2. Stop the PCS Job Service.

3. Complete the following steps to enable overall change history for PCS:

   1. Click Tools > Options.

      

      Options Window

   2. In the All pane, scroll down to Turn On Change Tracking and select it.

      

      Turn On Change Tracking Option

   Complete the following steps to modify change tracking settings for individual fields as needed:

   1. Click Tools > Field and UDF Customizations to open the Field and UDF Customizations window.

      

      Field and UDF Customization Window

   2. Select a table in the Properties panel that includes the field you want to modify for change history.

   3. In the grid, select the field you want to set up for change tracking.

   4. In the System-Track Changes column, select Yes to track changes made in the selected field while change history is enabled. Select No to not record any changes made in the field, even if change history is enabled in Options.

   5. Repeat steps for additional fields.

4. Enable CDC for tables in the PCS database by doing the following:

   1. Determine which tables you want to enable CDC-based change tracking. Some tables are not tracked by PCS change tracking; tables that are not tracked by PCS should not be configured for CDC-based change tracking.

      To get a list of all tables in the PCS database that are tracked by PCS change tracking but are not (yet) configured to use CDC to track changes, enter the following text in the Query pane, replacing <databasename> with the actual name of your PCS database:

      USE <databasename>
      GO
      SELECT OBJECT_NAME ( parent_id )
      FROM sys.triggers
      WHERE name LIKE '%changelogging'
      AND name NOT IN (
      SELECT name FROM sys.tables WHERE is_tracked_by_cdc = 1 )
      ORDER BY name
      GO

      Select the entered text and click Execute.

      It is recommended to configure CDC-based tracking for any table that undergoes high-volume operations. You can configure CDC-based change tracking for all tables in the resulting list or for a subset of the tables. Any table that PCS tracks but is not configured for CDC-based tracking will use a trigger to record the database changes.

      If a table or field is set up to be tracked using CDC but System-Enable Change History is set to No in PCS, records of the changes will not be accessible from within PCS and may not be retained.

   2. Enter the following text in the Query pane, replacing <databasename> with the actual name of your PCS database, replacing <tablename> with the name of the first table you wish to track using CDC, and adding , ( '<tablename>' ) for each additional table you wish to track with CDC to the end of the text statement:

      USE <databasename>
      DECLARE @tables nvarcharlist
      INSERT @tables
      VALUES
      ( '<tablename>' )

      Once all tables have been added to the statement in the Query pane, enter the following text to the end of the statement:

      EXEC configureChangeTrackingCDCTables @enable = 1, @tables = @tables

      Select the text entered and click Execute.

      The following example script sets up the PCSAppTest database to track the ACCAInspection, ACVGSurveyFolder, and CISurveyFolder tables with CDC-based tracking:

      USE PCSAppTest
      DECLARE @tables nvarcharlist
      INSERT @tables
      VALUES
      ( 'ACCAInspection' ), ('ACVGSurveyFolder'), ('CISurveyFolder')
      EXEC configureChangeTrackingCDCTables @enable = 1, @tables = @tables

5. If CDC is set up to track changes on specific tables that you wish to stop tracking, disable CDC for those tables in the PCS database by doing the following:

   1. Determine which tables you want to stop CDC-based change tracking. Any table that PCS tracks but is not configured for CDC-based tracking will use a trigger to record the database changes. It is recommended to configure CDC-based tracking for any table that undergoes high-volume operations.

      To get a list of all tables in the PCS database that currently have change tracking enabled with CDC, enter the following text in the Query pane, replacing <databasename> with the actual name of your PCS database:

      USE <databasename>
      GO
      SELECT *
      FROM sys.tables
      WHERE is_tracked_by_cdc = 1
      ORDER BY name
      GO

      Select the entered text and click Execute.

   2. Enter the following text in the Query pane, replacing <databasename> with the actual name of your PCS database, replacing <tablename> with the name of the first table you wish to stop tracking with CDC, and adding , ( '<tablename>' ) for each additional table you wish to stop tracking with CDC to the end of the text statement:

      USE <databasename>
      DECLARE @tables nvarcharlist
      INSERT @tables
      VALUES
      ( '<tablename>' )

      The following example script stops using CDC to track the ACCAInspection, ACVGSurveyFolder, and CISurveyFolder tables in the PCSAppTest database:

      USE PCSAppTest
      DECLARE @tables nvarcharlist
      INSERT @tables
      VALUES
      ( 'ACCAInspection' ), ('ACVGSurveyFolder'), ('CISurveyFolder')

   3. Once all desired tables have been added to the statement in the Query pane, enter the following text to the end of the statement:

      exec configureChangeTrackingCDCTables @enable = 0, @tables = @tables

   4. Select the text entered in steps and click Execute.

6. Update the change history setting in PCS by clicking to clear the Change History check box and then clicking to re-select the check box.

7. Start the PCS Job Service.