SQL Server Connector Plugin

Overview#

The SQL Server Connector serves Atlas range requests and live subscriptions against a Microsoft SQL Server database. One plugin instance owns one ConnectionString and connects to a single SQL Server database. Use this connector when you want Atlas to query your SQL Server tables and subscribe to row changes via polling. This is one of four SQL-dialect connectors; the same configuration options apply to PostgreSQL, MySQL, and Oracle, though each dialect has specific validation rules.

Configuration#

Type string: Microsoft.SqlServer

This connector shares a unified configuration schema with the PostgreSQL, MySQL, and Oracle SQL connectors. The options below are the canonical reference for all four.

Queries sub-fields#

Each entry in the Queries dictionary contains:

Constraints on user SQL#

SQL Server imposes validation rules on the user's SELECT so it can be wrapped as a CTE. The query is rejected at config load if it:

• Ends with ; - trailing semicolons break the CTE wrap.
• Starts with WITH - a user-authored CTE cannot be a CTE body.
• Contains a top-level ORDER BY - SQL Server does not permit ORDER BY in a CTE body without a FETCH or OFFSET clause; the wrapping outer query provides ORDER BY when sort pushdown is applied.
• Contains an OPTION clause - the outer query supplies its own OPTION hint; an inner one conflicts.
• Contains FOR XML or FOR JSON - these cannot be inside a CTE.
• Starts with EXECUTE or EXEC - dynamic statements cannot wrap.

When a query fails validation, the topic is marked and no pushdown is attempted for it. On every GetRange request, the bare user SQL (without CTE wrap) is executed. Atlas applies time/row filtering locally, so results stay correct but potentially more rows cross the wire than necessary.

Connection setup#

1. Ensure SQL Server is running and reachable on the configured server and database name.

2. Provide a connection string in the Connection string field using ADO.NET syntax:

   • Local: Server=localhost;Database=mydb;Integrated Security=true;
   • Azure: Server=myserver.database.windows.net;Database=mydb;User ID=admin;Password=...;Encrypt=true;
   • See the ADO.NET SqlConnection documentation for full syntax.

3. Verify the database login has SELECT permission on every table and view the configured queries reference.

4. In the Queries dictionary, define one entry per topic you want to expose:

   • Key - operator-chosen topic name (e.g., "Orders", "Customers/ByRegion"). Slashes organize a hierarchical binding path.
   • SQL - the SELECT statement, respecting the constraints above.
   • Parameters (if the key contains [placeholders]) - a type-tag map for each placeholder (e.g., "CustomerId": "int").
   • Subscription (if you want live polling) - set WatermarkColumn to an advancing column to enable watermark mode, or leave it null for re-snapshot mode.

5. Use Test to verify connectivity without going live. The test queries the database for version and table list, reporting server name and database name on success.

Binding paths#

This plugin advertises concrete binding paths from configured queries and templated paths from parameterized topics.

Concrete paths#

Each entry in the Queries dictionary (with no [placeholders] in the key) becomes one concrete binding path:

{InstanceId}/{TopicKey}

• {InstanceId} is the source's alias (e.g., sql-1). Its minted con_ id is accepted in the same position.
• {TopicKey} is the dictionary key (e.g., "Orders" → sql-1/Orders).

A GetRange request executes the user SELECT, applies pushdown (time/row filters, sort, paging), and returns all matching rows.

Templated paths#

A topic key containing [placeholder] syntax becomes a template:

{InstanceId}/{TopicKey}

Example: a topic key "Customers/[CustomerId]" with Parameters: { "CustomerId": "int" } advertises the template:

sql-1/Customers/[CustomerId:int]

A concrete request like GetRange for sql-1/Customers/42 captures CustomerId = 42 and binds it as a typed parameter @CustomerId. The connector does not add a WHERE clause for you: your SQL must use the parameter itself.

SELECT CustomerId, Name, Region FROM dbo.Customers WHERE CustomerId = @CustomerId

The value is always bound as a parameter, never pasted into the SQL text. A key that declares a placeholder the SQL never uses logs a warning when the configuration loads, and requests on that template return the query's rows unfiltered.

Placeholders are square brackets, and the advertised template always carries a type tag. A topic key uses [Name], and the template Atlas sees is [Name:type], with string filled in when you declared no type. Curly braces are not placeholder syntax: a key written with them is taken as a literal topic name and never templates, with no error to tell you so.

