# SQL Server Schema Migration — Database CI/CD for SQL Server

> On SQL Server the deploy script is often generated — a DACPAC diff or an SSMS one-off that runs before anyone reads it. Bytebase puts SQL Server change on a CI/CD pipeline: every change reviewed, staged, approved, and audited across SQL Server, Azure SQL, Managed Instance, and RDS — so a generated drop-and-add never reaches production unread.

Source: https://www.bytebase.com/databases/sql-server/schema-migration/

---

## Your app ships through CI/CD. Your SQL Server schema ships from SSMS.

A developer runs the change in SSMS, or a DACPAC publish generates and runs the deploy script — either way, no review, no staging, no record, and the SQL that executes is the SQL nobody read. The DDL was never the hard part; the pipeline around it is. Bytebase puts SQL Server change on the same pipeline as your code — reviewed, staged, approved, and audited — from a Git pull request or the Bytebase UI.

On SQL Server the ungoverned path skips every check that keeps a change safe:

### The DACPAC writes the SQL

State-based deployment (SSDT/DACPAC) diffs desired against actual and generates the deploy script. Nobody authored it, and a publish runs whatever the diff produced — no one has to read it first.

### No eyes on it, before or after

A rename surfaces in the diff as a destructive DROP plus ADD, so the dangerous version is the one that runs — and afterward there's no migration history of what changed or who ran it. You can't see the damage coming or trace it once it's done.

### One change, many databases

SQL Server shops run the same schema across many databases — a database per tenant, an instance per region. By hand that's the same change pasted into SSMS one database at a time; miss one or fat-finger another and they quietly diverge.

### Environments drift

The SSDT project, dev, staging, and prod diverge the moment one change lands outside the pipeline — and the next publish generates a surprise diff against a database it no longer matches.

## Every change runs the same path.

A change starts as a Git pull request or in the Bytebase UI — and from there the path is one, the same across SQL Server, Azure SQL, Managed Instance, and RDS.

The migration script is the same in every environment. What changes is the gate in front of each.

### Validate

SQL review runs on the change before it applies — 200+ rules catch the DROP COLUMN a DACPAC diff slipped in, a destructive type change, a missing clustered index, and naming violations. The ruleset is strict on production, relaxed on dev.

### Promote

The migration applies in dev, then staging, then production — and across a whole database group in one rollout, not pasted in database by database. The same script meets progressively more realistic data, so problems that only show at scale surface in staging. A failure stops the chain.

### Approve

Approval gates sit between stages. Routine changes auto-promote into dev; production-bound DDL waits for a human who sees the SQL, the review results, and the rollout plan in one record.

### Record

Every applied migration is logged with version, environment, executor, and timestamp. Schemas are compared automatically, and a change made outside the pipeline triggers a drift alert.

## The generated migration, caught before it runs.

A state-based publish (SSDT/DACPAC) writes the deploy script from a schema diff. SQL review reads the generated SQL before it runs:

```
-- Generated by SqlPackage from a schema diff
ALTER TABLE dbo.Users DROP COLUMN FullName;
ALTER TABLE dbo.Users ADD FirstName NVARCHAR(255) NULL;
ALTER TABLE dbo.Users ADD LastName  NVARCHAR(255) NULL;

-- ⚠ SQL review (production ruleset)
--   • DROP COLUMN FullName destroys data — the diff read a rename as drop-and-add
--   • no copy-forward: FirstName and LastName land empty, FullName is gone for good
```

The same script promotes through dev and staging before a human approves it for production. Each environment keeps its own audit trail.

## Planned schema change — and it says so.

The pipeline gates one class of work, and gates it well: planned schema change. Emergency hotfixes, one-off data patches, and an UPDATE STATISTICS during an incident don't arrive as a reviewed change — and forcing them through the pipeline only teaches people to route around it.

## Common questions.

### Does SQL Server have built-in schema migration or versioning?

No database does, really — versioning is a tool's job. SQL Server ships SSDT and DACPAC for state-based deployment, which diffs desired against actual and generates a script, but it doesn't review that script, gate it behind approval, or promote it across environments. The pipeline is what adds review, promotion, and audit on top.

### Can it catch a destructive change before it deploys?

Yes — that's the core of it. Review reads the SQL before it applies, so a DROP COLUMN, a rename the generator turned into drop-and-add, or a type change that rebuilds a table is flagged on the change, not discovered after the data's gone. Staged rollout then proves it in dev and staging before production.

### How does this handle state-based (DACPAC/SSDT) deployments?

Bytebase is migration-based: each change is an explicit, versioned, reviewable script. If your team generates that script from a DACPAC diff, the pipeline reviews the generated SQL — catching the silent drop-and-add or full table rebuild a publish would otherwise run unread — then promotes and audits it like any other migration.

### Doesn't SQL Server's transactional DDL already make changes safe?

Transactional DDL lets you wrap a change in a transaction and roll it back — a real advantage over MySQL or Oracle. But it doesn't review the SQL, gate production behind approval, promote across environments, or catch drift, and a forgotten open transaction holds the schema lock and blocks production. The rollback safety net is not the pipeline.

### Which SQL Server flavors are supported?

Any SQL Server Bytebase can connect to — on-prem SQL Server (Enterprise and Standard), Azure SQL Database, Azure SQL Managed Instance, Synapse, Fabric SQL database, and AWS RDS for SQL Server. The pipeline runs in front of the database, so there's no edition requirement.

## Get Started

- [Contact us](https://www.bytebase.com/contact-us/)
- [Start now (cloud)](https://console.bytebase.com)