Background
Recurrency’s integration connects directly with your ERP’s database to establish a real-time data pipeline between the two systems. This allows Recurrency to display accurate, up-to-date information within our web-application. This data is also used to calculate other data points such as forecasts and replenishment values.
Our system is designed to performantly and continuously scan your DB for new data and ingest it into our system; however, we sometimes run into issues due to ways that ERPs have configured their underlying Microsoft SQL database. Most ERPs provide a “last updated” time for every row in the database but we see cases where the ERP is not consistently updating those values, which makes it difficult to understand when something has been updated. This then leads to Recurrency missing updates and then displaying stale data.
We see this class of problem with:
On-prem, Epicor Prophet 21
On-prem, SAP BusinessOne
MSSQL databases support change tracking which is specifically designed to support the type of integration Recurrency performs with your database.
Change tracking creates a log of:
What row was changed
When it was changed
What operation was performed on the record (e.g. UPDATE, INSERT, DELETE)
Enabling change tracking can eliminate data inconsistency issues and ensure that Recurrency maintains consistent, and up-to-date data with your ERP. This guide will provide the steps necessary to enable change tracking for the specific tables that Recurrency needs.
💡 Change Tracking Performance: we very frequently hear distributors are concerned about how change tracking will impact their database performance. Change tracking is a fully supported feature for any MSSQL server and we have not seen it negatively impact the performance of a customer’s ERP. Enabling this does require issuing some SQL commands in your database, and will create a small increase in your overall database size.
The queries in this document default to holding only three days of change tracking information. Data specifically related to change tracking beyond that period is automatically removed from the system. This cleanup process helps prevent the accumulation of unnecessary historical data, resulting in better database performance.
We provide the queries for each type of ERP in this document.
To enable change tracking for your ERP, you will:
Check if change tracking is already enabled
Find the corresponding queries for your specific ERP
When you originally configured the integration between Recurrency and your ERP, you created a dedicated database user for Recurrency to use (normally called recurrencyuser). The last step is to grant this user access to the change tracking dataset.
Check if Change Tracking is Already Enabled
Some distributors have already enabled change tracking for some tables in their MSSQL database. To check if your system is already using change tracking and for which tables, you can execute the following SQL command.
-- Find out if the CT is enabled already
-- by listing all the CT-enabled tables
SELECT OBJECT_NAME(object_id) AS [TABLE_NAME] FROM sys.change_tracking_tables;
This will output a list of table names that already have change tracking enabled. If this query returns nothing, then your system is not using change tracking today.
Epicor Prophet 21
The following SQL query will enable change tracking for the common tables that Recurrency frequently reads data from. Make sure to replace {database_name} with the name of your production database.
-- P21 Script
ALTER DATABASE {database_name} SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);
-- Enable CT for a specific set of tables for our sync
ALTER TABLE invoice_hdr_salesrep ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_hdr_salesrep ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE inv_period_usage ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE inventory_supplier_x_loc ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE item_lead_time ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE invoice_line ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE inv_loc ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_line ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE invoice_hdr ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_pick_ticket_detail ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_hdr ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_pick_ticket ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE ship_to_salesrep ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
SAP Business One
-- SAPB1 Script
ALTER DATABASE {database_name} SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE inv1 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE rdr1 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oinv ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE por1 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oitm ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oitw ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE ordr ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE crd1 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE ocrd ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE itm2 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE qut1 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
Grant Recurrency User Access to Change Tracking Data
The final step is to grant the dedicated Recurrency User access to the change tracking data. This user is most commonly named recurrencyuser.
💡 Don’t remember the name of your Recurrency user? Reach out to [email protected] and we can provide the DB username to you.
-- allow recurrency DB user to access CT dataGRANT VIEW CHANGE TRACKING ON SCHEMA::[dbo] TO recurrencyuser;
Frequently Asked Questions
Will change tracking negatively impact my ERP’s performance?
We performed extensive testing with multiple ERP configurations and types and have found no negative impact to the day to day performance of an ERP after enabling change tracking.
To ensure optimal performance and minimize any potential disruptions, Recurrency has implemented comprehensive monitoring and reporting mechanisms. These tools allow us to closely track the performance of the querying process. In the event that we detect any anomalies or suspect issues, we proactively take action.
Change tracking is a built-in feature in Microsoft SQL Server that tracks modifications made to a table's data. It's designed to operate efficiently by employing the following principles:
Incremental Tracking: Change tracking typically works by maintaining a version number for each row in the tracked table. When a change occurs, only the version number is updated, which is a lightweight operation compared to logging the entire changed data.
Asynchronous Nature: Change tracking operates asynchronously from regular transactions. This means that changes are tracked separately from the main data manipulation operations, reducing the potential impact on performance.
Low Overhead Queries: The queries used to retrieve changes from the change tracking system are designed to be efficient and have a relatively low impact on performance. These queries are optimized to retrieve only the necessary information about changed rows.
Granular Control: Microsoft provides control over how frequently change tracking information is cleaned up. This means that administrators can adjust the balance between retaining change information and managing performance impact.
Designed for Synchronization: Change tracking is often used in synchronization scenarios, where efficient tracking of changes is crucial for maintaining data consistency across multiple databases or systems. This further emphasizes the need for minimal performance impact.
For more details, you can review Microsoft’s documentation at: About Change Tracking - SQL Server | Microsoft Learn
What is the retention window that Recurrency sets for change tracking in our system?
For the queries in this document, the retention window is three days. With a 3-day retention window, change tracking data beyond that period is automatically removed from the system. This cleanup process helps prevent the accumulation of unnecessary historical data which mitigates potential performance impacts.
Can I turn off change tracking if something goes wrong?
In the unlikely event that change tracking causes performance issues, you can disable it using the following query:
-- Turn CT off for a db if something goes wrong
ALTER DATABASE {database_name} SET CHANGE_TRACKING = OFF;
You can also disable change tracking for specific tables
ALTER TABLE YourSchemaName.YourTableName
DISABLE CHANGE_TRACKING;
Can I query the change tracking tables myself to see what data is in them?
Yes! Here's an example for Prophet21 which goes and pulls data out of the change tracking table for oe_hdr. This query shows the rows in the change tracking table, some additional system information about when the transaction occurred, and how to join that with data from other P21 tables. You can change this behavior by modifying the table name and fields you try and pull.
SELECT TOP 1000
tc.commit_time, -- transaction date that modified this record
CT.order_no, -- the primary key, which for oe_hdr is order_no
CT.SYS_CHANGE_OPERATION, -- the operation ([I]nsert, [U]pdate, [D]elete, etc)
oe_hdr.date_last_modified, -- additional data from the oe_hdr table that we query directly via a JOIN
oe_hdr.customer_id
FROM
CHANGETABLE(CHANGES oe_hdr, -1) AS CT
JOIN SYS.dm_tran_commit_table tc ON CT.sys_change_version = tc.commit_ts
JOIN oe_hdr ON oe_hdr.order_no = CT.order_no
WHERE CT.SYS_CHANGE_OPERATION <> 'I'
ORDER BY oe_hdr.date_last_modified ASC;
If you have any questions, please reach out to your dedicated Account Executive, [email protected], or [email protected].