Docs/Engines/SQL Server

SQL Server

SQL Server runs migrations inside a transaction, so a failed migration rolls back. Connections go through pymssql, which limits how you can authenticate.

type

sqlserver

Driver

pymssql

DDL

Transactional

Identifiers

Case-insensitive

Install

pip install "dblift[sqlserver]"

Connection URL

mssql+pymssql://user:password@localhost:1433/app

mssql:// is normalised to mssql+pymssql; a jdbc: URL is rejected. The aliases mssql, tsql and sql_server all select this engine.

dblift.yaml

database:
  type: sqlserver
  url: "mssql+pymssql://db.internal:1433/app"
  schema: "dbo"
  encrypt: true
  connection_timeout: 30

Engine settings

KeyDefaultEffect

encryptfalsetrue becomes encryption=require; false becomes encryption=off.

instance—Named instance, encoded into the host as host\INSTANCE.

connection_timeout30Sent as login_timeout.

integrated_securityfalseNot supported by the native driver — setting it raises a configuration error. Use a username and password.

trust_server_certificatefalseAlso unsupported. Configure certificate trust in FreeTDS, or turn encryption off explicitly.

fail_on_fixed_dbofalseSet under database:. When true, a login mapped to the fixed dbo user (sa, a sysadmin, or the database owner) with schema set to anything other than dbo fails before any migration or callback statement runs. No history row is written. The environment variable is DBLIFT_DB_FAIL_ON_FIXED_DBO (1, true, or yes). Default false warns and continues.

Behaviour notes

DDL is transactional.

A migration that fails half-way rolls back completely.

A dbo-mapped login cannot change schema.

With schema set to anything other than dbo (any case), DBLift logs a warning and continues: unqualified objects are created in dbo and the run is reported successful, the same outcome as 4.8.0. Set fail_on_fixed_dbo to true to fail instead. schema: dbo does not warn and does not send ALTER USER. A dry-run migrate or undo, and a migrate with nothing pending, do not predict the warning or the failure. clean, including clean --dry-run, does run the check. Do not switch an existing deployment's schema to dbo to silence the warning: that targets [dbo].[dblift_schema_history] and replays every migration.

Batches are split on GO.

The batch separator is honoured, so scripts written for sqlcmd run unchanged.

Online index builds, not concurrent ones.

Use the ONLINE option; CREATE INDEX CONCURRENTLY does not exist here.

Unquoted identifiers are case-insensitive.

Brackets are the quoting form.

PreviousOracle NextIBM Db2