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

Leave a comment