Change Log Configuration
The BCCL CHANGELOG task enables incremental SQL replication by reading the Business Central Change Log. To use this feature, you must configure the Change Log in Business Central to track the tables and operations you want to replicate. This page covers the complete setup process, with particular emphasis on configuring deletion tracking.
What is the Change Log?
The Business Central Change Log (table 405 - Change Log Entry) is a built-in auditing feature that records when records are inserted, modified, or deleted. Each entry includes:
- Table No.: Which table was affected
- Primary Key: The record's primary key values
- Field No.: Which field changed (for modifications)
- Old Value / New Value: The before and after values
- Date and Time: When the change occurred
- User ID: Who made the change
BCCL reads these entries and translates them into SQL INSERT, UPDATE, and DELETE operations on your SQL Server tables.
Why Change Log Matters for Replication
The Change Log is needed for deletions only. The GetData task handles inserts and updates without the Change Log: with a SystemModifiedAt view filter it picks up newly inserted and modified records on ledger tables, document tables such as sales orders, and master data tables such as customers and vendors, and no Change Log setup is required for that. See "Does GetData Need the Change Log?" in SQL Replication Overview.
What GetData cannot see is a record that has disappeared. Without the Change Log:
- Deletions in BC are not detected
- SQL Server retains records that no longer exist in BC
With Change Log deletion tracking enabled and the CHANGELOG task scheduled:
- Deletions are replicated as SQL DELETE statements
- SQL Server stays in sync with BC
If you want to keep Change Log volume low, enable Log Deletion only; logging insertions and modifications is not needed for replication.
Enabling the Change Log
Step 1: Open Change Log Setup
- In Business Central, search for Change Log Setup (Alt-Q)
- Open the Change Log Setup page
Step 2: Enable Change Log Logging
- Set Change Log Activated to Yes
- This enables the Change Log framework in your Business Central environment
Step 3: Configure Table Tracking
- From the Change Log Setup page, select Tables (or navigate to Change Log Setup (Table))
- For each table you want to replicate, add an entry:
| Field | Setting | Description |
|---|---|---|
| Table No. | e.g., 18 | The table to track |
| Table Caption | e.g., Customer | Automatically filled |
| Log Insertion | (not required) | Inserts are replicated by GetData without the Change Log; enable only if you want them in the audit trail |
| Log Modification | (not required) | Updates are replicated by GetData without the Change Log; enable only if you want them in the audit trail |
| Log Deletion | All Fields | Critical: track deletions |
Step 4: Enable Deletion Tracking
This is the most important step for SQL replication. Without deletion tracking, records deleted in Business Central will remain in your SQL Server tables, causing data inconsistency.
For each replicated table, ensure Log Deletion is set to All Fields (or at minimum, the specific fields included in your mapping).
Step 5: Verify Configuration
After configuration, test by:
- Making a change to a record in a tracked table
- Searching for Change Log Entries (Alt-Q)
- Verifying that the change appears in the log
Configuring Specific Tables
Recommended Settings for Common Tables
| Table | No. | Log Insertion | Log Modification | Log Deletion |
|---|---|---|---|---|
| Customer | 18 | Not required | Not required | All Fields |
| Vendor | 23 | Not required | Not required | All Fields |
| Item | 27 | Not required | Not required | All Fields |
| G/L Entry | 17 | Not required | Not required | Some Fields |
| Cust. Ledger Entry | 21 | Not required | Not required | Some Fields |
| Sales Header | 36 | Not required | Not required | All Fields |
| Sales Line | 37 | Not required | Not required | All Fields |
Note: For ledger entries that are typically not deleted, you may choose "Some Fields" for deletion tracking to reduce Change Log volume. Insertions and modifications on all of these tables are replicated by the GetData task without any Change Log entries.
Tracking Specific Fields
Instead of "All Fields", you can choose "Some Fields" and then specify exactly which fields to track. This reduces Change Log volume.
For deletion tracking, "All Fields" is generally recommended; at minimum the primary key fields included in your mapping must be logged so that BCCL can identify the SQL row to delete.
Using the CHANGELOG Task
Basic Command
BCCL -t changelog -s table=18 -m customer.json -o Customer --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes"Parameters
| Parameter | Description |
|---|---|
-t changelog |
Specifies the CHANGELOG task |
-s table=N |
Business Central table number |
-m |
Mapping file for field correspondence |
-o |
Target SQL table name |
--sqlconnect |
SQL Server connection string |
Filtering by Date
Use dynamic date expressions to limit the Change Log window:
# Changes from the last 24 hours
BCCL -t changelog -s table=18 "view=where(DateTime=filter({today(-1)}..{today}))" -m customer.json -o Customer --sqlconnect "..."
# Changes from the last 30 minutes
BCCL -t changelog -s table=18 "view=where(DateTime=filter({minutes(-30M)}..))" -m customer.json -o Customer --sqlconnect "..."Dynamic Date Expressions
| Expression | Meaning |
|---|---|
{today} |
Current date |
{today(-1)} |
Yesterday |
{today(-7)} |
7 days ago |
{minutes(-30M)} |
30 minutes ago |
{minutes(-1H)} |
1 hour ago |
{minutes(-1H30M)} |
1 hour 30 minutes ago |
{minutes(-1H2M13S)} |
1 hour, 2 minutes, 13 seconds ago |
The {minutes()} parameter is a time expression using H (hours), M (minutes), and S (seconds) — not a plain minute count. Units can be combined and signed.
How CHANGELOG Processes Changes
When the CHANGELOG task runs, it:
- Reads Change Log entries for the specified table within the date filter
- For each Insertion entry: executes an SQL INSERT
- For each Modification entry: executes an SQL UPDATE (matched by primary key)
- For each Deletion entry: executes an SQL DELETE (matched by primary key)
Deletion Processing
For deletions to be replicated to SQL:
- Change Log must have deletion tracking enabled for the table
- The BCCL mapping file must include the primary key fields
- The
AddDeletionsFromChangeLogparameter must be enabled (when using SQL Bundle Builder)
The DELETE operation uses the primary key values stored in the Change Log entry to identify and remove the corresponding SQL Server record.
Service Task Integration
You can configure CHANGELOG operations as Service Tasks for automated execution:
- Create a Service Task with Task type
CHANGELOG - Set the table number, mapping, SQL connection
- Set Classification to Automation
- Enable ChangeLogFilterSinceLast for incremental processing
- Schedule via Job Queue
See Automations and Scheduling for details.
Change Log Maintenance
The Change Log can grow very large over time. Without maintenance:
- The Change Log table consumes significant database space
- CHANGELOG task queries become slower
- BC performance may be affected
Best Practices for Maintenance
- Retention periods: Use date filters in your CHANGELOG commands to limit the processing window
- Delete old entries: Periodically delete Change Log entries older than your retention period
- Business Central built-in cleanup: Use the Delete Change Log Entries batch job
- Monitor table size: Regularly check the Change Log Entry table size
- Archive if needed: Export old entries before deleting if you need them for auditing
Recommended Cleanup Schedule
| Sync Frequency | Keep Change Log Entries For |
|---|---|
| Real-time (1-5 min) | 7 days |
| Hourly | 14 days |
| Daily | 30 days |
Frequently Asked Questions
Do I have to enable the Change Log on the tables I replicate, to capture inserts and updates?
No. The GetData task handles inserts and updates without the Change Log. Change Log configuration is therefore not required on ledger tables, sales order tables, or master data tables (customers, vendors, and so on) for insert/update replication. Customers who want to keep Change Log volume low do not need to enable Change Log entries for insertions and modifications on replicated tables; only Log Deletion is needed, and only if deletions must be replicated. See "Does GetData Need the Change Log?" in SQL Replication Overview.
Troubleshooting
| Issue | Solution |
|---|---|
| Deletions not appearing in SQL | Enable Log Deletion for the table in Change Log Setup |
| Too many Change Log entries | Disable Log Insertion/Log Modification on replicated tables (not needed for replication); reduce tracked tables/fields; schedule cleanup |
| CHANGELOG task slow | Use narrower date filters; enable index on Change Log |
| Missing changes | Ensure retention period exceeds sync interval |
| "Change Log not enabled" | Activate Change Log in Change Log Setup page |
Every run reports 0 inserted, N updated |
That is GETDATA without a modification filter, not a Change Log problem. Add a SystemModifiedAt view filter; see Filtering Data |