SQL Deploy

SQL Deploy compares the stored procedures, functions, views, triggers, tables and reference data held as .sql files in your repository against a live SQL Server database, and writes a reviewable T-SQL script containing only the objects that differ. What to deploy is a YAML manifest; where it goes is an environment file. Command line for the pipeline, a desktop application for the person who wants to see the plan first.

Most deployment tooling for stored procedures re-applies every file on every run. That works, but it destroys information: every object's modify_date becomes the date of the last deployment rather than the date it last changed, so the database can no longer tell you when anything was really modified. It also makes the deployment log useless — a thousand identical lines say nothing about what the release contained.

SQL Deploy compares first, and emits only what differs.

What it does

1. Reads the programmable objects from the target database, together with the ANSI_NULLS and QUOTED_IDENTIFIER settings each was created under.
2. Reads the .sql files from the directories the manifest names.
3. Compares them, ignoring differences that are not real: the form of the CREATE prefix, line endings, byte-order marks, surrounding whitespace, optional brackets. Two runs against an unchanged database produce identical output, byte for byte.
4. Writes two artifacts: the deployment script, and a report of what it contains.

The script is plain T-SQL with GO batch separators. It runs under any standard SQL client, on a command line or in a query window — the tool is not needed to apply it, and it contains nothing that works in only one of them. That is deliberate: the artifact is the deliverable, so it can be reviewed in a merge request, attached to a change record, or handed to whoever owns the target database. Every step and every object announces itself as it runs, so progress reads the same under any client.

Nothing is ever dropped. Objects present in the database but absent from source are listed in the report and left alone.

The manifest says what, the environment says where

version: 1

createEnvironment:
  databases:
    - { name: AppDatabase, source: Databases/App }
    - { name: Reporting }

steps:
  - name: Tables
    type: tables
    source: [Databases/App/Tables]

  - name: Views and procedures
    types: [view, procedure]
    schemas: [Sales]
    source: [Databases/App/Views, Databases/App/Procedures]

  - name: Reference data
    type: data
    mode: seed
    source: [Databases/App/Data]

  - name: Reporting procedures
    type: procedures
    database: Reporting
    source: [Databases/Reporting/Procedures]

Each step declares what it is responsible for — an object type, the schemas, optionally its own database — and that declaration is validated: a procedure sitting in a folder called Functions fails the step instead of deploying quietly. The same manifest serves every environment, because the target lives in a file beside it — deploy.uat.yaml, deploy.prod.yaml — with the server and password left as ${NAME} so the file holds no secret.

sqldeploy script --manifest deploy.yaml --environment uat --source-ref $(git rev-parse --short HEAD)
sqldeploy deploy --manifest deploy.yaml --environment uat
sqldeploy exec   --input out/deploy.sql --server SQL01 --database AppDatabase

script writes the script and the report and stops. deploy writes them and then runs the script — writing the artifacts before it connects, so what ran is on disk even when the run fails. exec runs a script that already exists. --validate-only compares and validates every step without emitting anything, which is the check to put on a pull request.

Tables and data, in the same script

Programmable objects are safe to compare as text. Tables are not: the same column added twice is not the same as adding it once, so tables are compared through a schema model and the resulting ALTER statements go into the same script. That step is the only one that can be destructive, and it is marked as such wherever it appears: a change that would drop a column stops the script before it gets there unless data loss is explicitly allowed, and the exit code says review this either way.

Reference data is held as files too. A data step lists, per table, the rows it would insert, update or delete — as a seed into a table the same run creates, or as a merge into one that already exists.

A window on the plan

The desktop application puts the same engine behind a window: pick a manifest and an environment, and the plan appears as a tree of steps and objects with a side-by-side diff of each change. Untick an object to leave it out of the script. Read the script that will run. Deploy it and watch the objects tick off as the server reaches them, then see the plan compared again — what still differs is what is still to do. An environment file decides what the application may do there: a production environment can be compared and its script copied for the release process, but never deployed by clicking.

A blank server is one press away from a deployed one: the application creates the databases the manifest declares, compares the sources against what it has just built, and deploys that.

SQL Deploy is free to use and open source.