Database schema management can be challenging, especially when working across multiple environments (dev, UAT, prod). This post introduces a SQL Server Database Projects template designed to simplify schema versioning, automate deployment, and enable teams to track database changes with the same rigor as application code.

What Are SQL Server Database Projects?

SQL Server Database Projects (formerly SSDT) are a declarative approach to database development that treats your schema as code. Instead of writing individual ALTER statements, you define your desired schema state, and the tooling generates the necessary scripts to move from current to target state.

Key benefits:

  • Version control: All schema changes tracked in Git
  • Build verification: Catch schema errors during build, not production
  • Automated deployment: CI/CD pipelines deploy consistently across environments
  • Schema comparison: Automatically sync databases with project state
  • Documentation: Schema structure is self-documenting in code

Why This Matters

Most teams manage database schema changes through scripts or manual updates. This approach has real pain points:

  1. Lost history: Scripts aren’t always preserved or properly versioned
  2. Inconsistent environments: Manual updates lead to drift between dev and prod
  3. High-risk deploys: Ad-hoc SQL doesn’t get tested the same way code does
  4. Onboarding friction: New team members don’t understand schema structure
  5. Rollback complexity: Undoing changes is error-prone without declarative state

By treating schema as code, you gain the safety and repeatability of application development processes.

Project Features

This template includes:

  • Pre-configured Database Project: Ready to import into Azure Data Studio or VSCode
  • Build validation: Compile and catch errors before deployment
  • CI/CD pipeline: Dev/Test/Staging/Prod promotion workflow
  • Schema comparison: Tools to sync existing databases with project state
  • Documentation: Auto-generated schema diagrams
  • Service Principal permissions: Pre-configured for automated deployments
  • Feature branch example: AdventureWorks branch shows best practices

Getting Started

Setup

  1. Install tooling (choose your editor):
  2. Clone the repository: 
    git clone https://github.com/brianjmurray/mssql-database-project.git
cd mssql-database-project
  3. Open in your editor and install the extension

Build & Verify

Right-click the database project and select Build. You should see a successful build output with no errors. The project compiles to a .dacpac file (a database package).

Small tip: Build frequently. A single error in one file can cascade to other files that reference it. Fix as you go rather than accumulating errors.

Build & Test Workflow

  1. Define schema in the project (tables, stored procedures, views, etc.)
  2. Build locally to verify syntax and references
  3. Test by deploying to a local SQL Server instance
  4. Commit and push to version control
  5. CI/CD pipeline automatically tests and deploys to target environments

CI/CD Pipeline Setup

This template includes pipeline examples for:

  • Dev: Build, stage files, generate documentation
  • UAT: Deploy to UAT environment after dev tests pass
  • Prod: Deploy to production with approval gates

Each environment is isolated, ensuring changes are validated before reaching production.

Schema Comparison & Import

Importing an Existing Database

If you have an existing database you want to bring under version control:

  1. Open the project in Azure Data Studio
  2. Right-click the project → Schema Compare
  3. Select your existing database as source
  4. Review the differences
  5. Click Update to import changes

You can filter what you import (e.g., only tables, exclude certain schemas).

Syncing Changes

If someone makes changes directly to a database, you can pull those changes back into the project using the same schema compare process. This ensures your project stays the source of truth.

Real-World Use Cases

Scenario 1: Multi-team development

  • Different teams own different schemas/modules
  • Database Projects keep everyone’s changes organized and merged safely
  • Prevents manual conflicts and duplicated work

Scenario 2: Compliance & auditing

  • Every schema change is tracked in Git with commit history
  • Who changed what, when, and why are all documented
  • Essential for regulated industries

Scenario 3: Disaster recovery

  • Database structure is in source control, separate from data
  • Recover or rebuild schema consistently across environments
  • Backup/restore focused on data, not schema

Scenario 4: Onboarding new engineers

  • New team members can clone the repo and deploy locally
  • Schema documentation is version-controlled alongside code
  • Reduces time-to-productivity

Key Permissions

For automated deployments, configure service principal with:

Server-level roles:

  • ##MS_DatabaseConnector##
  • ##MS_DatabaseManager##
  • loginmanager

Database-level role:

  • db_owner

Learning Resources

Next Steps

  1. Clone the repository and explore the structure
  2. Build the project to ensure your environment is set up
  3. Check out the feature/AdventureWorks branch to see a complete example
  4. Adapt the template to your own database schema
  5. Integrate into your CI/CD pipeline

Ready to bring your database schema under version control? Clone the repository and start building:

View on GitHub →

Have questions about database projects, schema versioning, or CI/CD for databases? Feel free to reach out on LinkedIn or via email at brian@cfktech.com.