Multiple gateway pipeline for same database

[srikanthp24]

Hi Team,

To do the POC on Lakeflow Connect Ingestion Pipeline, we directly connected the sql server prod DB and build the pipeline it is running successfully. Now, My question is we need to move that to UAT and Prod. In Dev we have created througn UI Wizard. In UAT I am planning to creating using the DABs. Is that impact existing dev pipeline or Is okay to create the multiple gateway pipeline in different  environment pointing to same source database.

Can anyone guide me on this clearly.

[Satyasai]

@srikanthp24 
Before Moving, Please consider the following Questions

Can the source SQL Server handle the additional CDC read load? (While adding a second pipeline will not break the Dev pipeline, it doubles the CDC query load on your production SQL Server.)
What is the CDC log retention window on SQL Server? Ensure your SQL Server CDC cleanup retention period is set long enough (e.g., 3–7 days). If one environment (e.g., UAT) is paused for several days and CDC logs are cleaned up on SQL Server before UAT resumes, the UAT pipeline will throw an out-of-range offset error and require a re-sync.
Are service accounts and network paths validated?

My Idea is Deploying via DABs (Best Practice Workflow)

[data_pulse]

@srikanthp24 

Yes, creating UAT/PROD with DAB is a good approach and should not impact the existing DEV pipeline, as long as each environment is deployed as a separate resource with its own names, staging location and destination schema/catalog.

Typical Example:

DEV -> gateway-dev -> ingestion-dev -> dev_catalog
UAT -> gateway-uat -> ingestion-uat -> uat_catalog
PROD -> gateway-prod -> ingestion-prod -> prod_catalog

One important limitation: each ingestion pipeline must have exactly one gateway, and a gateway cannot be shared across multiple ingestion pipelines. Can see more limitations here

If DEV/UAT/PROD all point to the same SQL Server source, it works, but remember that each gateway is a separate CDC reader, so should consider the additional load on the source and keep staging/checkpoint state separate.

On question of Creating Pipeline via DAB in Dev: The pipeline created with DAB can coexist with the existing UI created DEV pipeline as long as the bundle uses a different gateway name and ingestion pipeline name. Databricks requires unique names when creating both the ingestion pipeline and the gateway.

Alternatively, For the existing DEV pipeline created through the UI, if you later want to manage that same pipeline through DABs, use bundle bundle deployment bind rather than creating a new bundle resource.

Eg: If existing Dev pipeline Id is : 123abc and bundle resource looks like

resources:
  pipelines:
    dev_ingestion:
      name: sqlserver-dev-ingestion

Then bind that resource to the existing DEV pipeline:

databricks bundle deployment bind
  dev_ingestion
  123abc
  --target dev

Same for prod too, Can bind the Existing Pipelines (As above).

[stbjelcevic]

@srikanthp24 ,

To your core question: multiple gateway pipelines against the same source database don't interfere with each other. Each gateway keeps its own independent state, its own staging volume, and its own CDC/change-tracking cursor, so a separate Dev, UAT, and Prod gateway pointing at the same SQL Server won't step on one another. As long as each pair uses distinct names, staging, and destination schemas (as noted above), your existing Dev pipeline is unaffected. Databricks even points at this pattern for resolving table-name conflicts: create multiple gateway-pipeline pairs writing to different destination schemas.

The one thing I'd watch that isn't obvious: the risk with several environments isn't on the Databricks side, it's the SQL Server CDC cleanup job. It runs on a retention schedule and isn't aware of individual consumers, so your least-active environment sets the floor. If a UAT or Dev gateway sits stopped past the CDC retention window, its cursor ends up pointing at change data that's already been purged, which forces a full refresh of the affected tables. That's why the gateway must run continuously, and why you should size source-side CDC retention for the slowest gateway rather than the typical one.

Two smaller notes. Adding more gateways doesn't consume extra CDC capture instances, since they all read the same capture instance rather than creating their own, so you won't hit SQL Server's two-instance-per-table limit just by adding environments. And since each environment adds a continuous reader against the source, change tracking is recommended over CDC for any table with a primary key to keep source load down. If you're greenfielding UAT/Prod, the newer Integrated CDC (Beta) mode also collapses the gateway and pipeline into one, worth a look.

[data_pulse]

Thanks for the clarification @stbjelcevic 

The docs say the SQL Server gateway must run continuously and can resume from its previous point as long as the required source logs still exist. Is there any documented way to determine how far behind a gateway can safely fall before a full refresh is required or is that entirely governed by the SQL Server CDC/change-tracking retention configuration?

Also is there a recommended metric/event/log to monitor the gateway’s current CDC position/ lag against the old retained source log, so we can alert before the retention window is exceeded?