Replicating Multiple Companies
Many Business Central tenants contain more than one company, and a reporting database usually needs data from all of them. This page describes the supported pattern for extracting the same tables from several companies of one tenant into a single SQL Server destination, and the job structure that hougaard.com support has confirmed works well in practice.
The Scenario
- One Business Central tenant and one environment.
- Several companies (for example, company 1 through company 9).
- Company-specific tables whose content differs per company: Location, Dimension Value, Production Order, ledger entries, documents, master data.
- Central or shared tables whose content is identical in every company: setup tables such as Payment Terms or Currencies.
- Destination: one SQL Server database dedicated to the Business Central replica, loaded nightly or on a schedule.
Service Tasks are not the right tool for this volume; see Service Tasks vs. Scripted Runs. The pattern below uses BCCL command lines in scheduled scripts.
The Supported Pattern: One Command per Company
Run one BCCL command per company, with all parameters supplied on that single command line, and without --remember.
What Varies per Company
| Parameter | Why it changes |
|---|---|
-w |
The full web service URL contains the company name segment. Changing that segment is what selects which company's data is read. |
-o |
Use a company-specific SQL target table, such as [Company1$Location] through [Company9$Location], so data from different companies is never mixed. |
What Stays the Same
| Parameter | Note |
|---|---|
-t getdata |
Same task for every company |
-s table=<no> |
Same table number, for example table=14 for Location |
-f sql |
Same output format |
-m <mapping> |
The same mapping file can be reused when the table structure is identical in all companies |
| Authentication | The same --auth S2S --clientid --clientsecret --tenantid values, repeated on every command line |
Example: First and Last Company
BCCL --auth S2S --clientid <client-id> --clientsecret "<client-secret>" --tenantid <tenant-id> -w "https://api.businesscentral.dynamics.com/v2.0/<tenant-id>/<environment>/WS/<company-1>/Codeunit/bccl" -t getdata -s table=14 -f sql -o "[Company1$Location]" -m mapping\Location.mapping.json --sqlconnect "Server=<server>;Database=<database>;User ID=<user>;Password=<password>"
BCCL --auth S2S --clientid <client-id> --clientsecret "<client-secret>" --tenantid <tenant-id> -w "https://api.businesscentral.dynamics.com/v2.0/<tenant-id>/<environment>/WS/<company-9>/Codeunit/bccl" -t getdata -s table=14 -f sql -o "[Company9$Location]" -m mapping\Location.mapping.json --sqlconnect "Server=<server>;Database=<database>;User ID=<user>;Password=<password>"Each command is completely self-contained. Because --remember is not used, the authentication parameters must appear on every line. Each command must be on one unbroken line in a batch file; Windows executes each line separately.
The company name in the URL must be URL-encoded (a space becomes %20, a comma becomes %2C). In a .cmd file, write %%20 and %%2C because % is an escape character in batch files.
Why Not --remember
--remember stores connection information inside the prompt session. That storage mechanism makes it unsuitable for, and actively works against, running parallel or concurrent BCCL sessions: remembered values from one session can overwrite those used by another, so the wrong company or connection may be used.
The rule for multi-company work, and for any multiple or parallel BCCL invocations, is therefore:
Do not use
--remember. Put all parameters, including the web service URL (-w) and authentication, on each individual BCCL command line.
Do not attempt to remember only the credentials and override the URL with -w on each call; that is not a supported pattern.
Recommended Job Structure
One Batch File per Company
Create one batch file per company rather than a single monolithic script. This gives you:
- Isolated troubleshooting. A failure in one company can be re-run on its own without touching the others.
- Independent scheduling. Company jobs can be staggered instead of all starting at the same moment.
- Cleaner logging. Each company produces its own log output, so it is obvious where a failure occurred.
Keep the company-specific parameters (the company segment of the URL, the list of tables, the target table prefix) inside each company's own batch file so they can be adjusted per company without editing the others.
Optional Master Batch File
A single "master" batch file can call the per-company batch files in sequence:
@echo off
call C:\BCCL\Scripts\Company1.cmd >> C:\BCCL\Logs\Company1.log 2>&1
call C:\BCCL\Scripts\Company2.cmd >> C:\BCCL\Logs\Company2.log 2>&1
call C:\BCCL\Scripts\Company3.cmd >> C:\BCCL\Logs\Company3.log 2>&1
call C:\BCCL\Scripts\Company4.cmd >> C:\BCCL\Logs\Company4.log 2>&1The trade-off: the scheduler has one entry point, while the modular per-company pieces are kept for maintenance and individual re-runs.
Staggering
Stagger the start times of the per-company jobs to avoid contention on the server. If you use a master batch file, the companies run one after another by nature; if you schedule the per-company files individually, give each its own start time.
Failure Handling
Capture the console output and the exit code of every run so that a failure raises an alert instead of failing silently:
BCCL ... -t getdata -s table=14 ... >> C:\BCCL\Logs\Company1.log 2>&1
if %ERRORLEVEL% NEQ 0 (
echo %date% %time% Company1 table 14 FAILED with %ERRORLEVEL% >> C:\BCCL\Logs\alerts.log
)BCCL returns 0 on success and a non-zero code on failure; see the Exit Codes section in Command Reference.
Central vs. Company-Specific Tables
Distinguish the two kinds of tables and handle them differently:
- Central / shared tables (setup tables such as Payment Terms or Currencies, where the data is identical in every company): export them once, from one company, rather than once per company, to avoid loading identical data multiple times.
- Company-specific tables: export them from each company into that company's own destination tables.
If the company-specific tables depend on central tables being present in SQL first (for example, foreign keys or views), place the central-table commands before the company commands in the script. Commands in a .cmd file run sequentially in the order written.
Destination Table Layout
Give each company its own destination table(s) rather than merging all companies into one shared table per entity. For example, [Company1$Item] and [Company2$Item] instead of a single [Item] table with a company column.
Reasons:
- Master data stays separated per company.
- You avoid duplicate primary key values colliding in a combined table. The same Item No. or Customer No. commonly exists in more than one company with different content.
This layout has been reported as the easier one to maintain in practice and was confirmed as a good approach by hougaard.com support.
Load Strategy per Table
Choose how each table is refreshed. The two patterns below can be mixed freely within the same nightly run; the choice is made per table.
| Table type | Strategy | How |
|---|---|---|
| Small or low-volume tables (setup tables, small master data) | Truncate and full reload on every run | --sqlprecommand "TRUNCATE TABLE [Company1$Payment Terms]" followed by a plain getdata |
| Large transactional tables (ledger entries, posted documents) | Filtered incremental load with a rolling date window | Add a view filter on SystemModifiedAt (field 2000000003), for example the past 7 days: "view=WHERE(Field2000000003=1({today(-7D)}..))" |
The reference configuration uses a 7-day window for ledger tables. Narrow the window if that is still too much data per run, but keep it at least as long as the interval between runs so no changes are missed. For deletion tracking in incremental tables, combine with the CHANGELOG task as described in Change Log Configuration.
Example incremental line for a ledger table:
BCCL --auth S2S --clientid <client-id> --clientsecret "<client-secret>" --tenantid <tenant-id> -w "https://api.businesscentral.dynamics.com/v2.0/<tenant-id>/<environment>/WS/<company-1>/Codeunit/bccl" -t getdata -s table=17 "view=WHERE(Field2000000003=1({today(-7D)}..))" -f sql -o "[Company1$G_L Entry]" -m mapping\GLEntry.mapping.json --sqlconnect "Server=<server>;Database=<database>;User ID=<user>;Password=<password>"See Filtering Data for the filter syntax.
Scheduling: Windows Task Scheduler vs. SQL Server Agent
Both options work; the choice is a matter of environment and operations preference.
| Option | When it fits | Points to verify |
|---|---|---|
| SQL Server Agent | Often the better choice 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. | Everything stays in one place, so no separate scheduler configuration is needed. |
| Windows Task Scheduler | Fine and simpler if you prefer to keep scheduling at the operating-system level. | The account the task runs under must have the required permissions (file system, SQL Server, network). Log output must be explicitly redirected to a file, as shown above; it is not retained automatically. |
For step-by-step Task Scheduler configuration, see Scheduling Replication.
Worked Reference Example
The following anonymised configuration was reviewed and confirmed as a good plan by hougaard.com support. Use it as a starting point.
| Element | Configuration |
|---|---|
| Destination | A dedicated SQL Server database acting as the Business Central repository, for example BCReplica |
| Scope | 4 companies, roughly 12 tables each |
| Scripts | One batch file per company (Company1.cmd … Company4.cmd); an optional RunAll.cmd master file calling all four in sequence |
| Schedule | Nightly, with start times staggered per company |
| Destination tables | Per company: [Company1$Item], [Company2$Item], and so on |
| Small tables | Truncate the destination table, then full insert on every run |
| Ledger / transactional tables | Filtered to the last 7 days with a SystemModifiedAt view filter |
| Authentication | S2S, all parameters on every command line, no --remember |
| Monitoring | Console output redirected to a per-company log file; exit codes checked and failures written to an alert log |
Generate the CREATE TABLE scripts, mapping files, FirstRunBCCL.cmd (initial full load), and RunBCCL.cmd (incremental sync) with the SQL Bundle Builder from one company, then adapt the generated command lines per company by changing the company segment of -w and the -o table names.
Frequently Asked Question
Can I run BCCL for several companies at the same time? Will --remember overwrite my data or connection?
Yes, running per-company extractions is supported, but --remember must not be used for this. It is stored in the prompt session and will conflict across simultaneous sessions. Use one self-contained BCCL command per company with all parameters specified, a company-specific -w URL, and a company-specific -o output table.
Tips and Best Practices
- Build and test the script for one company end to end before copying it to the others.
- Keep one mapping file per table and share it across companies as long as the table structure is identical.
- Use S2S authentication for scheduled runs; it can re-authenticate during long transfers. See Running Long or Unattended Transfers Reliably.
- Run the company scripts from a BCCL folder that does not contain
bccl_service.json, otherwise BCCL starts in service mode instead of executing the command. - Review the logs after the first few nightly runs and adjust the incremental window and staggering as needed.