SQL Replication Overview

BCCL can replicate data from Business Central to Microsoft SQL Server, enabling you to use BC data for reporting, analytics, data warehousing, Power BI dashboards, and integration with other systems. BCCL supports both full load and incremental replication modes.

Why Replicate to SQL Server?

Business Central is optimized for transactional processing, not reporting. By replicating data to SQL Server, you gain:

Fast reporting: Run complex queries without impacting BC performance
Power BI integration: Connect Power BI directly to SQL Server for real-time dashboards
Data warehousing: Combine BC data with data from other systems
Custom analytics: Use SQL Server tools for advanced analysis
Cross-system integration: Share BC data with applications that read from SQL

Replication Modes

Full Load

A full load exports the entire contents of a Business Central table to SQL Server. This is used for:

Initial setup (first-time replication)
Periodic complete refreshes
Tables where Change Log is not enabled

Command:

BCCL -t getdata -s table=18 -f sql -o Customer --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes" --sqlprecommand "TRUNCATE TABLE Customer"

The --sqlprecommand "TRUNCATE TABLE Customer" clears the existing data before loading, ensuring a clean copy.

Incremental Replication

Incremental replication transfers only what changed since the last sync. It is much faster than a full load and is suitable for frequent synchronization. BCCL uses two mechanisms for it:

Inserts and updates are picked up by the GetData task with a filter on the SystemModifiedAt system field. No Change Log is involved.
Deletions are picked up by the CHANGELOG task, which reads the Business Central Change Log.

Commands (following the pattern the SQL Bundle Builder generates):

BCCL -t getdata -s table=18 view=WHERE(Field2000000003=1({today(-1D)}..)) -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"

Incremental replication requires:

A mapping file for field correspondence
Change Log deletion tracking enabled in Business Central for the target tables only if you need to replicate deleted records

Does GetData Need the Change Log?

No. The GetData task handles inserts and updates without the Change Log. No Change Log setup is required for the replication to pick up newly inserted records or updated records. This applies to tables in general and is not specific to a table category: for inserts and updates on ledger tables, on document tables such as sales orders, and on master data tables such as customers and vendors, Change Log activation is not needed.

The Change Log is used only for deletion tracking: the GetData task cannot see that a record has disappeared, so deletions are detected through Change Log entries and applied by the CHANGELOG task. If you want to keep Change Log volume low, you do not need to enable Change Log entries for insertions or modifications on replicated tables; enable Log Deletion only. See Change Log Configuration for the deletion setup.

To make GetData incremental (so that unchanged rows are not re-written on every run), add a SystemModifiedAt view filter as shown above; see "Filtering on System Fields (Incremental Exports)" in Filtering Data.

Architecture

The SQL replication flow works as follows:

Business Central
       │
       │ SOAP Web Service
       ▼
   BCCL.EXE
       │
       │ SQL Connection
       ▼
  SQL Server

BCCL connects to Business Central via the web service endpoint
For GETDATA: reads table data and writes it to SQL Server
For CHANGELOG: reads Change Log entries and applies INSERT/UPDATE/DELETE operations
Background workers handle parallel SQL operations (up to 10 concurrent)
Upsert logic: new records are inserted, existing records are updated (matched by primary key)

SQL Connection String

BCCL connects to SQL Server using a standard ADO.NET connection string passed via --sqlconnect:

Windows Authentication:

Server=localhost;Database=BCData;Trusted_Connection=yes

SQL Server Authentication:

Server=dbserver.example.com;Database=BCData;User Id=myuser;Password=mypassword

Named Instance:

Server=dbserver\SQLEXPRESS;Database=BCData;Trusted_Connection=yes

Key Parameters for SQL Replication

Parameter	Description	Example
`-f sql`	SQL output format	`-f sql`
`-o`	Target SQL table name	`-o Customer`
`--sqlconnect`	SQL Server connection string	`--sqlconnect "Server=..."`
`--sqlprecommand`	SQL executed before import	`--sqlprecommand "TRUNCATE TABLE Customer"`
`--sqlzerodate`	Date for empty BC dates	`--sqlzerodate "1900-01-01"`
`-m`	Mapping file	`-m customer.json`

Zero Date Handling

Business Central uses 0D (zero date) for empty date fields. SQL Server does not accept zero dates, so BCCL replaces them with a configurable date value:

Default: 1900-01-01
Override: --sqlzerodate "1753-01-01" (SQL Server minimum date)

SQL Type Mapping

BCCL automatically maps Business Central field types to appropriate SQL Server types:

BC Field Type	SQL Type
Text, Code	nvarchar(length)
Date	date
DateTime	datetime
Decimal	decimal(38,20)
Integer, Option	int
BigInteger, Duration	bigint
Boolean	bit
GUID	uniqueidentifier
Blob, Media, MediaSet	image
RecordID	varbinary(448)

BigInteger → bigint is mandatory. A BigInteger or Duration field mapped to an int column produces System.OverflowException: Value was either too large or too small for an Int32. at export time. With BCCL 2.x clients the same exception can occur even when the column is correctly bigint, because those clients could handle such fields internally as Int32; this has been observed on tables extended by the Shopify Connector (for example Sales Header, table 36). Use the BCCL 3.0 client, or exclude those fields from the mapping file. Generate the CREATE TABLE scripts with the SQL Bundle Builder rather than hand-writing them so the type mapping is correct. See the overflow entry in Common Issues.

The SQL Bundle Builder

For production SQL replication setups, BCCL provides the SQL Bundle Builder - a report in Business Central that generates a complete replication package including:

SQL CREATE TABLE scripts
BCCL run commands for each table
Mapping files
First-run and incremental-run scripts

See SQL Bundle Builder for details.

Getting Started with SQL Replication

Quick Start (Single Table)

Create the SQL table: Generate a mapping file and use its SQL CREATE TABLE output, or create the table manually
Full load: BCCL -t getdata -s table=18 -f sql -o Customer -m customer.json --sqlconnect "..."
Incremental inserts and updates: BCCL -t getdata -s table=18 view=WHERE(Field2000000003=1({today(-1D)}..)) -f sql -o Customer -m customer.json --sqlconnect "..." (no Change Log needed)
Deletions (optional): enable Log Deletion in the BC Change Log for the table, then run BCCL -t changelog -s table=18 -m customer.json -o Customer --sqlconnect "..."
Schedule using Windows Task Scheduler

Production Setup (Multiple Tables)

Use the SQL Bundle Builder to generate a complete package
Run FirstRunBCCL.cmd for initial full load
Schedule RunBCCL.cmd for ongoing incremental sync
Monitor execution and logs

For several companies, see Replicating Multiple Companies.

Tips and Best Practices

Always start with a full load before enabling incremental replication
Enable Change Log deletion tracking for replicated tables where records may be deleted; Change Log is not required for inserts and updates
Use the SQL Bundle Builder for multi-table setups, and run its scripts from Windows Task Scheduler rather than building one Service Task per table (see Service Tasks vs. Scripted Runs)
For replication runs (full load or incremental) against cloud Business Central, authenticate with S2S and supply all parameters on one command line, so BCCL can re-authenticate during long runs. OAuth access stops working after a period defined by the tenant administrators. See Running Long or Unattended Transfers Reliably and Service-to-Service Authentication
Monitor SQL Server table sizes and index performance
Schedule incremental syncs based on business requirements (not more frequent than needed)
Test replication in a sandbox environment before production; one BCCL license covers both sandbox and production environments of the same tenant