Scheduling Replication
Once you have set up SQL replication with BCCL, you need to schedule it to run at regular intervals. BCCL supports two scheduling methods: Windows Task Scheduler for command-line mode, and the Business Central Job Queue for service mode.
Choosing a Scheduling Method
| Method | Best For | Requires |
|---|---|---|
Windows Task Scheduler (or SQL Server Agent) with a .cmd/.bat script |
Recommended for ongoing SQL replication, multi-table and multi-company replication | Windows Server with BCCL.EXE (no service needed) |
| BC Job Queue with Service Task Automations | One-off or individually managed operations where everything must be managed inside BC | BCCL running as a Windows service |
Windows Task Scheduler with a .cmd/.bat script is the recommended method for multi-table and multi-company replication. The scripts generated by the SQL Bundle Builder (FirstRunBCCL.cmd, RunBCCL.cmd) are built for exactly this. BC Job Queue with Service Tasks is intended for one-off or individually managed operations: one Service Task covers one table, and one agent executes only one task at a time, so high table counts or short intervals require multiple agents. See Service Tasks vs. Scripted Runs.
Windows Task Scheduler
Best when:
- You replicate many tables and/or many companies
- You want direct control over the schedule
- You do not need Business Central integration for monitoring
Two things to verify: the account the scheduled task runs under must have the required permissions (file system, SQL Server, network), and log output must be explicitly redirected to a file, because Task Scheduler does not retain console output.
SQL Server Agent is an equally valid alternative when the destination is SQL Server anyway: everything stays in one place, you get built-in job history and logging, and you can chain job steps with error handling. The choice between Task Scheduler and SQL Server Agent is a matter of environment and operations preference. See Replicating Multiple Companies.
Business Central Job Queue
Best when:
- BCCL runs as a Windows service with agents
- You want to manage and monitor a limited number of tasks from within BC
- You use Service Tasks and Automations for individually managed operations
Do I Need BCCL as a Service?
No. If all replication runs from scheduled scripts, BCCL can run purely in command-line mode; no bccl_service.json is required. Service mode is only needed if Business Central users should dispatch Service Tasks to an agent.
Running Both Models on One Machine
You can run both side by side. Install BCCL.EXE into two separate folders on the same server. Place bccl_service.json only in the folder that should act as the service agent, and run the scheduled scripts from the other folder. The presence of bccl_service.json in the same folder as BCCL.EXE is what switches BCCL into service mode; a command run from a folder that contains it never executes and keeps the prompt occupied (see Service Installation Overview).
Initial vs. Ongoing Replication
Before scheduling incremental sync, you must perform an initial full load.
First Run (Full Load)
The initial full load exports all data from each table without date filters:
BCCL -t getdata -s table=18 -f sql -o Customer --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes" --sqlprecommand "TRUNCATE TABLE Customer"Key points:
- Use
--sqlprecommand "TRUNCATE TABLE ..."to clear existing data - This exports the complete table contents
- Run once per table to establish the baseline
- Best run during off-peak hours for large tables
If you used the SQL Bundle Builder, run the generated FirstRunBCCL.cmd script:
FirstRunBCCL.cmdOngoing (Incremental Sync)
After the initial load, schedule the incremental sync. It consists of two kinds of command per table:
- GETDATA with a
SystemModifiedAtfilter for inserts and modifications (no Change Log needed):
BCCL -t getdata -s table=18 view=WHERE(Field2000000003=1({minutes(-30M)}..)) -f sql -o Customer -m customer.json --sqlconnect "..."- CHANGELOG for deletions (requires Change Log deletion tracking in BC):
BCCL -t changelog -s table=18 -m customer.json -o Customer --sqlconnect "..." "view=WHERE(Field2=1({minutes(-30M)}..))"Both process only changes within the retention window, making them much faster than a full load.
If you used the SQL Bundle Builder, schedule the generated RunBCCL.cmd script, which contains both kinds of line for every selected table.
Incremental Sync from the Command Line (without Service Tasks)
When you drive BCCL from Windows Task Scheduler or .cmd scripts instead of BC Service Tasks, the "only process what changed" mechanism is a SystemModifiedAt view filter on the GETDATA task, not a special parameter:
BCCL -t getdata -s table=<N> view=WHERE(Field2000000003=1({minutes(-2H)}..)) -f sql -o <SqlTableName> -m mapping\<Table>.mapping.jsonField2000000003is theSystemModifiedAtsystem field addressed by its field number;=1(...)is the filter-group form used in BCCL view strings.- Choose the lookback (
{today(-1D)},{minutes(-2H)}, and so on) so that it matches or exceeds the scheduled run frequency. Use the "Retention Period Guidelines" table below as the reference. - Without such a filter, GETDATA transfers the whole table on every run and upserts it, which shows up as
Sql table [...], 0 inserted, N updatedlines. That is expected behaviour, not an error.
The Service Task setting Filter to only entries since last run (ChangeLogFilterSinceLast) is a Service Task setting only. It is not available as a BCCL.EXE command-line parameter; command-line users achieve the equivalent by supplying the SystemModifiedAt view filter themselves. See Automations and Scheduling.
The rule for complete incremental replication is two-pronged: GETDATA with a SystemModifiedAt filter covers inserts and modifications; CHANGELOG covers deletions. See Filtering Data for the filter syntax.
Scheduling with Windows Task Scheduler
Step 1: Create a Script
Create a batch file (e.g., sync.cmd) with your BCCL commands:
@echo off
echo Starting BCCL sync at %date% %time%
REM Customer: inserts/updates via GETDATA, deletions via CHANGELOG
BCCL -t getdata -s table=18 view=WHERE(Field2000000003=1({minutes(-30M)}..)) -f sql -o Customer -m customer.json --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes"
BCCL -t changelog -s table=18 -m customer.json -o Customer --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes"
REM Vendor
BCCL -t getdata -s table=23 view=WHERE(Field2000000003=1({minutes(-30M)}..)) -f sql -o Vendor -m vendor.json --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes"
BCCL -t changelog -s table=23 -m vendor.json -o Vendor --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes"
REM Item
BCCL -t getdata -s table=27 view=WHERE(Field2000000003=1({minutes(-30M)}..)) -f sql -o Item -m item.json --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes"
BCCL -t changelog -s table=27 -m item.json -o Item --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes"
echo Sync completed at %date% %time%Or use the RunBCCL.cmd generated by the SQL Bundle Builder.
For cloud Business Central, put the S2S authentication parameters on each command line (or, if you use --remember, make sure all of them are on one single line) so that BCCL can re-authenticate during long runs, and keep every BCCL command on one unbroken line. See Running Long or Unattended Transfers Reliably. For several companies, use one script per company; see Replicating Multiple Companies.
Step 2: Open Task Scheduler
- Open Task Scheduler (taskschd.msc) on your Windows Server
- Select Create Task (not "Create Basic Task" for more control)
Step 3: Configure the General Tab
| Setting | Value |
|---|---|
| Name | BCCL SQL Replication |
| Description | Incremental sync from BC to SQL Server |
| Run whether user is logged on or not | Checked |
| Run with highest privileges | Checked (if needed for file/SQL access) |
Step 4: Configure the Trigger
| Setting | Value |
|---|---|
| Begin the task | On a schedule |
| Settings | Daily |
| Repeat task every | Your desired interval |
| For a duration of | Indefinitely |
Common interval settings:
| Interval | Setting |
|---|---|
| Every 5 minutes | Repeat every 5 minutes |
| Every 15 minutes | Repeat every 15 minutes |
| Every hour | Repeat every 1 hour |
| Once daily | Do not repeat; set specific start time |
Step 5: Configure the Action
| Setting | Value |
|---|---|
| Action | Start a program |
| Program/script | C:\BCCL\sync.cmd (path to your script) |
| Start in | C:\BCCL&#; (BCCL installation folder) |
Step 6: Configure Conditions and Settings
Recommended settings:
| Setting | Value |
|---|---|
| Stop task if it runs longer than | 1 hour |
| If the task is already running | Do not start a new instance |
| Start only if network connection is available | Checked |
Step 7: Save and Test
- Enter credentials for the user account that will run the task
- Right-click the task and select Run to test
- Check History tab for results
- Verify data in SQL Server
Scheduling with Business Central Job Queue
Step 1: Set Up Service Tasks as Automations
For each table, create a Service Task with:
- Task: CHANGELOG
- Classification: Automation
- ChangeLogFilterSinceLast: Enabled
- Table, mapping, SQL connection: Configured
- Agent: Assigned
See Automations and Scheduling for details.
Step 2: Create a Job Queue Entry
- Search for Job Queue Entries in Business Central (Alt-Q)
- Create a new entry:
| Field | Value |
|---|---|
| Object Type to Run | Codeunit |
| Object ID to Run | 80807 (BCCL Run From Job Queue) |
| Description | SQL Replication Sync |
| Recurring Job | Yes |
- Set the recurrence pattern:
| Field | Value |
|---|---|
| Run on Mondays-Fridays | Checked (adjust as needed) |
| Starting Time | 00:00 (or your start time) |
| Ending Time | 23:59 |
| No. of Minutes between Runs | Your desired interval |
- Set status to Ready
Step 3: Monitor
- Check Job Queue Entries for execution status
- Review Service Task Clone List for individual run results
- Check Task Logs for errors
Recommended Schedules
| Scenario | Interval | Method | Notes |
|---|---|---|---|
| Near real-time | 1-5 minutes | Job Queue | High frequency; monitor performance |
| Regular updates | 15-30 minutes | Either | Good balance of freshness and load |
| Hourly reports | 60 minutes | Either | Suitable for most reporting needs |
| Daily batch | Once daily | Task Scheduler | Low overhead; run during off-hours |
| On-demand | Manual | CLI or Service Task | For ad-hoc needs |
Retention Period Guidelines
The retention period (how far back the CHANGELOG looks) should always be longer than the sync interval to ensure no changes are missed:
| Sync Interval | Minimum Retention | Recommended Retention |
|---|---|---|
| 1 minute | 5 minutes | 30 minutes |
| 5 minutes | 15 minutes | 1 hour |
| 15 minutes | 30 minutes | 2 hours |
| 1 hour | 2 hours | 6 hours |
| Daily | 1 day | 2 days |
The extra buffer accounts for:
- Sync runs that take longer than expected
- Temporary failures that skip a cycle
- Time zone differences or clock drift
Monitoring Scheduled Replication
Service Mode Monitoring
- Service Task Clone List: View all execution instances with status and timestamps
- Task Logs: Detailed execution output including record counts
- Available Agents Factbox: Confirm agents are online
- Job Queue Log Entries: Track Job Queue execution history
Command-Line Mode Monitoring
- Task Scheduler History: View task execution results
- BCCL Log Files: Check console output (redirect to file in your script)
- Windows Event Viewer: Application log, source "BCCL"
- SQL Server: Verify row counts and last-modified timestamps
Adding Logging to Scripts
Add output redirection to your batch scripts for troubleshooting:
@echo off
echo %date% %time% - Starting sync >> C:\BCCL\sync.log
BCCL -t changelog -s table=18 -m customer.json -o Customer --sqlconnect "..." -v >> C:\BCCL\sync.log 2>&1
echo %date% %time% - Sync complete >> C:\BCCL\sync.logHandling Failures
Automatic Recovery
Both scheduling methods handle failures gracefully:
- Task Scheduler: Retries on next scheduled run
- Job Queue: Creates a new Clone on next interval
Because the CHANGELOG task uses a retention window, missed runs are automatically covered by subsequent runs (as long as retention is sufficient).
Manual Recovery
If replication falls significantly behind:
- Run a full load to reset the baseline
- Resume incremental sync from the current time
- Use
--sqlprecommand "TRUNCATE TABLE ..."for the full load
Tips and Best Practices
- Always test your schedule with a few cycles before relying on it for production
- Set retention periods conservatively to avoid missing changes
- Monitor for failures regularly, especially in the first week
- Use verbose mode (
-v) during initial testing, disable for production - Keep Change Log entries cleaned up to maintain performance
- Consider separate schedules for high-volume tables vs. low-volume tables
- Document your replication setup for team members