Metadata-Version: 2.5
Name: tirion-dagster-mssql
Version: 0.29.17.post1
Summary: Microsoft SQL Server storage for Dagster
Project-URL: Homepage, https://github.com/tirion-tools/dagster
Project-URL: Source, https://github.com/tirion-tools/dagster/tree/standalone/python_modules/libraries/dagster-mssql
Author: Tirion Tools
License-Expression: Apache-2.0
License-File: LICENSE
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Requires-Python: <3.14,>=3.10
Requires-Dist: dagster<1.14,>=1.13.17
Requires-Dist: pyodbc>=5.0
Description-Content-Type: text/markdown

# tirion-dagster-mssql

Microsoft SQL Server storage for Dagster: run storage, event log storage and schedule
storage, backed by SQL Server 2017+, Azure SQL Database, or Azure SQL Managed Instance.

```sh
pip install tirion-dagster-mssql
```

The distribution is `tirion-dagster-mssql`; the module it provides is `dagster_mssql`.

## Relationship to dagster

SQL Server support needs changes inside `dagster` core, which are proposed upstream but
not in a released `dagster` yet. This build applies the same changes at import time (see
`dagster_mssql/_compat.py`) so it works against a released `dagster` from PyPI.

That is why the dagster pin has an upper bound. The patched internals are private, so a
dagster minor bump can move them; `_compat` verifies what it is about to change and
refuses rather than guessing, so an unsupported dagster fails at import with a message
naming what moved instead of silently building a wrong schema.

Version numbers follow dagster's own library convention: `0.29.x` pairs with dagster
`1.13.x`.

When the changes land in a released `dagster`, `_compat` detects them and does nothing.
At that point `storage: mssql:` in `dagster.yaml` starts working too, with no reinstall,
because core resolves that shorthand to the `dagster_mssql` module this package provides.

## Requirements

`dagster-mssql` connects through [pyodbc](https://github.com/mkleehammer/pyodbc), which
dispatches to a locally installed ODBC driver. Install
[Microsoft ODBC Driver 18 for SQL Server](https://learn.microsoft.com/sql/connect/odbc/download-odbc-driver-for-sql-server)
(or 17) on every host that runs Dagster — the webserver, the daemon, and any code
location.

## Configuration

In `$DAGSTER_HOME/dagster.yaml`, configure each storage explicitly. The shorter
`storage: mssql:` form shown further down needs the upstream core changes and does not
work on a released `dagster` yet:

```yaml
run_storage:
  module: dagster_mssql.run_storage
  class: MSSQLRunStorage
  config:
    mssql_url: "mssql+pyodbc://user:password@host:1433/dagster?driver=ODBC+Driver+18+for+SQL+Server"

event_log_storage:
  module: dagster_mssql.event_log
  class: MSSQLEventLogStorage
  config:
    mssql_url: "mssql+pyodbc://user:password@host:1433/dagster?driver=ODBC+Driver+18+for+SQL+Server"

schedule_storage:
  module: dagster_mssql.schedule_storage
  class: MSSQLScheduleStorage
  config:
    mssql_url: "mssql+pyodbc://user:password@host:1433/dagster?driver=ODBC+Driver+18+for+SQL+Server"
```

Once the upstream changes ship, the same configuration collapses to:

```yaml
storage:
  mssql:
    mssql_url: "mssql+pyodbc://user:password@host:1433/dagster?driver=ODBC+Driver+18+for+SQL+Server"
```

or configure the parts separately:

```yaml
storage:
  mssql:
    mssql_db:
      username: dagster
      password:
        env: DAGSTER_MSSQL_PASSWORD
      hostname: sqlserver.example.com
      db_name: dagster
      port: 1433
      driver: "ODBC Driver 18 for SQL Server"
      params:
        Encrypt: "yes"
```

Every field is a `StringSource`/`IntSource`, so any of them can be read from an
environment variable as shown above. Anything under `params` is passed through to the ODBC
driver, which is where connection options such as `Encrypt`, `TrustServerCertificate`,
`Authentication` (for Entra ID against Azure SQL) and `MultiSubnetFailover` belong.

## Notes on SQL Server

**Enable read-committed snapshot.** SQL Server's default `READ COMMITTED` takes shared
read locks, so the daemon and the webserver will block each other under load. Postgres
does not behave this way, and neither does SQL Server once you run:

```sql
ALTER DATABASE dagster SET READ_COMMITTED_SNAPSHOT ON;
```

This is left to whoever provisions the database: it is a database-wide setting, it needs
elevated permissions, and it briefly requires exclusive access.

**Upserts retry on deadlock.** SQL Server has no `INSERT .. ON CONFLICT`, so dagster's
upserts are `MERGE ... WITH (HOLDLOCK, UPDLOCK)`, which is serializable. At that isolation
level SQL Server protects a key *range* rather than a row, so two processes writing
different rows — two daemons sending heartbeats, or materializing unrelated assets — can
still be picked as deadlock victims. That is expected rather than a fault, and SQL Server's
own message says what to do about it: rerun the transaction. `dagster-mssql` does, with
jittered backoff. Deadlocks logged at DEBUG by `dagster_mssql.utils` are normal under load;
a `DagsterMSSQLException` mentioning the deadlock victim means the retries were exhausted,
which is worth investigating.

**Collation does not matter.** Dagster's text columns are `NVARCHAR` on SQL Server, so
asset keys, partition keys and run tags containing non-ASCII characters are stored
correctly under any collation — including on SQL Server 2017, where UTF-8 collations are
not available.

**Bounded identifier columns.** SQL Server cannot index `NVARCHAR(max)`, so columns that
appear in an index key are bounded (asset keys at 256 characters, partition and step keys
at 128, and so on) rather than unbounded. Postgres has a comparable btree key limit, and
MySQL indexes only a 64-character prefix of these same columns. A value that exceeds the
bound is rejected rather than silently truncated.

## Schema migrations

`dagster-mssql` ships its own Alembic revision tree under `dagster_mssql/alembic/`, rather
than sharing the one in `dagster` core the way `dagster-mysql` and `dagster-postgres` do.
Core's tree carries per-dialect branches whose DDL is not valid on SQL Server.

The consequence is that **new core migrations are not inherited**. A fresh SQL Server
deployment is unaffected — it builds its schema with `create_all()` and stamps the head —
so a missing migration is invisible until an existing deployment runs
`dagster instance migrate` and silently receives nothing.

`dagster_mssql/alembic/__init__.py` records `SYNCED_TO_CORE_REVISION`, the head of core's
tree this one was last reconciled against.
`dagster_mssql_tests/test_migrations.py::TestCoreRevisionParity` fails as soon as core's
head moves, and names the revisions that appeared. When that happens, each new core
revision must either be ported to `dagster_mssql/alembic/versions/` or recorded in
`CORE_REVISIONS_NOT_APPLICABLE` with the reason it does not apply — and only then may
`SYNCED_TO_CORE_REVISION` be advanced.

Schema snapshots for back-compatibility testing live in `dagster_mssql_tests/compat_tests/`;
see that module's docstring for how to capture one.

## Testing

The tests need Docker. `pytest` brings up a SQL Server container via
`dagster_mssql_tests/docker-compose.yml`, creates the test database, and runs Dagster's
shared storage suites against it:

```sh
tox -e py312-storage_tests
```