Each concrete instantiation of a template is independent for subscription purposes (each carries its own watermark state).

Multi-row results#

Unlike some connectors, GetRange returns all matching rows from the query, not just the last one. If your SELECT returns 100 rows and applies no row filter, all 100 cross the wire as Rows in the ConnectorRangeResponse. Atlas's subscriber bus consumes only the last row of a multi-row snapshot emit (a known limitation tracked separately), so for multi-row re-snapshot subscriptions, only the final row reaches downstream calculated properties.

Quality column#

This plugin does not emit a Quality column. It returns only the columns in your SELECT statement, with their original names and types. If you need a quality indicator, add a literal or computed column to your query (e.g., SELECT ..., CAST(1 AS tinyint) AS Quality).

Live subscriptions#

Subscriptions are opt-out: a topic with no Subscription block defaults to re-snapshot mode.

Watermark mode (efficient, incremental)#

Set Subscription.WatermarkColumn to an advancing column (e.g., ModifiedAt, UpdatedAt, RowVersion). On each poll, the connector runs:

SELECT * FROM (...user SELECT...) WHERE {WatermarkColumn} > @__wm ORDER BY {WatermarkColumn} ASC

and emits only changed rows as EmitKind.Update. The watermark is monotonically non-decreasing per subscription.

Requirements:

• The column must appear in the user SELECT's projection list (its name must be in the SELECT clause).
• It must advance on every modification (every row update increments it).
• rowversion columns must be CAST to bigint in the user SQL (no binary type in the reader yet).
• The column name must not contain ] (which would break SQL quoting).

Re-snapshot mode (universal, full re-fetch)#

When Subscription is declared but WatermarkColumn is absent, the connector runs CHECKSUM_AGG(BINARY_CHECKSUM(*)) over the result on each poll. If the checksum changes, it re-emits the full result as EmitKind.Snapshot and SubscriptionStatus.SnapshotComplete.

• Pros: works on any query; handles arbitrary mutations.
• Cons: full re-fetch on every change; collision-free only for the row count and checksum pair (acceptable for alpha).

When no Subscription block is present, the topic defaults to re-snapshot mode with the instance-level PollInterval.

Disabled subscriptions#

Set Subscription.NotSupported = true to make a topic pull-only. Subscribe returns NotSupported, and GetRange still works. Use this for time-windowed queries whose result hash changes on every poll (e.g., WHERE EventDate > DATEADD(day, -7, SYSUTCDATETIME())), which would emit constantly in re-snapshot mode.

Hot-reload#

Changing a topic's Subscription block (including PollInterval, WatermarkColumn, or the Sql itself) Faults active subscriptions, so Atlas re-subscribes against the new baseline. Removing a topic Faults its subscriptions cleanly. Adding an unrelated topic does not disturb existing subscriptions.

Error handling#

A subscription tick that throws backs off 1s → 5s → 30s → Fault on the fourth consecutive error. A single successful tick resets the counter. Check the logs for the underlying exception.

Connection status#

The instance's status in Connect says whether it can reach SQL Server, separately from whether it started. Connect checks about every 20 seconds, by opening a connection, and the check never writes anything to SQL Server. A blank connection string, one the driver rejects outright, a login SQL Server refused, or a server certificate the connector will not trust reads Connection Fault at once, with the reason; retrying cannot change any of those, and the corrected setting is checked as soon as it is saved. A database that cannot be reached reads Connecting… for a minute and then Connection Fault. Every subscription Atlas holds on the instance is kept through the outage, and the snapshot it owed is delivered once the database answers again, so nothing has to be re-subscribed. See Plugin Instance States, Connection Status and Retries for the status model and the retry ladders.

Troubleshooting#

Use the Test button in Connect's admin UI to probe your SQL Server without going live. It reports server name, database name, and SQL Server version.

Related articles#

• Connecting Data Sources - the shared source settings this plugin sits inside, including the Subscription defaults PollInterval and AllowedLatency
• How Atlas Aligns and Times Values - what Atlas does with the watermark once this plugin reports it, and how a slow source affects the rows it appears in
• Binding Concepts - how a binding path reaches this source in the first place