Open topic with navigation

Set Up Enhanced Change Tracking with CDC

Configuring PCS to track data changes with 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 configurations are made during the initial configuration of PCS or while performing system maintenance to avoid any impact to PCS users.

If SQL Server Agent is not running, changes to data in CDC-enabled tables will still be tracked. However, they will not be pulled into PCS until the SQL Server Agent is restored. If the SQL Server Agent is down for greater than 60 days, changes that were made more than 60 days ago will be lost.

Complete the following steps to set up enhanced change tracking using CDC:

1. 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.

2. 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.

3. Set up SQL Server by doing the following:

   1. Connect to your PCS database using Microsoft SQL Server Management Studio.

   2. Open a new query window to enter SQL commands.

   3. Enter text to enable CDC with either of the following configurations:
      
      Continuous Mode: SQL Server Agent will run the process to capture changes on a regular basis, scheduled by default to run every five minutes. Enter the following text in the Query pane, replacing <databasename> with the actual name of your PCS database:

      USE <databasename>
      EXEC configureChangeTrackingCDCSettings @enableCDC = 1

      Continuous Mode with a Custom Polling Interval: SQL Server Agent will run the process to capture changes on a regular basis, scheduled to run according to your desired polling interval. Enter the following text in the Query pane, replacing <databasename> with the actual name of your PCS database and xx with the desired polling interval in seconds:

      USE <databasename>
      EXEC configureChangeTrackingCDCSettings @enableCDC = 1, @oneshotmode = 0, @pollingInterval = xx

      

      Enable CDC Mode in SQL Server Management Studio

   4. Select the entered text and click .

4. To configure the SQL Server Agent to capture the changes, enter the following text in the Query pane, replacing <databasename> with the actual name of your PCS database:

   USE <databasename>
   DECLARE @settings nvarcharkeyvaluepair
   INSERT @settings
   VALUES
   ( 'CDCCapture_MSCDC_Capture_Job_ExecutionMode', 'None' )
   EXEC configureChangeTrackingSettings @settings = @settings

   Then right-click on SQL Server Agent and select Start.

5. Grant each PCS user VIEW SERVER STATE privileges to the database. VIEW SERVER STATE privileges are required so PCS can match the transaction log, which stores the history of data changes, with user information. To grant each user VIEW SERVER STATE privileges, execute the stored procedure grantAxisUsersChangeTrackingPermissions saved in the PCS database.

   Alternatively, you can manually grant each user database permissions. For more information, refer to the MSDN article GRANT Server Permissions (Transact-SQL) (https://msdn.microsoft.com/en-us/library/ms186717.aspx).

1. 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

2. 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 .

3. 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. 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>' )

      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

      Select the entered text entered and click .

      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')

8. 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.