Several databases with one agent

One agent installation handles every database on a SQL Server instance that the agent login can reach. This scenario shows an ERP database on Change Tracking, a legacy database with no primary keys on full extracts, a warehouse staging database on CDC, and how to lay the output out so the three never collide.

When to use it

• Several databases live on one instance (or one Availability Group listener) and you want one host, one schedule, one state database and one set of alerts.
• The databases differ in what they support: some have primary keys and Change Tracking, some do not.
• Total object count is large (thousands of tables), so refresh and memory settings matter.

When two instances are involved, install two agents (one per instance, each with its own target_schema_prefix and state database) rather than pointing one agent at a linked server.

Configuration

agent_version: 1.9.4
state_db_path: C:/dlh/state/dlh_agent_state.duckdb

connection_information:
  connection_type: sql_server
  server_name_or_ip: sql01.corp.local
  server_port: 1433
  database_names: [ErpDb, LegacyDb, StagingDb]

data_retrieval:
  historical_load: false
  output_format: delta
  direct_cloud_write: true
  storage_base_path: /data/delta_iceberg_tables
  output_path: C:/dlh/output
  table_name_structure: db_separated
  table_name_casing: lowercase
  parquet_compression: zstd
  on_schema_drift_action: migrate_target
  on_schema_drift_action_handling: fail_once
  fetch_batch_size: 50000
  max_memory_percent: 80
  databases:
    # ERP: primary keys everywhere, Change Tracking enabled on the database
    - name: ErpDb
      sync_mode: ct
      schema_refresh_mode: 1D
      tables:
        - name: dbo.*
          all_columns: true
          no_pk_strategy: full
        - name: dbo.EventLog
          all_columns: true
          write_strategy: append
      views:
        - name: rpt.*
          all_columns: true

    # Legacy: mostly heaps without primary keys, no CT available
    - name: LegacyDb
      sync_mode: full
      schema_refresh_mode: 1W
      tables:
        - name: dbo.Customers
          all_columns: true
          write_strategy: replace
        - name: dbo.Products
          all_columns: false
          columns: [ProductId, Name, Category, UnitPrice, Discontinued]
          write_strategy: replace
        - name: dbo.SalesHistory   # has a primary key but CT is not enabled on LegacyDb
          all_columns: true
          write_strategy: delete+insert
          incremental_key: SaleDate

    # Staging: CDC is already enabled for other consumers
    - name: StagingDb
      sync_mode: cdc
      schema_refresh_mode: table_changed
      tables:
        - name: stg.*
          all_columns: true
          no_pk_strategy: full

dlh_ref:
  org_guid: '<YOUR_ORG_GUID>'
  prj_guid: '<YOUR_PROJECT_GUID>'
  connection_guid: '<YOUR_CONNECTION_GUID>'
  target_schema_prefix: Site003
  api_key: '<YOUR_DLH_API_KEY>'

dlh_notifications:
  enabled: true
  platform_run_report: true
  webhooks:
    - url_env_var: DLH_OPS_WEBHOOK_URL
      format: teams

Naming structures compared

table_name_structure decides how database, schema and table are arranged in the output. For Delta tables under storage_base_path:

Pick one before the first run and keep it. Changing it later makes the agent create new tables at the new paths while the old ones stay behind; you would then reload and repoint the downstream engine. flat is fine for a single database; db_separated or nested read better once several databases share a prefix. CSV and Parquet output ignores this setting and always writes <db>.<schema>.<table> file names in dated folders.

Run behaviour

The run summary groups tables by database, and each database's tables are processed in turn:

  ErpDb      dbo.Customer            table   ct         42  OK
  ErpDb      dbo.EventLog            table   ct       1210  OK
  ErpDb      rpt.OpenOrders          view    full     3308  OK
  LegacyDb   dbo.Customers           table   full    18455  OK
  LegacyDb   dbo.SalesHistory        table   full     4021  OK
  StagingDb  stg.Shipment            table   cdc        17  OK
  StagingDb  stg.ShipmentLine        table   cdc         0  OK        No CT/CDC changes - skipped write

Views are always extracted in full (they have no CT or CDC) and are typically written with replace. The state database records one sync state per database, schema and table, so a failure in one database never blocks the others, and --diagnose reports the CT and CDC status of each database separately.

Sizing for thousands of tables

• Wildcards keep the file short: dbo.* on a 3,000-table schema is one entry. Add specific entries only for exceptions.
• Schema refresh is the main per-run cost at scale. Use 1D, 1W or table_changed rather than always in production.
• Initial load of thousands of tables can take hours. Run it interactively or from a one-off scheduled task outside business hours, then enable the five-minute schedule once the first run finishes. Keep the scheduled task's "Do not start a new instance" setting so a long run is never overlapped by the next trigger; see Operations.
• Memory: watch the STATE STORE block and the memory pause messages in the log during the first week. If pauses recur, lower fetch_batch_size further. See Performance and retention.
• Alerts at volume: a single failing table out of 3,000 still produces an error notification every run. Use error_severity: critical on a noisy channel and error on the channel your operators watch.

Variations

• Different targets per database (for example Iceberg for staging and Delta for ERP) are not supported in one configuration; output_format is global. Install a second agent with its own target_schema_prefix.
• Same database name on two instances: give each agent a distinct target_schema_prefix; storage paths always include it, so nothing collides.
• Availability Groups: point server_name_or_ip at the listener. Change Tracking and CDC state fails over with the database, and the agent's own state is in its DuckDB file on the host.