Tag: SQL Database Projects

Data Development, Databases, DP-800, Microsoft Certification

Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners – Part 1 (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Microsoft expects SQL AI Developers to understand not only how to develop database solutions but also how to deploy them safely, consistently, and securely using modern DevOps practices.

Organizations rarely allow developers to deploy SQL changes directly into production. Instead, database changes pass through controlled deployment pipelines that validate the code, enforce security policies, require approvals, and ensure only authorized changes reach production.

Modern SQL development emphasizes:

  • Source-controlled database projects
  • Automated builds
  • Automated testing
  • Controlled deployments
  • Secure authentication
  • Governance through branching policies and approvals
  • Auditable deployment history

Understanding these concepts is essential for both the DP-800 exam and real-world enterprise database development.

What Are Deployment Pipeline Controls?

Deployment pipeline controls are rules and processes that ensure database changes move safely from development to production.

Instead of allowing developers to make direct changes to production databases, organizations require every change to follow a controlled workflow.

A typical workflow looks like this:

Developer
      │
      ▼
Feature Branch
      │
      ▼
Pull Request
      │
      ▼
Code Review
      │
      ▼
Automated Build
      │
      ▼
Unit Tests
      │
      ▼
Integration Tests
      │
      ▼
Approval
      │
      ▼
Deployment Pipeline
      │
      ▼
Development
      │
      ▼
Test
      │
      ▼
Staging
      │
      ▼
Production

Each stage reduces the risk of introducing errors into production.

Why Deployment Controls Matter

Without deployment controls, organizations often experience:

  • Accidental schema changes
  • Lost database objects
  • Unauthorized modifications
  • Production outages
  • Failed deployments
  • Data corruption
  • Compliance violations
  • Security risks

Deployment controls provide:

  • Consistency
  • Repeatability
  • Security
  • Governance
  • Auditability
  • Faster recovery
  • Higher software quality

For enterprise environments, these controls are considered mandatory.

SQL Database Projects and Deployment Pipelines

SQL Database Projects represent an entire database schema as source-controlled code.

Instead of modifying objects directly inside SQL Server Management Studio (SSMS), developers modify project files.

Example:

Tables
    Customers.sql
    Orders.sql
    Products.sql
Views
    SalesView.sql
Stored Procedures
    usp_CreateOrder.sql
Functions
    fn_TotalSales.sql

The deployment pipeline compares the project against the target database and generates the necessary deployment script automatically.

Benefits include:

  • Version history
  • Repeatable deployments
  • Easier collaboration
  • Automated validation
  • Reduced deployment risk

CI/CD Overview

CI/CD stands for:

Continuous Integration (CI)

Developers frequently merge changes into a shared repository.

Every commit automatically triggers:

  • Build validation
  • SQL compilation
  • Static code analysis
  • Unit testing
  • Artifact creation

Example:

Developer Commit
↓
Git Repository
↓
Automatic Build
↓
Database Project Build
↓
Validation
↓
Package Generated

Continuous Delivery (CD)

Continuous Delivery automates deployments through multiple environments.

Example:

Development
↓
QA
↓
Staging
↓
Production

Each deployment can require approvals before continuing.

Benefits include:

  • Faster releases
  • Fewer deployment errors
  • Repeatable deployments
  • Reliable rollback strategies

Understanding Branching Strategies

Branching is one of the most important deployment controls.

A branch is an independent line of development inside source control.

Instead of every developer modifying the main branch directly, developers work in isolated branches.

Example:

Main
│
├── Feature A
├── Feature B
├── Bug Fix
└── Feature C

Each branch is reviewed before merging.

Why Branching Is Important

Branching allows developers to:

  • Work independently
  • Prevent conflicts
  • Test safely
  • Review code
  • Protect production code
  • Isolate unfinished features

Without branching:

  • Developers overwrite one another’s work.
  • Unfinished code reaches production.
  • Rollbacks become difficult.

Common Branching Strategies

Several branching strategies are commonly used.

Feature Branch Workflow

The most common approach.

Each new feature receives its own branch.

Example:

Main
│
├── feature/AddOrders
├── feature/AddInvoices
├── feature/SearchCustomers

Advantages:

  • Easy code review
  • Simple testing
  • Low risk
  • Small pull requests

This is one of the most common approaches for SQL Database Projects.

GitFlow

GitFlow introduces several branch types.

Main
Develop
Feature Branches
Release Branches
Hotfix Branches

Typical workflow:

Main
↓
Develop
↓
Feature Branch
↓
Develop
↓
Release
↓
Main

Advantages:

  • Strong release management
  • Good for large teams
  • Stable production releases

Disadvantages:

  • More complex
  • Additional branch management

Trunk-Based Development

Developers merge frequently into a single shared branch.

Main
│
Developer 1
Developer 2
Developer 3
Developer 4

Developers create very short-lived branches.

Advantages:

  • Small changes
  • Faster integration
  • Less merge complexity

Disadvantages:

  • Requires excellent automated testing
  • Requires disciplined developers

Branch Protection Policies

Branch protection prevents unsafe changes.

The main branch is typically protected.

Developers cannot:

  • Force push
  • Delete the branch
  • Merge without approval
  • Merge failed builds
  • Bypass policies

Example policy:

Main Branch
✓ Build must succeed
✓ Two reviewers required
✓ No direct commits
✓ Status checks pass
✓ Linked work item required
✓ Up-to-date before merge

These policies dramatically reduce deployment mistakes.

Common Branch Protection Rules

Organizations often require:

Required Pull Requests

Direct commits are blocked.

Developers must create a pull request.

Required Reviewers

Example:

Minimum Reviewers = 2

Multiple reviewers reduce errors.

Successful Build Required

If automated validation fails, merging is blocked.

Example:

Build Failed
↓
Merge Blocked

Required Status Checks

Policies verify that:

  • Unit tests passed
  • Integration tests passed
  • Security scans completed
  • SQL build succeeded
  • Code quality passed

Only then is the merge allowed.

Prevent Force Push

Force pushes rewrite Git history.

Most organizations disable them for protected branches.

Prevent Branch Deletion

Important branches should never be accidentally removed.

Branch protection prevents deletion.

Pull Requests (PRs)

A pull request requests permission to merge one branch into another.

Example:

Feature Branch
↓
Pull Request
↓
Review
↓
Approval
↓
Merge

A pull request usually includes:

  • Description
  • Changed files
  • SQL object modifications
  • Reviewer comments
  • Build status
  • Test results

Benefits of Pull Requests

Pull requests improve quality by encouraging:

  • Peer review
  • Knowledge sharing
  • Early defect detection
  • Security review
  • Coding standard enforcement

For SQL projects, reviewers often examine:

  • Table changes
  • Index changes
  • Stored procedures
  • Permissions
  • Migration scripts
  • Performance impacts

Code Reviews

Code reviews help identify issues before deployment.

Reviewers commonly check:

Correctness

Does the SQL produce the expected results?

Performance

Are indexes appropriate?

Will queries scale?

Security

Are permissions appropriate?

Is SQL injection prevented?

Maintainability

Is the code readable?

Are naming standards followed?

Backward Compatibility

Will existing applications continue working?

Code Owners

One important governance feature is Code Owners.

A Code Owners file automatically assigns reviewers based on the files that change.

Example:

Tables/*
→ Database Team
StoredProcedures/*
→ Backend Team
Security/*
→ Security Team

When a developer modifies a protected object, the correct experts are automatically requested to review the change.

Benefits of Code Owners

Code Owners provide several advantages:

  • Automatic reviewer assignment
  • Faster review workflows
  • Consistent governance
  • Improved accountability
  • Better code quality
  • Subject matter expert validation
  • Compliance with organizational policies

For example:

  • Changes to security-related scripts can require approval from the security team.
  • Changes to database schema objects can require approval from database administrators.
  • Changes to deployment scripts can require DevOps team approval.

This ensures that critical database components are always reviewed by the appropriate personnel before deployment.

Best Practices for Branching and Pull Requests

Microsoft recommends following modern DevOps practices when managing SQL Database Projects.

Some recommended best practices include:

  • Create small, focused feature branches.
  • Keep branches short-lived.
  • Merge changes frequently.
  • Require pull requests for protected branches.
  • Require successful builds before merging.
  • Require automated tests before deployment.
  • Require peer reviews.
  • Protect the main branch from direct commits.
  • Use Code Owners for sensitive database objects.
  • Document pull requests with clear descriptions.
  • Resolve merge conflicts promptly.
  • Use descriptive branch names such as:
    • feature/AddCustomerSearch
    • bugfix/FixDeadlockIssue
    • hotfix/CorrectCustomerIndex

Following these practices improves collaboration, reduces deployment risk, and helps maintain a reliable, auditable database development process.

Part 1 Summary

In this first part, you learned the foundational deployment pipeline controls that are central to modern SQL DevOps and the DP-800 exam:

  • The purpose of deployment pipeline controls
  • The role of SQL Database Projects in CI/CD
  • Continuous Integration (CI) and Continuous Delivery (CD)
  • Common branching strategies (Feature Branch, GitFlow, and Trunk-Based Development)
  • Branch protection policies and why they matter
  • Pull requests and peer code reviews
  • Code Owners and automated reviewer assignment
  • Best practices for secure and reliable database development

Go to the DP-800 Exam Prep Hub main page

Databases, Data Governance, SQL, Microsoft Certification, DP-800

Update a SQL database project and deploy changes (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Update a SQL database project and deploy changes

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

For the DP-800 exam, you should understand how to modify a SQL Database Project, validate the changes, build the project into a DACPAC, compare the project to a target database, generate deployment scripts, publish changes safely, and integrate the entire deployment process into a CI/CD pipeline.

What Is a SQL Database Project?

A SQL Database Project is a source-controlled representation of a database schema. Rather than directly modifying a production database, developers modify the project files, commit those changes to source control, and deploy them through an automated pipeline.

A SQL Database Project typically contains:

  • Tables
  • Views
  • Stored procedures
  • Functions
  • Triggers
  • Security objects
  • Roles
  • Users
  • Schemas
  • Permissions
  • Reference data (optional)
  • Project configuration

The project serves as the single source of truth for the database schema.

Why Update the Database Project?

Every database change should begin in the project—not in the production database.

Typical changes include:

  • Adding new tables
  • Modifying columns
  • Creating indexes
  • Updating stored procedures
  • Adding functions
  • Changing permissions
  • Creating new schemas
  • Modifying constraints

Example:

Original table:

CREATE TABLE Sales.Customer
(
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100)
);

Business requirement:

Store customer email addresses.

Updated project:

CREATE TABLE Sales.Customer
(
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    EmailAddress NVARCHAR(255)
);

After the project is updated, the deployment process determines the necessary ALTER TABLE statement.

Typical Deployment Workflow

The recommended workflow is:

Developer
↓
Modify SQL Database Project
↓
Validate
↓
Build DACPAC
↓
Commit to Git
↓
Pull Request
↓
Code Review
↓
Merge
↓
CI/CD Pipeline
↓
Deploy Development
↓
Deploy Test
↓
Deploy Production

This workflow provides consistency, repeatability, and auditability.

Updating Database Objects

Developers modify individual object files.

For example:

Tables
    Customer.sql
Views
    ActiveCustomers.sql
Procedures
    usp_CreateOrder.sql
Functions
    fn_TotalSales.sql

Each object exists as its own SQL file.

Benefits include:

  • Easier source control
  • Better merge handling
  • Clear code reviews
  • Object-level change history

Schema Validation

Before deployment, the project should validate successfully.

Validation checks include:

  • Syntax errors
  • Missing object references
  • Invalid dependencies
  • Duplicate object names
  • Constraint issues
  • Circular references

Early validation prevents deployment failures.

Building the Project

Once validated, the project is built into a DACPAC.

A DACPAC contains:

  • Database schema
  • Metadata
  • Deployment model

It does not include:

  • User data
  • Transaction logs
  • Database backups

The DACPAC becomes the deployment artifact used throughout the pipeline.

What Happens During Deployment?

Deployment compares:

Desired State (DACPAC)
↓
Target Database
↓
Difference Analysis
↓
Deployment Script
↓
Database Update

The deployment engine generates only the necessary changes.

Example:

Project:

EmailAddress column exists

Target database:

EmailAddress missing

Generated deployment:

ALTER TABLE Sales.Customer
ADD EmailAddress NVARCHAR(255);

Declarative Deployment Model

SQL Database Projects use a declarative deployment model.

Developers describe the desired database schema rather than writing migration scripts manually.

Instead of:

Run these SQL commands.

You define:

The database should look like this.

The deployment engine determines the required SQL statements.

Incremental Deployments

Deployments are incremental.

Only differences are deployed.

If no differences exist:

No deployment changes

If one object changes:

Only that object is updated.

This minimizes deployment time and risk.

Deployment Reports

Before publishing, SQL Database Projects can generate a deployment report.

The report identifies:

  • New objects
  • Modified objects
  • Removed objects
  • Security changes
  • Dependency changes

Reviewing the report before production deployment is a best practice.

Deployment Scripts

Instead of deploying immediately, teams often generate a deployment script.

Benefits include:

  • DBA review
  • Change approval
  • Compliance auditing
  • Troubleshooting
  • Rollback planning

Example workflow:

Build
↓
Generate Script
↓
Review
↓
Approve
↓
Deploy

Publish Profiles

A publish profile stores deployment settings.

Typical settings include:

  • Target server
  • Database name
  • Authentication
  • Deployment options
  • Object exclusions
  • Ignore settings

Rather than entering these settings each time, teams reuse publish profiles.

Deployment Options

Deployment options control deployment behavior.

Common examples include:

  • Block deployment on data loss
  • Drop objects not in source
  • Ignore permissions
  • Ignore users
  • Ignore role memberships
  • Ignore whitespace differences
  • Ignore filegroups

Proper configuration reduces deployment risk.

Handling Schema Drift

Before deployment, the deployment engine compares:

Project

↓

Production

If unexpected differences exist:

  • deployment report identifies them
  • deployment script reflects them
  • pipeline may fail
  • manual approval may be required

This helps prevent accidental overwriting of production changes.

Deploying Through CI/CD

Modern SQL deployments are automated.

Typical Azure DevOps or GitHub Actions workflow:

Developer Commit
↓
Build
↓
Validate
↓
Create DACPAC
↓
Run Tests
↓
Schema Comparison
↓
Generate Deployment Script
↓
Approval
↓
Deploy

Automation reduces manual errors.

Safe Deployment Practices

Good deployment practices include:

  • Always build before deployment.
  • Validate object dependencies.
  • Review deployment reports.
  • Use pull requests.
  • Test deployments in lower environments.
  • Generate deployment scripts.
  • Back up production before deployment.
  • Avoid direct production edits.

Environment-Specific Deployments

The same DACPAC can deploy to:

  • Development
  • Test
  • QA
  • Staging
  • Production

Environment-specific settings come from publish profiles or pipeline variables.

Rollback Considerations

Unlike application deployments, database rollbacks can be difficult because:

  • Data may have changed.
  • Schema changes may be irreversible.
  • Dropped columns may lose data.
  • Constraint changes may affect applications.

Best practices include:

  • Backup databases
  • Generate deployment scripts
  • Test deployments
  • Use staged rollouts
  • Block deployments that could cause data loss

Common Deployment Problems

Missing Dependencies

Example:

Procedure references a table that does not exist.

Validation catches this before deployment.

Schema Drift

Someone manually modified production.

Deployment identifies unexpected differences.

Data Loss Warnings

Example:

ALTER TABLE Employee
DROP COLUMN Salary;

The deployment engine warns that existing data will be lost.

Permission Errors

The deployment account lacks sufficient permissions.

Required permissions often include:

  • ALTER
  • CREATE
  • DROP
  • EXECUTE
  • CONTROL (depending on deployment scope)

Using SqlPackage

Microsoft’s SqlPackage utility is commonly used for automated deployments.

Common actions include:

Build DACPAC
↓
Generate Deploy Report
↓
Generate Script
↓
Publish Database

Examples:

Generate deployment report:

SqlPackage /Action:DeployReport

Generate deployment script:

SqlPackage /Action:Script

Publish:

SqlPackage /Action:Publish

Azure DevOps Integration

Azure DevOps pipelines commonly perform the following:

  • Restore dependencies
  • Build SQL project
  • Produce DACPAC
  • Validate project
  • Run tests
  • Publish artifacts
  • Deploy to development
  • Require approval
  • Deploy to production

Approvals and gates help prevent accidental production deployments.

GitHub Actions Integration

GitHub Actions follows a similar workflow:

Push
↓
Build SQL Project
↓
Generate DACPAC
↓
Validate
↓
Deploy

Secrets such as connection strings are stored using GitHub Secrets rather than in project files.

Best Practices

  • Treat the SQL Database Project as the authoritative database definition.
  • Make schema changes only within the project.
  • Keep all database objects in source control.
  • Build the project after every change.
  • Validate dependencies before deployment.
  • Review deployment reports and generated scripts.
  • Deploy through automated CI/CD pipelines.
  • Test deployments in non-production environments.
  • Protect production deployments with approvals.
  • Keep publish profiles and pipeline configurations under version control where appropriate, excluding sensitive information.

DP-800 Exam Tips

Remember these important exam points:

  • SQL Database Projects use a declarative deployment model.
  • Building the project creates a DACPAC.
  • Deployments compare the desired schema with the target database.
  • Deployment reports identify planned changes before publishing.
  • Publish Profiles simplify repeatable deployments.
  • CI/CD pipelines automate building, validating, and deploying database changes.
  • Schema drift should be detected before deployment.
  • Production changes should originate from the SQL Database Project rather than direct database modifications.

Practice Exam Questions

Question 1

A developer adds a new stored procedure to a SQL Database Project. What should be the next step before deployment?

A. Restart the SQL Server service.

B. Build and validate the SQL Database Project.

C. Export the production database.

D. Rebuild all indexes.

Answer: B

Explanation: Building validates the project, checks dependencies, and produces the DACPAC used for deployment.

Question 2

What artifact is produced when a SQL Database Project is successfully built?

A. BACPAC

B. MDF file

C. DACPAC

D. Transaction log

Answer: C

Explanation: Building a SQL Database Project produces a DACPAC that contains the database schema and metadata.

Question 3

What is the primary purpose of a deployment report?

A. To store backup data

B. To monitor CPU usage

C. To list planned schema changes before deployment

D. To compress the database

Answer: C

Explanation: Deployment reports allow administrators to review proposed schema changes before they are applied.

Question 4

Which deployment model is used by SQL Database Projects?

A. Declarative deployment

B. Manual migration

C. Script-first deployment

D. Procedural deployment

Answer: A

Explanation: SQL Database Projects describe the desired end state, allowing the deployment engine to determine the required SQL statements.

Question 5

Why are Publish Profiles useful?

A. They encrypt databases.

B. They permanently store passwords inside source code.

C. They save deployment settings for reuse.

D. They improve query execution plans.

Answer: C

Explanation: Publish Profiles store deployment configuration such as server names, database names, and deployment options.

Question 6

What should a deployment pipeline typically do before publishing database changes?

A. Delete all indexes.

B. Generate and review a deployment script.

C. Disable all constraints.

D. Shrink the database.

Answer: B

Explanation: Reviewing generated deployment scripts helps identify unintended schema changes before deployment.

Question 7

Why is schema validation performed during the build process?

A. To increase transaction log size.

B. To encrypt the database.

C. To identify syntax errors and dependency issues before deployment.

D. To compress database files.

Answer: C

Explanation: Validation ensures that the project is internally consistent and can be successfully deployed.

Question 8

Which statement best describes incremental deployment?

A. Every database object is recreated during each deployment.

B. Only security objects are deployed.

C. Data is copied without changing the schema.

D. Only differences between the project and target database are deployed.

Answer: D

Explanation: SQL Database Projects compare the desired schema with the existing database and deploy only the necessary changes.

Question 9

Which practice best supports reliable database deployments?

A. Making schema changes directly in production.

B. Keeping the SQL Database Project as the authoritative source.

C. Editing production objects with SSMS only.

D. Avoiding source control.

Answer: B

Explanation: Using the SQL Database Project as the single source of truth supports consistent, repeatable, and auditable deployments.

Question 10

A team wants to automate database deployments across Development, Test, and Production environments. What is the recommended approach?

A. Manually execute SQL scripts on every server.

B. Use separate copies of the project for each environment.

C. Build one DACPAC and deploy it through a CI/CD pipeline using environment-specific settings.

D. Create a new SQL Database Project for every deployment.

Answer: C

Explanation: A single validated DACPAC can be deployed to multiple environments while Publish Profiles or pipeline variables provide environment-specific configuration.

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification

Detect schema drift by using SQL Database Projects (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Detect schema drift by using SQL Database Projects

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Understanding schema drift is essential for modern DevOps and database lifecycle management (DLM). The DP-800 exam expects candidates to understand how SQL Database Projects establish the desired database state, how schema drift occurs, how to detect it before deployment, and how to prevent accidental overwrites of production databases.

What is Schema Drift?

Schema drift occurs when the actual database schema no longer matches the schema stored in source control or the SQL Database Project.

In other words:

  • Source control contains the expected design
  • Database contains the actual implementation

If someone changes the production database directly, the database “drifts” away from the project.

Example:

Project contains:

CREATE TABLE Sales.Customer
(
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100)
);

A DBA later runs:

ALTER TABLE Sales.Customer
ADD LoyaltyPoints INT;

The SQL Database Project still contains only:

CustomerID
Name

The live database now contains:

CustomerID
Name
LoyaltyPoints

This difference is schema drift.

Why Schema Drift Is Dangerous

Schema drift creates several problems:

  • unexpected deployment failures
  • overwritten production changes
  • missing documentation
  • inconsistent environments
  • broken CI/CD pipelines
  • difficult troubleshooting
  • unreliable rollback

Organizations using DevOps aim to eliminate manual production changes because every manual change introduces drift.

Common Causes of Schema Drift

Manual Changes

A DBA executes:

ALTER TABLE Products
ADD InternalNotes NVARCHAR(200);

The project is never updated.

Emergency Production Fixes

A production outage occurs.

An engineer fixes the database immediately.

The fix is never committed back into Git.

Hotfix Deployments

A hotfix bypasses the normal deployment pipeline.

The database project remains outdated.

Third-Party Applications

Vendor software automatically creates:

  • indexes
  • tables
  • triggers
  • stored procedures

These objects may not exist in source control.

Automatic Maintenance Scripts

Jobs create:

  • audit tables
  • archive tables
  • logging procedures

If unmanaged, these appear as schema drift.

Desired State vs Actual State

SQL Database Projects follow a declarative model.

Instead of saying:

Execute these SQL commands.

They say:

The database should look like this.

Deployment tools compare:

Desired State (Project)

↓

Current Database

↓

Generate Deployment Script

The comparison process naturally identifies drift.

SQL Database Projects as the Source of Truth

A SQL Database Project should become the organization’s single source of truth.

Everything should originate from:

  • Git
  • pull requests
  • code reviews
  • approved deployments

Not from:

  • SSMS manual edits
  • Azure Data Studio changes
  • production hotfixes
  • direct ALTER TABLE statements

How Schema Comparison Works

The deployment engine compares:

Project Object

↓

Database Object

It evaluates:

  • tables
  • columns
  • indexes
  • views
  • procedures
  • functions
  • triggers
  • users
  • roles
  • constraints
  • sequences

Every difference is identified before deployment.

Schema Compare

Schema Compare compares:

Source

↓

Target

Possible comparisons include:

Project → Database

Database → Project

Database → Database

Project → Project

The generated report identifies:

  • missing objects
  • additional objects
  • modified objects
  • renamed objects
  • changed permissions

Example Drift Detection

Project contains:

CREATE TABLE Employee
(
    EmployeeID INT,
    Name NVARCHAR(100)
);

Database contains:

CREATE TABLE Employee
(
    EmployeeID INT,
    Name NVARCHAR(100),
    Department NVARCHAR(50)
);

Schema Compare reports:

Table: Employee
Column missing from project:
Department

Drift During Deployment

Suppose:

Project:

Customer
Orders
Invoices

Production:

Customer
Orders
Invoices
AuditLog

If the deployment option allows dropping extra objects:

Deployment may attempt:

DROP TABLE AuditLog;

This could remove an important production table.

Understanding deployment options is therefore critical.

Deployment Reports

Before publishing, SQL Database Projects can generate:

  • deployment report
  • deployment script

The deployment report shows:

  • objects added
  • objects removed
  • objects modified

Reviewing the report is a best practice.

Deployment Script Review

Instead of deploying immediately:

Generate Script

↓

Review

↓

Approve

↓

Deploy

This catches accidental schema drift before changes reach production.

Ignore Options

Some differences are expected.

Deployment settings allow ignoring:

  • whitespace
  • object order
  • permissions
  • filegroups
  • partition schemes
  • users
  • role memberships
  • extended properties

Ignoring irrelevant differences reduces false positives.

Drift Detection in CI/CD Pipelines

Typical pipeline:

Developer commits

↓

Build project

↓

Run validation

↓

Compare schema

↓

Detect drift

↓

Generate report

↓

Approve

↓

Deploy

If drift exists:

Pipeline can:

  • fail
  • warn
  • require manual approval

Preventing Schema Drift

Best practices include:

Use Source Control

Every schema change should originate in Git.

Require Pull Requests

No direct commits to the main branch.

Block Direct Production Changes

Restrict:

  • ALTER
  • CREATE
  • DROP

to deployment pipelines.

Automate Deployments

Avoid manual publishing whenever possible.

Review Deployment Reports

Always inspect changes before production deployment.

Synchronize Hotfixes

If an emergency fix is applied directly to production:

  1. Update the SQL Database Project.
  2. Commit the change to source control.
  3. Redeploy from the project.

Detecting Drift with SqlPackage

SqlPackage can compare a DACPAC with a target database.

Example:

SqlPackage /Action:DeployReport

or

SqlPackage /Action:Script

These operations generate reports showing differences before deployment.

Azure DevOps and GitHub Actions

CI/CD pipelines commonly:

  • build the SQL Database Project
  • produce a DACPAC
  • compare with the target database
  • generate deployment scripts
  • detect unexpected schema changes
  • require approval before deployment

This ensures every deployment is repeatable and auditable.

Handling Intentional Drift

Sometimes production intentionally differs.

Examples:

  • monitoring tables
  • audit tables
  • replication objects
  • vendor-managed objects

Possible approaches:

  • exclude those objects
  • maintain separate projects
  • use deployment filters
  • configure ignore settings

Schema Drift vs Data Drift

These terms are different.

Schema DriftData Drift
Structure changesData values change
TablesRows
ColumnsRecords
IndexesBusiness data
ConstraintsTransactions

Example:

Schema drift:

ADD COLUMN Salary

Data drift:

Salary changed from 50000 to 70000

Best Practices

  • Treat the SQL Database Project as the single source of truth.
  • Never make untracked production schema changes.
  • Use pull requests and code reviews for every schema modification.
  • Generate deployment reports before publishing.
  • Review deployment scripts for unintended object drops.
  • Integrate schema comparison into CI/CD pipelines.
  • Keep production synchronized with source control after emergency fixes.
  • Use deployment options carefully to avoid deleting valid production objects.
  • Automate validation whenever possible.
  • Document intentional schema differences.

DP-800 Exam Tips

Remember these key exam points:

  • Schema drift means the database no longer matches the project.
  • SQL Database Projects define the desired database state.
  • Schema Compare identifies differences before deployment.
  • Deployment reports should always be reviewed.
  • CI/CD pipelines should automatically detect drift.
  • Source control should remain the authoritative definition of the schema.
  • Direct production modifications increase deployment risk.
  • Emergency fixes should always be merged back into the SQL Database Project.

Practice Exam Questions

Question 1

A database administrator manually adds a column to a production table without updating the SQL Database Project. What has occurred?

A. Data corruption

B. Schema drift

C. Query regression

D. Database fragmentation

Answer: B

Explanation: Schema drift occurs whenever the deployed database differs from the schema stored in the SQL Database Project.

Question 2

What is considered the desired state during SQL Database Project deployments?

A. The production database

B. The deployment report

C. The SQL Database Project

D. The Query Store

Answer: C

Explanation: SQL Database Projects define the desired schema used to generate deployment changes.

Question 3

Which tool compares a SQL Database Project with an existing database?

A. SQL Profiler

B. Database Mail

C. Activity Monitor

D. Schema Compare

Answer: D

Explanation: Schema Compare analyzes differences between project schemas and deployed databases.

Question 4

Why should deployment reports be reviewed before publishing?

A. To improve indexing

B. To compress data

C. To identify unexpected schema changes

D. To rebuild statistics

Answer: C

Explanation: Deployment reports identify additions, deletions, and modifications before changes are applied.

Question 5

Which practice best minimizes schema drift?

A. Allow direct production changes

B. Disable source control

C. Store only stored procedures in Git

D. Require all schema changes through source control

Answer: D

Explanation: Requiring every schema modification to flow through source control prevents unmanaged changes.

Question 6

Which deployment option helps prevent accidental removal of valid production objects?

A. Review deployment scripts before publishing

B. Disable indexes

C. Shrink the database

D. Disable Query Store

Answer: A

Explanation: Reviewing generated scripts allows teams to identify unintended DROP statements before deployment.

Question 7

An emergency production fix was made directly on the database. What should happen next?

A. Ignore the change

B. Remove the production change immediately

C. Update the SQL Database Project and commit the change

D. Rebuild every index

Answer: C

Explanation: Production hotfixes should be reflected in the project and committed to source control to eliminate schema drift.

Question 8

Which pipeline stage commonly detects schema drift?

A. Backup compression

B. Schema comparison before deployment

C. Statistics updates

D. Data import

Answer: B

Explanation: CI/CD pipelines typically compare the desired schema with the target database before deployment.

Question 9

Which statement correctly describes schema drift?

A. It refers to changes in business data.

B. It indicates poor query performance.

C. It describes missing backups.

D. It occurs when the deployed schema differs from the SQL Database Project.

Answer: D

Explanation: Schema drift specifically concerns differences between database structure and the project’s intended schema.

Question 10

Why are ignore settings sometimes configured during schema comparison?

A. To disable security

B. To ignore expected differences that should not trigger deployments

C. To improve backup speed

D. To compress deployment packages

Answer: B

Explanation: Ignore settings reduce false positives by excluding acceptable differences such as permissions or extended properties from deployment comparisons.

Go to the DP-800 Exam Prep Hub main page

Database Administration, Databases, DP-800, Microsoft Certification

Implement secrets management (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Implement secrets management

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Modern database applications rarely operate in isolation. They connect to databases, Azure services, AI models, storage accounts, APIs, messaging services, and monitoring tools. Each connection typically requires credentials such as passwords, connection strings, API keys, certificates, tokens, or managed identities.

One of the most common security mistakes is storing these secrets directly in application code, SQL scripts, configuration files, or source control repositories. Modern DevOps practices eliminate this risk by implementing centralized secrets management, ensuring that sensitive information is securely stored, rotated, audited, and accessed only by authorized applications and users.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, candidates should understand how secrets management integrates with SQL Database Projects, Azure DevOps, GitHub, Azure Key Vault, Managed Identity, Microsoft Entra ID, CI/CD pipelines, and Azure SQL Database deployments.

What Are Secrets?

A secret is any sensitive information used to authenticate or authorize access to a resource.

Examples include:

  • Database passwords
  • SQL authentication credentials
  • Azure Storage account keys
  • Azure OpenAI API keys
  • Azure AI Search API keys
  • Connection strings
  • Service Principal secrets
  • OAuth client secrets
  • Certificates
  • Personal Access Tokens (PATs)
  • SAS tokens
  • Encryption keys

Secrets should always be protected because unauthorized disclosure can compromise systems and data.

Why Secrets Management Is Important

Poor secrets management can result in:

  • Unauthorized database access
  • Data breaches
  • Credential theft
  • Service impersonation
  • Compliance violations
  • Accidental exposure in public repositories
  • Unauthorized AI model usage
  • Financial loss

Proper secrets management helps organizations achieve:

  • Least privilege
  • Secure authentication
  • Regulatory compliance
  • Credential rotation
  • Centralized auditing
  • Simplified administration

Common Security Risks

Common mistakes include storing secrets in:

{
    "ConnectionString":
    "Server=myserver;
    User ID=admin;
    Password=P@ssword123!"
}

or

CREATE LOGIN appuser
WITH PASSWORD='MyPassword!';

or

AzureOpenAIKey=abc123xyz

These files often become part of Git repositories and remain permanently visible in version history, even if later deleted.

Principles of Secrets Management

Microsoft recommends the following principles:

  • Never hard-code secrets.
  • Never store secrets in source control.
  • Use centralized secret stores.
  • Use managed identities whenever possible.
  • Rotate secrets regularly.
  • Grant only required permissions.
  • Audit secret access.
  • Automate secret retrieval.
  • Encrypt secrets both at rest and in transit.

Azure Key Vault

Azure Key Vault is Microsoft’s centralized secrets management service.

It securely stores:

  • Passwords
  • Keys
  • Certificates
  • Tokens
  • Connection strings
  • API keys

Applications retrieve secrets at runtime rather than storing them locally.

Benefits include:

  • Centralized management
  • Encryption
  • Access policies
  • Role-Based Access Control (RBAC)
  • Secret versioning
  • Automatic rotation support
  • Auditing
  • High availability

Types of Objects in Azure Key Vault

Azure Key Vault stores three object types:

Secrets

Examples:

  • Passwords
  • API keys
  • Connection strings

Keys

Used for:

  • Encryption
  • Digital signatures
  • Key management

Certificates

Used for:

  • TLS authentication
  • Client authentication
  • Secure communications

Secret Lifecycle

Typical lifecycle:

Create Secret
↓
Store in Key Vault
↓
Grant Access
↓
Retrieve During Execution
↓
Rotate
↓
Update Applications
↓
Retire Old Version

Secret Versioning

Azure Key Vault automatically versions secrets.

Example:

DatabasePassword
Version 1
↓
Version 2
↓
Version 3

Applications can:

  • Use the latest version
  • Pin to a specific version
  • Rotate without downtime

Managed Identity

Whenever possible, Microsoft recommends using Managed Identity instead of secrets.

Managed Identity eliminates:

  • Passwords
  • Client secrets
  • Credential rotation

Instead:

Azure automatically authenticates the workload.

Supported services include:

  • Azure SQL Database
  • Azure App Service
  • Azure Functions
  • Azure Container Apps
  • Azure Kubernetes Service
  • Azure Virtual Machines
  • Azure Data Factory
  • Microsoft Fabric services (where supported)

Types of Managed Identity

System-Assigned Managed Identity

Characteristics:

  • One identity
  • Lifecycle tied to the Azure resource
  • Automatically deleted with the resource

User-Assigned Managed Identity

Characteristics:

  • Independent Azure resource
  • Shared across multiple services
  • Longer lifecycle
  • Easier identity reuse

Microsoft Entra ID Authentication

Microsoft recommends using Microsoft Entra ID authentication rather than SQL logins whenever possible.

Benefits include:

  • Centralized identity management
  • Multi-factor authentication
  • Conditional Access
  • Passwordless authentication
  • Single Sign-On
  • Managed Identity integration

Secrets in SQL Database Projects

SQL Database Projects should never contain:

  • Passwords
  • API keys
  • Tokens
  • Production connection strings

Instead they should contain:

  • Schema definitions
  • Stored procedures
  • Functions
  • Views
  • Security objects
  • Build configurations

Secrets should be injected during deployment.

Secrets in Azure DevOps

Azure DevOps supports secure secret storage through:

  • Variable Groups
  • Secret Variables
  • Azure Key Vault integration
  • Service Connections
  • Managed Identity (supported services)

Example pipeline:

Build
↓
Retrieve Secret
↓
Deploy DACPAC
↓
Remove Secret From Memory

Secrets remain encrypted throughout execution.

Secrets in GitHub

GitHub provides encrypted GitHub Secrets.

Secrets can be defined at:

  • Repository level
  • Environment level
  • Organization level

Examples:

  • SQL_PASSWORD
  • AZURE_CLIENT_ID
  • OPENAI_API_KEY

GitHub Actions retrieves them securely during workflow execution.

GitHub Actions Example

env:
  SQL_PASSWORD: ${{ secrets.SQL_PASSWORD }}

The actual password never appears in the workflow file.

Azure DevOps Example

variables:
- group: ProductionSecrets

The pipeline references the secure variable group rather than storing credentials.

Secret Rotation

Secrets should be rotated periodically.

Reasons include:

  • Compliance
  • Reduced exposure
  • Personnel changes
  • Compromised credentials
  • Security policies

Rotation process:

Create New Secret
↓
Update Applications
↓
Validate
↓
Disable Old Secret
↓
Delete Old Secret

Access Control

Access should follow the Principle of Least Privilege.

Applications receive:

  • Only required permissions
  • Only required secrets
  • Only for required duration

Avoid granting:

  • Vault Administrator
  • Owner
  • Full secret access

Unless absolutely necessary.

RBAC vs Access Policies

Azure Key Vault supports:

Azure RBAC

Uses Azure role assignments.

Examples:

  • Key Vault Secrets User
  • Key Vault Administrator
  • Key Vault Reader

Recommended for new deployments.

Access Policies

Older permission model.

Still supported but Microsoft recommends RBAC for most new implementations.

Secret Auditing

Organizations should monitor:

  • Secret retrieval
  • Failed access attempts
  • Secret updates
  • Secret deletion
  • Permission changes

Azure Monitor and Azure Activity Logs provide auditing capabilities.

CI/CD Pipeline Integration

Typical deployment:

Developer
↓
GitHub
↓
Pull Request
↓
Build
↓
Retrieve Secrets
↓
Deploy DACPAC
↓
Azure SQL Database

Secrets remain outside source control throughout the deployment.

Environment-Specific Secrets

Different environments use different secrets.

Example:

EnvironmentDatabase
DevelopmentDev SQL
TestTest SQL
ProductionProduction SQL

Each environment references its own Key Vault or secret store.

Secure Connection Strings

Instead of:

Server=myserver;
User=admin;
Password=P@ssword123

Use:

  • Managed Identity
  • Microsoft Entra authentication
  • Secret references
  • Azure Key Vault retrieval

Preventing Secret Leakage

Organizations should:

  • Enable secret scanning
  • Use repository scanning
  • Review pull requests
  • Block committed secrets
  • Rotate exposed credentials immediately
  • Monitor repositories continuously

GitHub Advanced Security and Microsoft Defender for DevOps can detect exposed credentials.

Common Mistakes

Avoid:

  • Hard-coded passwords
  • SQL logins embedded in code
  • API keys inside scripts
  • Secrets in Git repositories
  • Emailing passwords
  • Sharing credentials among developers
  • Using production secrets in development
  • Long-lived credentials
  • Ignoring secret rotation

Best Practices

Microsoft recommends:

  • Use Azure Key Vault.
  • Prefer Managed Identity over passwords.
  • Use Microsoft Entra authentication.
  • Never commit secrets to Git.
  • Rotate secrets regularly.
  • Enable auditing.
  • Apply least privilege.
  • Separate secrets by environment.
  • Automate secret retrieval.
  • Protect CI/CD pipelines.
  • Use RBAC for Key Vault authorization.
  • Monitor secret access continuously.

DP-800 Exam Tips

Remember these important points:

  • Azure Key Vault is Microsoft’s preferred centralized secrets management solution.
  • Managed Identity is preferred over passwords or client secrets whenever supported.
  • SQL Database Projects should never contain secrets.
  • GitHub Secrets and Azure DevOps Secret Variables securely provide credentials during pipeline execution.
  • Secrets should be retrieved at runtime rather than stored in source code.
  • Secret rotation reduces the risk of credential compromise.
  • Microsoft Entra ID provides modern authentication with support for passwordless and managed identities.
  • Apply least privilege to secret access.
  • Enable auditing and monitoring for secret usage.
  • Never commit connection strings containing passwords to source control.

Practice Exam Questions

Question 1

A development team wants to eliminate database passwords from its Azure-hosted application. Which authentication method should be used whenever possible?

A. SQL Authentication with a strong password

B. Windows Authentication over VPN

C. Managed Identity

D. Shared administrator account

Answer: C

Explanation: Managed Identity allows Azure resources to authenticate to supported services without storing passwords or client secrets, reducing administrative overhead and improving security.

Question 2

Which Azure service is specifically designed to centrally store passwords, certificates, keys, and connection strings?

A. Azure Key Vault

B. Azure Monitor

C. Azure Storage

D. Azure Policy

Answer: A

Explanation: Azure Key Vault provides secure storage, versioning, access control, auditing, and rotation capabilities for secrets, keys, and certificates.

Question 3

A SQL Database Project needs to connect to an Azure SQL Database during deployment. Where should the production connection string password be stored?

A. In Azure Key Vault or a secure pipeline secret store

B. In a README file

C. In the SQL project file

D. In the source code comments

Answer: A

Explanation: Production credentials should never be committed to source control. They should be stored securely in Azure Key Vault or pipeline secret stores such as GitHub Secrets or Azure DevOps Secret Variables.

Question 4

Which practice represents the greatest security risk?

A. Using Microsoft Entra ID authentication

B. Storing passwords in Azure Key Vault

C. Using Managed Identity

D. Hard-coding API keys in application source code

Answer: D

Explanation: Hard-coded secrets are easily exposed through source control, backups, or application binaries and are considered a major security vulnerability.

Question 5

Why should secrets be rotated on a regular basis?

A. To reduce the risk associated with compromised credentials

B. To improve SQL query performance

C. To reduce storage costs

D. To simplify branching strategies

Answer: A

Explanation: Regular rotation limits the usefulness of compromised credentials and helps organizations meet compliance and security requirements.

Question 6

Which GitHub feature securely provides sensitive values to GitHub Actions workflows?

A. Repository Wiki

B. GitHub Issues

C. GitHub Releases

D. GitHub Secrets

Answer: D

Explanation: GitHub Secrets securely stores encrypted values that workflows can access during execution without exposing them in source code.

Question 7

A company wants applications to authenticate to Azure SQL Database using centralized identity management, Multi-Factor Authentication, and Conditional Access policies. Which authentication method best supports these requirements?

A. SQL logins

B. Microsoft Entra ID authentication

C. Shared local accounts

D. Anonymous authentication

Answer: B

Explanation: Microsoft Entra ID provides centralized authentication with advanced security features including MFA, Conditional Access, Single Sign-On, and integration with Managed Identity.

Question 8

Which authorization principle should be applied when granting applications access to secrets?

A. Full administrative access

B. Read and write access for all developers

C. Principle of Least Privilege

D. Anonymous access

Answer: C

Explanation: Applications should receive only the permissions required to perform their tasks, reducing the potential impact of compromised identities.

Question 9

What is the primary benefit of storing secrets outside a SQL Database Project?

A. Faster database indexing

B. Reduced network latency

C. Automatic SQL optimization

D. Sensitive credentials remain protected and can be managed independently of application code

Answer: D

Explanation: Separating secrets from application code improves security, supports credential rotation, simplifies compliance, and prevents accidental exposure through source control.

Question 10

A CI/CD pipeline retrieves a database password from Azure Key Vault immediately before deploying a DACPAC. What is the primary advantage of this approach?

A. It permanently stores the password inside the DACPAC.

B. It eliminates the need for authentication.

C. It allows credentials to be securely retrieved at deployment time without storing them in source control.

D. It improves query execution plans.

Answer: C

Explanation: Retrieving secrets during deployment keeps credentials out of source control and build artifacts while allowing secure, centralized management and rotation of sensitive information.

Go to the DP-800 Exam Prep Hub main page

Data Development, Data Governance, Databases, DP-800, Microsoft Certification

Configure source control for SQL Database Projects (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Configure source control for SQL Database Projects

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Modern database development follows the same software engineering principles as application development. Rather than making changes directly in production databases, database objects are stored as source code, versioned, reviewed, tested, and deployed through automated pipelines.

SQL Database Projects provide a declarative approach to database development, where the desired database schema is maintained as source-controlled code. The database project becomes the single source of truth, and deployment tools compare the project with the target database to determine the required changes.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

  • Why source control is essential
  • How SQL Database Projects integrate with Git
  • Repository structure
  • Branching strategies
  • Pull requests and code reviews
  • Handling schema changes
  • Managing deployment artifacts
  • Best practices for collaborative development
  • Integration with Azure DevOps and GitHub

Why Source Control Matters

Without source control:

  • Database scripts become scattered.
  • Multiple developers overwrite one another’s work.
  • Changes cannot be audited.
  • Rollbacks are difficult.
  • Production drift becomes common.

Source control provides:

  • Version history
  • Change tracking
  • Collaboration
  • Branching
  • Merging
  • Code reviews
  • Automated deployment
  • Rollback capability
  • Compliance auditing

Instead of the database being the authoritative copy, the SQL Database Project stored in Git becomes the authoritative definition.

SQL Database Projects Overview

A SQL Database Project stores database objects as files.

Typical objects include:

  • Tables
  • Views
  • Stored procedures
  • Functions
  • Triggers
  • Users
  • Roles
  • Schemas
  • Security objects
  • Static data scripts

When the project is built:

  • A DACPAC is generated.
  • Deployment compares the DACPAC with the target database.
  • Required schema changes are produced automatically.

Supported Source Control Systems

Microsoft primarily supports:

  • GitHub
  • Azure Repos (Azure DevOps)
  • Local Git repositories

Older systems such as Team Foundation Version Control (TFVC) are largely superseded by Git for modern development.

Typical Repository Structure

A repository commonly contains:

DatabaseProject/
    Database.sqlproj
    Tables/
        Customers.sql
        Orders.sql
    Views/
        vwSales.sql
    Stored Procedures/
        uspInsertOrder.sql
    Functions/
    Security/
    PostDeployment/
    PreDeployment/
    RefData/
    .gitignore
    README.md

Organizing objects into logical folders makes navigation and maintenance easier.

Initializing Git

After creating a SQL Database Project:

  1. Initialize Git.
  2. Create the repository.
  3. Commit the initial project.
  4. Push to GitHub or Azure DevOps.
  5. Begin collaborative development.

Typical workflow:

Create Project
↓
Initialize Git
↓
Commit
↓
Push
↓
Create Branches
↓
Develop
↓
Pull Request
↓
Merge
↓
Deploy

Git Ignore Files

A .gitignore file prevents unnecessary files from entering source control.

Common exclusions include:

  • bin/
  • obj/
  • build outputs
  • temporary files
  • IDE cache files
  • user-specific settings

Only source code should be versioned.

What Should Be Stored in Git?

Typically stored:

  • SQL object definitions
  • SQL project file
  • Pre-deployment scripts
  • Post-deployment scripts
  • Static data scripts
  • Build configuration
  • Documentation
  • CI/CD pipeline definitions

Usually not stored:

  • DACPAC outputs
  • Temporary files
  • Build artifacts
  • IDE-generated cache
  • Local configuration files
  • Secrets
  • Passwords
  • Connection strings containing credentials

Branching Strategy

Most organizations use a branching strategy.

Example:

main
├── develop
│      ├── feature/customer-table
│      ├── feature/new-index
│      ├── bugfix/login
│      └── feature/security

Benefits include:

  • Parallel development
  • Safer releases
  • Easier testing
  • Isolated changes

Common Git Workflow

Developer workflow:

Pull latest code
↓
Create feature branch
↓
Modify SQL objects
↓
Build project
↓
Run tests
↓
Commit
↓
Push
↓
Open Pull Request
↓
Review
↓
Merge
↓
CI/CD deployment

Pull Requests

Pull requests (PRs) are central to database DevOps.

A PR allows reviewers to:

  • Inspect schema changes
  • Validate naming conventions
  • Review indexes
  • Evaluate security
  • Check performance
  • Ensure coding standards

This reduces production issues.

Merge Conflicts

Multiple developers may modify the same object.

Example:

Developer A edits:

Customers.sql

Developer B edits:

Customers.sql

Git cannot automatically determine which change is correct.

Conflict resolution requires:

  • Reviewing differences
  • Selecting the correct version
  • Combining changes
  • Testing
  • Rebuilding the project

Commit Best Practices

Good commit messages describe why changes were made.

Good examples:

Add CustomerStatus lookup table
Create uspProcessOrders procedure
Add index for OrderDate queries
Implement row-level security
Fix foreign key constraint

Poor examples:

Changes
Stuff
Update
Fix
Work

Atomic Commits

Each commit should represent a single logical change.

Good:

Commit 1

Add Customer table

Commit 2

Create stored procedure

Commit 3

Add index

Bad:

200 unrelated changes

Atomic commits simplify reviews and rollbacks.

Reviewing Schema Changes

Before merging:

Review:

  • New tables
  • New columns
  • Dropped columns
  • Constraint changes
  • Index additions
  • Foreign keys
  • Permissions
  • Views
  • Stored procedures

Ensure no unintended schema changes exist.

Database Drift

Database drift occurs when production changes bypass source control.

Example:

Developer directly executes:

ALTER TABLE Customers
ADD PhoneNumber VARCHAR(20)

The SQL Database Project remains unchanged.

Consequences:

  • Future deployments may remove the column.
  • Schema becomes inconsistent.
  • Production no longer matches source control.

Best practice:

All changes originate in the SQL Database Project.

Working with Azure DevOps

Azure DevOps integrates SQL Database Projects with:

  • Azure Repos
  • Azure Pipelines
  • Pull Requests
  • Branch Policies
  • Work Items
  • Release Pipelines

Typical flow:

Developer
↓
Azure Repos
↓
Pull Request
↓
Build Pipeline
↓
Validation
↓
Merge
↓
Release Pipeline
↓
Azure SQL Database

Working with GitHub

GitHub supports:

  • Git repositories
  • Pull requests
  • Protected branches
  • GitHub Actions
  • Issue tracking
  • Code review
  • Automated deployments

GitHub Actions can automatically:

  • Build SQL Database Projects
  • Generate DACPAC files
  • Execute validation
  • Deploy to staging
  • Deploy to production after approval

Branch Protection

Production branches should be protected.

Common policies:

  • Pull request required
  • Minimum reviewers
  • Successful build required
  • No direct commits
  • Signed commits (optional)
  • Required status checks

This greatly improves deployment quality.

Handling Secrets

Never commit:

  • SQL passwords
  • Azure keys
  • API keys
  • Tokens
  • Certificates
  • Connection strings with credentials

Instead use:

  • Azure Key Vault
  • GitHub Secrets
  • Azure DevOps Variable Groups
  • Managed Identity

CI/CD Integration

Source control enables continuous integration.

Typical process:

Commit
↓
Build
↓
Validate SQL
↓
Run Tests
↓
Create DACPAC
↓
Publish Artifact
↓
Deploy Dev
↓
Deploy Test
↓
Deploy Production

Source Control Best Practices

Microsoft recommends:

  • Keep database definitions in Git.
  • Use feature branches.
  • Require pull requests.
  • Keep commits small.
  • Review schema changes.
  • Build every commit.
  • Automate deployments.
  • Protect production branches.
  • Never commit secrets.
  • Use SQL Database Projects as the source of truth.
  • Avoid direct production changes.
  • Keep repository structure organized.

DP-800 Exam Tips

Remember these key points:

  • SQL Database Projects integrate naturally with Git.
  • GitHub and Azure DevOps are the primary source control platforms.
  • Use feature branches instead of committing directly to main.
  • Protect production branches with policies.
  • Pull requests enable peer review.
  • Source control prevents database drift.
  • Build validation should occur before merging.
  • Store database definitions—not deployed databases—in source control.
  • Secrets belong in secure secret stores, not Git repositories.
  • CI/CD pipelines should deploy from source-controlled SQL Database Projects.

Practice Exam Questions

Question 1

A development team wants every database schema change to be reviewed before deployment. Which Git feature best supports this requirement?

A. Git tags

B. Pull requests

C. Local branches

D. Git stash

Answer: B

Explanation:
Pull requests enable peer review, discussion, automated validation, and approval before changes are merged into protected branches.

Question 2

Which item should generally NOT be committed to a SQL Database Project repository?

A. Stored procedures

B. Table definitions

C. Build output DACPAC files

D. Database project file

Answer: C

Explanation:
Build artifacts such as generated DACPAC files can be recreated and are typically excluded via .gitignore.

Question 3

A developer needs to implement a new reporting view without affecting ongoing work by teammates. What is the recommended approach?

A. Commit directly to the main branch

B. Modify the production database first

C. Create a feature branch

D. Disable branch protection

Answer: C

Explanation:
Feature branches isolate development work, allowing changes to be tested and reviewed before merging.

Question 4

What is the primary purpose of branch protection rules?

A. Improve query execution speed

B. Encrypt repository contents

C. Automatically resolve merge conflicts

D. Prevent unauthorized or unreviewed changes to critical branches

Answer: D

Explanation:
Branch protection enforces policies such as required reviews and successful builds before changes can be merged.

Question 5

A production database contains schema changes that were made directly using SQL Server Management Studio instead of through the SQL Database Project. This situation is known as:

A. Database drift

B. Schema normalization

C. Dependency injection

D. Continuous deployment

Answer: A

Explanation:
Database drift occurs when deployed databases differ from the schema defined in source control.

Question 6

Why should commits generally be small and focused?

A. They eliminate the need for testing.

B. They increase deployment speed automatically.

C. They simplify reviews, troubleshooting, and rollbacks.

D. They prevent merge conflicts entirely.

Answer: C

Explanation:
Atomic commits make it easier to understand changes, review code, identify issues, and revert individual modifications if necessary.

Question 7

Where should sensitive connection strings and passwords typically be stored?

A. README.md

B. SQL project file

C. Source-controlled configuration file

D. Azure Key Vault or another secure secret store

Answer: D

Explanation:
Secrets should never be committed to source control. Secure secret management services protect sensitive credentials.

Question 8

Which activity is commonly performed automatically by a CI pipeline after code is committed?

A. Manual code review

B. Physical database backup

C. Building the SQL Database Project and validating it

D. Creating user accounts

Answer: C

Explanation:
Continuous Integration pipelines commonly build the project, validate the schema, execute automated tests, and produce deployment artifacts.

Question 9

What is the primary benefit of using pull requests for SQL Database Projects?

A. They provide structured code review before merging changes.

B. They replace source control.

C. They eliminate the need for deployment pipelines.

D. They permanently lock database objects.

Answer: A

Explanation:
Pull requests facilitate collaboration, improve code quality, and ensure that schema changes are reviewed before becoming part of the main codebase.

Question 10

Which statement best describes the role of a SQL Database Project in a DevOps workflow?

A. It stores only database backups.

B. It replaces Git repositories.

C. It serves as the authoritative, source-controlled definition of the database schema.

D. It is used only during production deployment.

Answer: C

Explanation:
In modern database DevOps, the SQL Database Project is the single source of truth for the database schema. Deployment tools compare this project with target databases to generate the required schema changes automatically.

Go to the DP-800 Exam Prep Hub main page

Database Administration, Databases, DP-800, Microsoft Certification, SQL

Create, build, and validate database models by using SQL Database Projects, including SDK-style models – Part 2 (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Create, build, and validate database models by using SQL Database Projects, including SDK-style models

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

In Part 1, you learned about SQL Database Projects, database models, SDK-style projects, build validation, and DACPAC generation. In this section, we’ll examine how developers work with existing databases, manage dependencies, validate projects, deploy changes, and implement modern DevOps practices.

Importing an Existing Database into a SQL Database Project

Many organizations already have production databases before adopting Database-as-Code practices. Rather than starting from scratch, developers can import an existing schema into a SQL Database Project.

The import process typically:

  1. Connects to an existing SQL Server or Azure SQL Database.
  2. Reads the database schema.
  3. Extracts supported objects.
  4. Creates corresponding .sql files.
  5. Generates the SQL Database Project.

Objects that are typically imported include:

  • Tables
  • Views
  • Stored procedures
  • Functions
  • User-defined data types
  • Schemas
  • Security objects
  • Synonyms
  • Sequences

Data itself is not imported into the project.

Reverse Engineering a Database

Importing is often called reverse engineering because the project is generated from an existing database rather than the database being created from source code.

Example workflow:

Production Database
        │
        ▼
Extract Schema
        │
        ▼
Generate SQL Project
        │
        ▼
Commit to Git
        │
        ▼
Future Changes Through Source Control

This allows teams to transition from manual database administration to modern DevOps practices.

Source Control Integration

One of the biggest advantages of SQL Database Projects is seamless integration with Git.

A repository may contain:

DatabaseProject/
│
├── Tables/
├── Views/
├── Procedures/
├── Security/
├── Scripts/
├── Database.sqlproj
└── README.md

Each change becomes a Git commit, providing:

  • Version history
  • Code reviews
  • Branching
  • Pull requests
  • Rollback capabilities
  • Team collaboration

Branching Strategies

Common Git workflows include:

Feature Branches

Each developer works in an isolated branch.

Main
├── Feature-A
├── Feature-B
└── Feature-C

Changes are merged only after review and successful validation.

Release Branches

Organizations often create release branches for production deployments.

Example:

Main
↓
Release 1.0
↓
Production

This ensures stable production releases.

Database References

Large enterprise systems often contain multiple databases.

Examples include:

  • Sales
  • Inventory
  • Human Resources
  • Finance

Applications frequently reference objects across databases.

SQL Database Projects support database references to resolve these dependencies during the build process.

Example of a Cross-Database Reference

Suppose a stored procedure references another database:

SELECT *
FROM Inventory.dbo.Products;

Without a database reference, the build reports an unresolved reference.

Adding a database reference informs the build engine where the referenced objects reside.

Project References

A SQL Database Project can reference another SQL Database Project.

Example:

SalesDatabase
↓
References
↓
SharedDatabase

This allows developers to:

  • Reuse shared schemas
  • Validate dependencies
  • Build multiple databases together

Schema Compare

Schema Compare is one of the most valuable tools in SQL Database Projects.

It compares:

  • Project vs Database
  • Database vs Database
  • Project vs DACPAC
  • DACPAC vs Database

The comparison identifies differences before deployment.

Schema Compare Example

Suppose the project contains:

CustomerName NVARCHAR(200)

Production contains:

CustomerName NVARCHAR(100)

Schema Compare highlights the difference before deployment.

Why Schema Compare Matters

Schema Compare helps prevent:

  • Missing objects
  • Accidental deletions
  • Unexpected schema drift
  • Incorrect deployments
  • Manual mistakes

It also generates deployment scripts automatically.

Schema Drift

Schema drift occurs when changes are made directly to a production database instead of through the SQL Database Project.

Example:

Project:

Employee
Salary

Production:

Employee
Salary
Bonus

The project is now out of sync.

Schema Compare identifies this difference.

Build Process

Building a SQL Database Project performs several validation steps:

  1. Parse SQL files
  2. Validate syntax
  3. Resolve dependencies
  4. Build the database model
  5. Detect conflicts
  6. Generate the DACPAC

Only after these steps succeed is the project considered buildable.

Common Build Errors

Examples include:

Missing Table

SELECT *
FROM Orders;

If the Orders table does not exist, the build fails.

Invalid Column

SELECT CustomerAge
FROM Customers;

If CustomerAge is absent, validation reports an error.

Duplicate Object

Two files define:

CREATE TABLE Customers

The project cannot determine which definition is correct, so the build fails.

Circular Dependency

View A depends on View B.

View B depends on View A.

This circular dependency prevents successful validation.

Build Warnings vs Build Errors

WarningError
Build succeedsBuild fails
Potential issueMust be fixed
Deployment possibleDeployment blocked
Review recommendedImmediate action required

Developers should investigate warnings even if the build succeeds.

Pre-Deployment Scripts

Pre-deployment scripts execute before schema deployment.

Typical uses include:

  • Backups
  • Temporary objects
  • Data preparation
  • Environment validation
  • Configuration checks

Example:

PRINT 'Preparing deployment';

Post-Deployment Scripts

Post-deployment scripts execute after schema deployment.

Typical tasks include:

  • Insert lookup data
  • Populate configuration tables
  • Create default users
  • Update permissions
  • Seed application settings

Example:

INSERT INTO Status
VALUES ('Active');

SQLPackage

SQLPackage is Microsoft’s command-line utility for SQL Database Projects.

It can:

  • Build projects
  • Publish DACPACs
  • Extract schemas
  • Generate deployment scripts
  • Compare schemas
  • Export DACPACs

SQLPackage is widely used in automated deployment pipelines.

Common SQLPackage Operations

Developers commonly use SQLPackage to:

  • Publish a DACPAC to Azure SQL Database.
  • Extract a DACPAC from an existing database.
  • Generate deployment scripts without applying them.
  • Compare source and target schemas.

This enables repeatable, automated deployments.

Continuous Integration (CI)

A CI pipeline typically performs:

Git Commit
↓
Restore
↓
Build SQL Project
↓
Validate Model
↓
Run Tests
↓
Generate DACPAC
↓
Publish Build Artifact

Every commit is validated automatically.

Continuous Delivery (CD)

The CD pipeline deploys validated artifacts.

Typical workflow:

DACPAC
↓
Development
↓
Testing
↓
Staging
↓
Production

Promotion between environments follows organizational approval policies.

Deployment Validation

Before deployment, the deployment engine evaluates:

  • Schema differences
  • Data loss risks
  • Object dependencies
  • Permission changes
  • Unsupported operations

Potentially destructive changes, such as dropping a populated table, are flagged for review.

Environment-Specific Configuration

Projects should avoid hard-coding environment-specific settings.

Instead, deployment profiles or pipeline variables should define values such as:

  • Server name
  • Database name
  • Authentication method
  • Connection strings
  • Environment-specific options

This supports consistent deployments across development, test, and production.

SDK-Style Project Best Practices

Microsoft recommends the following practices:

  • Store every schema object in its own file.
  • Use meaningful folder structures.
  • Commit all schema changes to source control.
  • Build frequently.
  • Resolve warnings before deployment.
  • Validate pull requests automatically.
  • Use deployment profiles for different environments.
  • Automate builds with CI/CD pipelines.
  • Minimize manual production changes.
  • Keep database references current.

Common DP-800 Exam Scenarios

Scenario 1

A developer changes a table directly in production.

Question: What problem has occurred?

Answer: Schema drift.

Scenario 2

A project builds successfully but deployment has not occurred.

Question: What artifact was created?

Answer: A DACPAC.

Scenario 3

A stored procedure references another database and validation fails.

Question: What should be added?

Answer: A database reference (or project reference, where appropriate).

Scenario 4

A team wants every schema change reviewed before deployment.

Recommended approach:

  • Git repository
  • Pull requests
  • SQL Database Projects
  • Automated build validation
  • DACPAC deployment

DP-800 Exam Tips

  • Understand the difference between project references and database references.
  • Know how Schema Compare identifies schema drift and deployment differences.
  • Recognize when to use pre-deployment versus post-deployment scripts.
  • Be familiar with SQLPackage as the primary command-line deployment tool.
  • Understand that CI pipelines build, validate, and generate DACPACs, while CD pipelines deploy those validated artifacts.
  • Remember that schema validation occurs before deployment, helping detect unresolved references, duplicate objects, and dependency issues.

Key Takeaways

  • Existing databases can be reverse engineered into SQL Database Projects.
  • Source control enables collaboration, auditing, and rollback.
  • Database and project references resolve dependencies across databases.
  • Schema Compare identifies schema differences and drift.
  • SQLPackage automates building, extracting, comparing, and deploying database projects.
  • CI/CD pipelines automate validation and deployment.
  • Pre-deployment and post-deployment scripts help manage operational tasks during deployment.
  • SDK-style projects reduce maintenance while supporting modern DevOps workflows.

Practice Exam Questions

Question 1

A development team wants to ensure that all database schema changes are version controlled, reviewed through pull requests, and automatically validated before deployment.

Which approach should they implement?

A. Store the database schema in a SQL Database Project managed in Git and use CI/CD pipelines.

B. Allow developers to make schema changes directly in production and back up the database daily.

C. Export a database backup after every schema change.

D. Maintain documentation of schema changes in a shared spreadsheet.

Correct Answer: A

Explanation

SQL Database Projects support Database-as-Code practices by storing database objects in source control. Combined with Git and CI/CD pipelines, schema changes can be reviewed, validated, tested, and deployed consistently. The other options lack automation, version control, and build validation.

Question 2

What is the primary output generated when a SQL Database Project is successfully built?

A. A transaction log

B. A DACPAC

C. A backup (.bak) file

D. A SQL trace file

Correct Answer: B

Explanation

A successful build generates a DACPAC (Data-tier Application Package) that contains the compiled database model. It serves as the deployment artifact for publishing schema changes. A backup file and transaction log contain database data, not compiled schema definitions.

Question 3

A stored procedure references a table in another database. During the build process, an unresolved reference error occurs.

What should you configure?

A. A post-deployment script

B. A schema comparison

C. A database reference

D. Query Store

Correct Answer: C

Explanation

Database references inform the build engine about objects located in external databases, allowing dependency validation during compilation. Without the reference, the build engine cannot resolve cross-database object names.

Question 4

Which statement accurately describes an SDK-style SQL Database Project?

A. It requires every SQL file to be manually added to the project file.

B. It supports only Azure SQL Database.

C. It cannot be used with Git.

D. It automatically discovers SQL files and uses a simplified project format.

Correct Answer: D

Explanation

SDK-style projects simplify project configuration by automatically discovering SQL files and using a modern SDK-based project structure. This reduces maintenance, improves Git compatibility, and supports cross-platform development.

Question 5

During a build, a view references a table that no longer exists.

What is the expected outcome?

A. The build reports a validation error.

B. The DACPAC is generated without warnings.

C. The table is automatically recreated.

D. The deployment succeeds and fixes the dependency.

Correct Answer: A

Explanation

The build engine validates object dependencies while constructing the database model. Missing referenced objects generate validation errors that prevent a successful build until the dependency is resolved.

Question 6

Your team notices that a production database contains several tables that are not present in the SQL Database Project because administrators modified production directly.

What situation does this describe?

A. Database normalization

B. Incremental deployment

C. Schema drift

D. Model optimization

Correct Answer: C

Explanation

Schema drift occurs whenever changes are made outside the controlled development process, causing production and source control to diverge. Schema Compare is commonly used to detect these differences.

Question 7

Which tool is specifically designed to compare differences between a SQL Database Project and a target database before deployment?

A. Query Store

B. SQL Profiler

C. SQL Server Agent

D. Schema Compare

Correct Answer: D

Explanation

Schema Compare analyzes differences between schemas stored in projects, DACPACs, and databases. It helps identify schema drift and generates deployment scripts before changes are applied.

Question 8

Why is compile-time validation an important feature of SQL Database Projects?

A. It encrypts the deployed database automatically.

B. It detects schema and dependency problems before deployment.

C. It improves query execution speed.

D. It compresses database backups.

Correct Answer: B

Explanation

Compile-time validation identifies syntax errors, unresolved references, duplicate objects, and dependency problems before deployment, reducing production failures and improving deployment reliability.

Question 9

Which activity is most appropriate for a post-deployment script?

A. Building the DACPAC

B. Validating SQL syntax

C. Inserting lookup or reference data after schema deployment

D. Resolving project references

Correct Answer: C

Explanation

Post-deployment scripts execute after schema changes have been applied. Common tasks include inserting lookup data, populating configuration tables, creating default records, and updating permissions.

Question 10

Which statement best describes the relationship between Continuous Integration (CI) and SQL Database Projects?

A. CI replaces the need for SQL Database Projects.

B. CI automatically converts databases into NoSQL databases.

C. CI performs backups before every deployment.

D. CI automatically builds, validates, and produces deployment artifacts whenever changes are committed.

Correct Answer: D

Explanation

Continuous Integration automates the process of building SQL Database Projects, validating database models, detecting errors, and generating DACPAC deployment artifacts whenever developers commit changes. This enables early detection of issues and supports reliable, repeatable deployments.

Exam Tips

  • Know the difference between a SQL Database Project, a database model, and a DACPAC.
  • Remember that SDK-style projects automatically discover SQL files and simplify project maintenance.
  • Understand the purpose of database references and project references.
  • Be able to identify scenarios involving schema drift and understand how Schema Compare addresses them.
  • Know the difference between pre-deployment and post-deployment scripts.
  • Understand how SQLPackage, CI/CD pipelines, and Git work together to automate database deployments.
  • Expect scenario-based questions that ask you to choose the appropriate development or deployment strategy for a given situation.

Go to the DP-800 Exam Prep Hub main page

Database Administration, Databases, DP-800, Microsoft Certification, SQL

Create, build, and validate database models by using SQL Database Projects, including SDK-style models – Part 1 (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Create, build, and validate database models by using SQL Database Projects, including SDK-style models

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

For the exam, you should understand how to:

  • Create SQL Database Projects
  • Build database models
  • Validate database schemas before deployment
  • Use SDK-style SQL projects
  • Work with DACPACs
  • Manage project references
  • Integrate SQL Database Projects into DevOps pipelines
  • Detect schema problems before deployment
  • Support collaborative database development

This topic is one of the most important DevOps-related objectives on the DP-800 exam because Microsoft encourages Database-as-Code (DbC) practices.

What Is a SQL Database Project?

A SQL Database Project is a source-controlled representation of a SQL Server or Azure SQL database.

Instead of editing objects directly inside the database, developers edit project files that describe every object.

The project can then be:

  • Built
  • Validated
  • Version controlled
  • Tested
  • Published

Think of it as treating a database exactly like application code.

Instead of storing the database only on a SQL Server instance, the schema becomes part of the application’s source code repository.

Traditional Database Development vs SQL Database Projects

Traditional DevelopmentSQL Database Projects
Direct changes in SSMSChanges made in project files
Difficult to track historyFull Git history
Manual deploymentsAutomated deployments
Hard to validateBuild-time validation
Production-first changesDevelopment-first workflow
Error detection during deploymentError detection during build

Database-as-Code (DbC)

SQL Database Projects implement the Database-as-Code methodology.

Database objects become code files that can be:

  • reviewed
  • versioned
  • tested
  • validated
  • automatically deployed

Just like C# or Java projects.

Benefits include:

  • Consistent deployments
  • Easier collaboration
  • Rollback capability
  • Repeatable deployments
  • Reduced production errors
  • CI/CD integration

Components of a SQL Database Project

A project typically contains:

DatabaseProject
│
├── Tables
│   ├── Customers.sql
│   ├── Orders.sql
│
├── Views
│   ├── SalesSummary.sql
│
├── Stored Procedures
│   ├── usp_InsertOrder.sql
│
├── Functions
│
├── Security
│
├── Users
│
├── Roles
│
├── Schemas
│
├── Scripts
│
├── PostDeployment.sql
│
├── PreDeployment.sql
│
└── Database.sqlproj

Every object is stored as an individual SQL file.

What Is a Database Model?

A database model is the complete representation of every database object contained within a SQL Database Project.

It includes:

  • Tables
  • Columns
  • Primary keys
  • Foreign keys
  • Constraints
  • Views
  • Stored procedures
  • Functions
  • Triggers
  • Users
  • Roles
  • Schemas
  • Permissions

The model exists independently of any live database.

Microsoft builds this model during compilation.

Why Build a Database Model?

Building the model allows SQL Server Data Tools (SSDT) or SQL Database Projects to verify:

  • Object existence
  • Dependency correctness
  • Syntax correctness
  • Invalid references
  • Circular dependencies
  • Duplicate objects
  • Naming conflicts

before deployment.

SQL Database Projects vs DACPAC

These two concepts are closely related but not identical.

SQL Database Project

Contains:

  • Source files
  • SQL scripts
  • Project configuration
  • Build settings

Editable by developers.

DACPAC

A Data-tier Application Package (DACPAC) is the compiled output generated from the project.

Think of it like:

C# Source Code
↓
DLL

Similarly,

SQL Project
↓
DACPAC

The DACPAC contains:

  • Database model
  • Schema metadata
  • Deployment information

It does not contain user data.

Development Workflow

A typical workflow looks like this:

Developer
↓
Modify SQL files
↓
Build project
↓
Validate model
↓
Generate DACPAC
↓
Source Control
↓
CI Pipeline
↓
Testing
↓
Deployment
↓
Production

This workflow ensures every schema change is validated before deployment.

Creating a SQL Database Project

Common methods include:

  • Visual Studio
  • Azure Data Studio (with SQL Database Projects extension)
  • Visual Studio Code (SQL Database Projects extension)
  • .NET CLI (SDK-style projects)

Typical steps:

  1. Create project
  2. Choose SQL Server platform
  3. Add database objects
  4. Build project
  5. Resolve validation errors
  6. Generate DACPAC
  7. Deploy

SQL Server Data Tools (SSDT)

Historically, SSDT was the primary development environment.

It provides:

  • IntelliSense
  • Schema Compare
  • Build validation
  • Refactoring
  • Deployment
  • Publish wizard

Modern SQL Database Projects also support lightweight editors like Visual Studio Code.

SDK-Style SQL Database Projects

The newer SDK-style format modernizes SQL project development.

Benefits include:

  • Simpler project files
  • Cross-platform support
  • .NET SDK integration
  • Better Git compatibility
  • Easier automation
  • Better Azure DevOps integration
  • Improved command-line support

Microsoft is increasingly encouraging SDK-style projects over older project formats.

Traditional Project Format

Older projects contain verbose XML.

Example:

<Project DefaultTargets="Build">
<ItemGroup>
<Build Include="Tables\Customer.sql"/>
<Build Include="Views\Sales.sql"/>
</ItemGroup>
</Project>

As projects grow, these files become difficult to maintain.

SDK-Style Project Format

SDK-style projects are dramatically simpler.

Example:

<Project Sdk="Microsoft.Build.Sql">
<PropertyGroup>
<TargetFramework>net8.0</TargetFramework>
<SqlServerVersion>Sql160</SqlServerVersion>
</PropertyGroup>
</Project>

Files are automatically discovered.

Developers no longer have to manually list every SQL object.

Advantages of SDK-Style Projects

Compared to legacy projects:

TraditionalSDK-Style
Large XMLMinimal XML
Manual file inclusionAutomatic discovery
Windows-focusedCross-platform
Older MSBuildModern SDK
More maintenanceLess maintenance
Limited CLI supportExcellent CLI support

Automatic File Discovery

One major benefit is automatic inclusion.

Suppose a developer creates:

Tables
Products.sql

The project automatically includes it.

No project modification is required.

This greatly reduces merge conflicts in Git.

Platform Targets

Projects target a SQL platform.

Examples include:

  • SQL Server 2019
  • SQL Server 2022
  • Azure SQL Database
  • Azure SQL Managed Instance

The selected platform determines which SQL features are valid.

For example:

A feature available in SQL Server 2022 but not Azure SQL Database may produce a build warning or error if the wrong target platform is selected.

Schema Validation

During the build, SQL Database Projects perform extensive validation.

Checks include:

  • Missing tables
  • Missing columns
  • Invalid views
  • Invalid stored procedures
  • Invalid foreign keys
  • Duplicate objects
  • Broken references
  • Unsupported features
  • Syntax errors

This allows developers to catch issues long before deployment.

Dependency Analysis

The build engine understands dependencies.

For example:

View
↓
Table
↓
Schema

If a table is renamed without updating dependent objects, the build detects the issue.

Object Dependency Example

Consider:

CREATE VIEW SalesSummary
AS
SELECT *
FROM Sales;

If the Sales table is removed, the build process reports an error because the view references a nonexistent object.

Compile-Time Validation vs Runtime Validation

Compile-TimeRuntime
During buildDuring execution
Finds schema errors earlyErrors appear after deployment
Faster troubleshootingProduction outages possible
Safer deploymentsHigher operational risk

Compile-time validation is one of the biggest advantages of SQL Database Projects.

Common DP-800 Exam Tips

  • Understand the distinction between a SQL Database Project and a DACPAC.
  • Know that SQL Database Projects implement Database-as-Code practices.
  • Recognize that SDK-style projects simplify project maintenance through automatic file discovery and modern MSBuild integration.
  • Remember that the database model is built and validated before deployment, helping identify schema issues early.
  • Be familiar with how build validation detects missing objects, dependency problems, and syntax errors before changes reach production.
  • Know that SQL Database Projects integrate naturally with Git, Azure DevOps, and GitHub workflows for CI/CD.

Key Takeaways

  • SQL Database Projects represent database schemas as source code.
  • Database models are compiled representations of all database objects.
  • Building a project validates the model before deployment.
  • DACPACs are compiled deployment artifacts generated from SQL Database Projects.
  • SDK-style projects simplify configuration, support cross-platform development, and improve automation.
  • Automatic file discovery reduces project maintenance and Git merge conflicts.
  • Compile-time validation helps prevent deployment failures by identifying schema and dependency issues early.

Go to the DP-800 Exam Prep Hub main page