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

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

Leave a comment