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

Leave a comment