Author: Eric Vasbinder

ERPs Applicable:

• Vista

Overview

Change Data Capture (CDC) is a powerful capability, supported by Microsoft SQL, and other databases, to allow for data changes in tables to be tracked and logged, ensuring that insertions, deletions, changes, etc. will be visible and easily consumable by third party tools and applications.  This allows for these tools to receive a streamlined data feed of information that they need without the need to set up heavier replication processes or to enable API access through AppXchange to every endpoint.

However, only certain tables in Vista are supported for CDC.  Other tables are possible but not supported in the application itself.

Please click on the heading for the "Tables Supported" section below to see a LONG, detailed list of all of the tables supported for CDC.

CDC Deployment Considerations

The most important item we must consider with deploying CDC is whether or not you wish to use CDC in a live, production environment in our cloud, or if you wish to test CDC in a pre-production cloud environment during your cloud transformation.

The method that we use for this is slightly different depending upon if you are wanting CDC in a live, production environment, vs. a non-live pre-production cloud transformation environment that WILL BE live in the future.

such as when testing third party integrations, we will need to perform a series of three manual steps.

Another option is to use your Enterprise Test Environment, available for ESB Silver tier or higher customers.  This will allow you to set up CDC prior to go-live with our complete Analytics stack.

Enabling CDC in Pre-Production Cloud Transformation Environments

As we do not normally implement CDC in a customer's production, cloud environment until after that environment is live in the cloud, there are a number of steps that need to be performed that will be manual if done prior to the cloud transformation being complete.  Given this complexity, we do not normally recommend that customers test CDC prior to a cloud go live in their cloud preproduction environment.

However, in the event that enabling CDC in a cloud preproduction environment during testing, prior to the go live itself, is desired, the following manual steps must be performed (NOTE:  an Initial Data Upload from on-premise MUST be completed prior to enabling CDC in your cloud pre-production environment):

1. CUSTOMER:  Submit Cloud Transformation support case with specific list of tables for which you require CDC be activated.

2. TRIMBLE:  Activate CDC on viewpoint database

3. TRIMBLE:  Activate CDC on requested tables

4. TRIMBLE:  Activate CDC Capture and Cleanup jobs using internal deployment tools.

5. TRIMBLE and CUSTOMER:  Ensure direct ODBC connectivity to the Vista DB is available using either the IPSEC VPN or a TLS Database Endpoint (TLS VPN/Load Balancer).

6. TRIMBLE and CUSTOMER:  Use the steps in the following article to create a SQL Service account for querying the data with CDC:  I need a dedicated SQL account for my integration to Vista in your cloud.  How do I set that up? 

NOTE:  Any future data refreshes into this pre-production environment, such as to update data during the transformation testing stage (Stage 5), will cause steps 2-4 to be redone, and an additional step of reassociating the appropriate SQL service account from the cloud MSDB to the newly refreshed Viewpoint records DB will be needed.

Enabling CDC in Live Production Environments

Implementing CDC in a live, production Vista environment is more straight forward and involves an automated set of processes that are executed by default with every new environment that is set up in our cloud.

In this case, our team will automatically set up CDC on the tables against which our data pipeline for TC1 Analytics queries data as part of the environment go live process.  This automatically activates the DB for CDC, turns on the SQL CDC Capture job, and turns on the SQL CDC Cleanup job.  

As such, the only thing remaining for a customer to do who wants CDC added to additional tables, beyond those used for our own TC1 Analytics process is:

1. CUSTOMER:  Submit a cloud engineering support case with the specific list of tables for which you would like CDC turned on.

Enabling CDC in Enterprise Test (Test==Prod) Environments

Available at the Silver tier or higher of the Enterprise Solutions Bundle (ESB), Enterprise test environments are designed to provide the totality of what production environmments provide.  As such, when your Enterprise Test environment is stood up, we should also set up the CDC pipeline for TC1 Analytics.  Given that, again, all a customer would then need to do is to put in a cloud engineering support case with a list of the tables on which they wish to have CDC activated.