SQL Server Source Control for Developers: Best Practices and Workflow

Naming: prefix migration files with ISO-8601 timestamps or incremental numbers to enforce order.

Branching and Workflow

Follow Git flow principles adapted for databases:

1. Feature branches: each developer works on a feature branch containing migration scripts and schema changes.
2. Pull requests: include migration scripts plus tests or verification steps.
3. Code review: reviewers check for destructive operations, data-migration safety, and idempotency.
4. Merge to main: only merged after CI passes.

Avoid long-lived DB feature branches; keep changes small and incremental.

Writing Safe Migration Scripts

• Always wrap data-changing operations in transactions where supported.
• Make schema changes backward-compatible where possible (add columns nullable or with defaults).
• For destructive changes (drop column/table), prefer a two-step process: mark deprecated in one release, drop in a later release after consumers are updated.
• Use checks to avoid errors on repeated runs:
  • IF NOT EXISTS for CREATE
  • IF EXISTS before DROP
• Keep migrations idempotent where feasible, or ensure they run exactly once via a migrations table.

Example pattern:

sql
 ffON2NH02oMAcqyoh2UU MQCbz04ET5EljRmK3YpQ CPXAhl7VTkj2dHDyAYAf” data-copycode=“true” role=“button” aria-label=“Copy Code”>Copy CodeCopied
BEGIN TRAN;
IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = ‘NewCol’ AND object_id = OBJECT_ID(‘dbo.MyTable’))
BEGIN
  ALTER TABLE dbo.MyTable ADD NewCol INT NULL;
END
COMMIT TRAN;

Tracking Applied Migrations

Maintain a migrations table (e.g., dbo.SchemaVersions) that records applied script id, hash, applied_by, applied_at. Migration runners should:

• Check the table before applying
• Validate script hashes to detect drift
• Apply only unapplied scripts in order

Tooling Recommendations

• Migration runners: Flyway, RoundhousE, DbUp, or custom tooling.
• State-based tooling: SQL Server Data Tools (SSDT), Redgate SQL Source Control, SQL Compare for schema sync.
• CI/CD: run migrations in pipelines using agents that have access to ephemeral test databases.
• Use a linter/static analyzer for SQL (tSQLLint, SQLFluff with SQL Server dialect) in CI.

Testing Strategy

• Unit: test individual stored procedures/functions in isolation.
• Integration: apply migrations to a clean database and run end-to-end tests.
• Smoke: run a minimal CI job that applies migrations and runs basic health checks.
• Data migration tests: include tests that verify data transformations and performance characteristics.

Automate test database provisioning (local Docker SQL Server images or ephemeral cloud instances).

CI/CD Pipeline Example

1. On PR: lint migrations and schema; apply migrations to a test DB; run unit/integration tests.
2. On merge to main: build artifacts (DACPAC), run full migration on staging using the migration runner, run acceptance tests.
3. On release: apply migrations in a transaction-backed deployment window; monitor and rollback plan ready.

Include rollback procedures: have reverse scripts for complex migrations or backups/snapshots for quick recovery.

Handling Reference/Seed Data

• Treat static reference data as versioned scripts in /seed/.
• Apply seed scripts as part of migrations when adding or changing critical lookup values.
• For large data loads, use separate ETL pipelines instead of embedding massive inserts in migrations.

Security and Permissions

• Use least privilege for automated migration accounts (ALTER, CREATE, INSERT as needed).
• Avoid running migrations as a sysadmin unless absolutely required.
• Audit schema changes through the migrations table and VCS history.

Monitoring and Observability

• Log migration runs centrally.
• Alert on failed migrations or schema drift detected in production vs repository.
• Periodically compare production schema to repository (automated schema drift checks).

Common Pitfalls and How to Avoid Them

• Direct changes in production: enforce policy that all changes go through VCS and CI.
• Large monolithic migrations: split into smaller, incremental scripts.
• Missing rollback plan: test rollbacks in staging and maintain backups.
• Relying only on state comparisons: supplement with migration scripts for complex data changes.

Checklist Before Deploying a Migration

• Migration script reviewed and idempotent or versioned.
• Tests passed in CI against a clean DB.
• Backups/snapshots available for the target environment.
• Rollback plan documented.
• Runbook for deployment and verification steps prepared.

Conclusion

Source-controlling SQL Server for developers requires discipline, clear repository layout, safe migration practices, and automation in testing and deployment. Choose a model (migration, state, or hybrid) that suits your team’s needs, enforce small changes, and integrate checks into CI/CD to minimize risk and keep schema and data changes predictable.

References and further reading

• Flyway, DbUp, SSDT documentation
• tSQLLint, SQLFluff for linting
• SQL Server migration patterns and best practices (vendor docs)