SQL Server Incremental Batch Sync Between Instances

Move only what changed between two SQL Server instances, and never lose track of where the last run left off. This blueprint reads a watermark from Kestra's KV Store, pulls every row updated since that point from a source table, bulk-inserts the delta into a destination instance using chunked JDBC batch operations, then advances the watermark only after the load commits. A failed run leaves the watermark untouched, so the next execution retries the exact same window instead of skipping rows or double-loading them.

How it works

1. read_watermark (io.kestra.plugin.core.kv.Get) reads the orders_sync_watermark key from the namespace KV Store. On the first run, errorOnMissing: false lets the flow fall through to the epoch (1900-01-01T00:00:00).
2. extract_since_watermark (io.kestra.plugin.jdbc.sqlserver.Query) selects every row from dbo.orders where updated_at is greater than the watermark, binding it as a :watermark prepared-statement parameter, and stores the result set with fetchType: STORE.
3. bulk_load (io.kestra.plugin.jdbc.sqlserver.Batch) reads that stored file and bulk-inserts it into dbo.orders_reporting on the destination instance, in chunks of 1,000 rows per JDBC batch call.
4. compute_new_watermark (io.kestra.plugin.jdbc.sqlserver.Query) re-reads the maximum updated_at committed on the source for the same window, with fetchType: FETCH_ONE.
5. advance_watermark (io.kestra.plugin.core.kv.Set) writes that value back to orders_sync_watermark, but only after bulk_load has already succeeded, so a mid-batch failure never advances the watermark past what was actually committed downstream.
6. notify (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) confirms the row count and new watermark; the errors block posts a separate Slack alert on failure.

Source and destination credentials are split across two pluginDefaults blocks (one for Query, one for Batch), so the same flow can point at two different SQL Server instances without repeating connection settings on every task.

What you get

• An idempotent incremental sync: reruns after a failure never skip or duplicate rows, because the watermark only advances on success.
• Chunked bulk inserts (1,000 rows per JDBC batch) instead of row-by-row INSERT statements.
• Watermark state tracked in the KV Store, with no external state table required.
• Slack confirmation on success and a distinct Slack alert on failure.

Who it's for

• Data engineers replicating operational tables between SQL Server instances or into a reporting database.
• Platform teams migrating off SSIS packages and SQL Server Agent jobs for cross-instance data movement.
• Teams who need resumable, chunked bulk loads without standing up a full CDC pipeline.

Why orchestrate this with Kestra

SQL Server Agent can run a stored procedure on a schedule, but it has no built-in concept of a portable watermark, no chunked bulk-insert primitive across two different instances, and no way to skip a Slack alert on success while still alerting on failure. Kestra adds all of that: the KV Store tracks state across runs, io.kestra.plugin.jdbc.sqlserver.Batch chunks the insert with automatic retry and resume behavior, and the errors block guarantees a failed run is never silent.

Prerequisites

• A source SQL Server database with a dbo.orders table containing an updated_at column suitable as a watermark.
• A destination SQL Server database with a dbo.orders_reporting table matching the selected columns.
• A Slack incoming webhook for notifications.

Secrets

• SQLSERVER_SOURCE_URL: JDBC URL for the source instance, for example jdbc:sqlserver://source-host:1433;trustServerCertificate=true.
• SQLSERVER_SOURCE_USERNAME / SQLSERVER_SOURCE_PASSWORD: credentials for the source instance.
• SQLSERVER_DEST_URL: JDBC URL for the destination instance.
• SQLSERVER_DEST_USERNAME / SQLSERVER_DEST_PASSWORD: credentials for the destination instance.
• SLACK_WEBHOOK_URL: Slack incoming webhook URL.

Quick start

1. Add the secrets above to your Kestra namespace.
2. Confirm the source dbo.orders table and destination dbo.orders_reporting table exist with matching columns.
3. Run the flow once manually to seed the watermark, then enable the hourly Schedule trigger.
4. Check the KV Store for the orders_sync_watermark key after each run to confirm it advances.

How to extend

• Swap table for an explicit sql INSERT statement on Batch if the source and destination column order differs.
• Replace the Schedule trigger with a Flow trigger so the sync fires the moment an upstream load finishes.
• Add a row-count If check after bulk_load to branch on an unexpectedly large or small batch before advancing the watermark.
• Chain a Snowflake or dbt load after bulk_load for a full cross-database ETL pattern.

Links

• Plugin docs: https://kestra.io/plugins/plugin-jdbc-sqlserver
• SQL Server orchestration with Kestra: https://kestra.io/orchestration/sql-server