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:

Review the PCS Job Service queue to ensure that all current jobs have finished.
Stop the PCS Job Service.
Modify change tracking settings for each individual field as needed by doing the following:
1. Click Tools > Field and UDF Customizations to open the Field and UDF Customizations window.
2. Select a table in the Properties panel that includes the field you want to modify for change history.
3. Select the field you want to set up for change tracking.
4. In the System-Enable Change History 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 through for additional fields. When you finish, click the close icon to close the Field and UDF Customizations window.
Enable change history for PCS by clicking to select the Change History check box in Options. If change history was already enabled, update the setting by clicking to clear the Change History check box and then clicking to re-select the check box.
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 .
  
  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 .
  
  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
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 .
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 and click .
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.
Start the PCS Job Service.