SQL Server change model
01Rowversion is an ordering clue, not business time.
A Microsoft SQL Server route may span on-premises estates, cloud-hosted databases, reporting stores, and partner destinations. Schemas, views, type conversions, query load, transaction scope, and write behavior must be explicit for a repeatable production flow.
| Decision | Integration approach |
|---|---|
| Server, database, schema, view | Define server and database, schema, tables or views, query, selected columns, filters, ordering, keys, time zones, and SQL Server-specific types. Confirm the selection can be repeated and reconciled. |
| Resume with token plus key | Use deterministic batches or checkpoints for large extracts and loads. Set transaction boundaries so a retry can identify committed work and avoid duplicating identity, key, or update operations. |
| Map SQL Server types deliberately | Specify nulls, decimal precision, date and time types, Unicode text, binary values, identity and keys, insert or update policy, constraints, rejected rows, and reconciliation counts. |
| Authentication and access | Configure the selected SQL driver and connection string. Validate authentication, TLS and least-privilege grants on the exact database version; keep credentials outside the flow example. |
SQL Server-aware Flower route
02Keep query visibility and write atomicity in the same plan.
Flower's SQL Server adapter supports structured reads, writes, and transactions using SQL Server parameter and identifier rules. The route adds deterministic selection, processing, validation, lineage, and recovery around those database operations.
Extract with a stable cursor
Use rowversion as a binary watermark paired with a unique key and explicit ordering. Persist the batch watermark and last processed tuple to create a precise, repeatable incremental extraction path.
Normalize before crossing engines
Validate decimal scale, datetime2 precision, datetimeoffset, Unicode nvarchar, uniqueidentifier, bit, varbinary, and nullability. Identity and computed columns need an explicit destination policy instead of blind insertion.
Commit with affected-row evidence
Prepared writes execute in managed transactions. Flower reconciles committed keys, affected rows, rejects, and constraints before advancing the source cursor; an interrupted connection triggers verification, not automatic replay.
SQL Server operating policy
03Choose isolation with the database owner, not by accident.
Default READ COMMITTED can wait on locks; READ_COMMITTED_SNAPSHOT and SNAPSHOT use row versions and consume version-store resources. An extraction design must fit the database configuration, workload, and recovery objective.
Locking or version-store budget
Align isolation settings, query timeouts, batch duration, and connection pooling with the database workload. Monitor locking, version-store activity, transaction-log usage, and extraction progress to keep operations predictable.
Change and delete coverage
Combine rowversion-based extraction with business keys and reconciliation policies. Soft-delete markers or tombstones can carry lifecycle events into the flow, helping teams maintain a clear relationship between source records and delivered data.
Topology and read target
Document whether the endpoint is primary, a readable replica, Azure SQL, or another managed form; define expected lag and failover behavior. A connection that succeeds after failover still needs cursor and snapshot reconciliation.