Category: Database Administration

AI, Data Development, Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Exam Prep Hub for DP-800: Developing AI-Enabled Database Solutions

Welcome to the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub!

Welcome to the one-stop hub with information for preparing for the DP-800: Developing AI-Enabled Database Solutions certification exam. The content for this exam helps prepare you to have “subject matter expertise in designing and developing AI-enabled database solutions across Microsoft SQL platforms, including Microsoft SQL Server, Azure SQL, and SQL databases in Microsoft Fabric”.
Upon successful completion of the exam, you earn the Microsoft Certified: SQL AI Developer Associate certification.

This hub provides information directly here (topic-by-topic as outlined in the official study guide), links to a number of external resources, tips for preparing for the exam, practice tests, and section questions to help you prepare. Bookmark this page and use it as a guide to ensure that you are fully covering all relevant topics for the DP-800 exam and making use of as many of the resources available as possible.

Audience profile (from Microsoft’s site)

As a candidate for this Microsoft Certification, you should have subject matter expertise in designing and developing AI-enabled database solutions across Microsoft SQL platforms, including Microsoft SQL Server, Azure SQL, and SQL databases in Microsoft Fabric.

You should also have experience writing T-SQL code and developing databases in Microsoft SQL platforms. Plus, you need to be familiar with continuous integration and continuous deployment (CI/CD) practices in GitHub, AI-assisted development tools, and AI concepts, such as embeddings, vectors, and models.

Your responsibilities include:
- Designing and developing database solutions that include both structured and semi-structured data.
- Integrating AI features into modern and highly scalable enterprise applications.
- Securing, optimizing, and deploying database solutions.
- Implementing AI capabilities in database solutions.

You work closely with application developers; database administrators (DBAs); architects; AI engineers; development, security, operations (DevSecOps) engineers; security and compliance administrators; and other stakeholders to deliver robust, high-performance database solutions that power modern applications and AI-driven experiences.

Skills at a glance (as specified in the official study guide)

Design and develop database solutions (35–40%)
Secure, optimize, and deploy database solutions (35–40%)
Implement AI capabilities in database solutions (25–30%)

Topic-by-Topic Exam Content

[click a topic link to access the content and practice questions for that topic]

Design and develop database solutions (35–40%)

Design and implement database objects

Implement programmability objects

Write advanced T-SQL code

Design and implement SQL solutions by using AI-assisted tools

Interpret security impact of using AI-assisted tools
Enable GitHub Copilot and Microsoft Copilot in Fabric
Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Copilot in Fabric chat session
Create and configure GitHub Copilot instruction files
Connect to MCP server endpoints, including Microsoft SQL Server and Fabric lakehouse

Secure, optimize, and deploy database solutions (35–40%)

Implement data security and compliance

Optimize database performance

Implement CI/CD by using SQL Database Projects

Design and implement a testing strategy, including unit tests and integration tests
Create and manage reference/static data in source control
Create, build, and validate database models by using SQL Database Projects, including SDK-style models
- Create, build, and validate database models by using SQL Database Projects, including SDK-style models – Part 1
- Create, build, and validate database models by using SQL Database Projects, including SDK-style models – Part 2
Configure source control for SQL Database Projects
Manage branching, pull requests, and conflict resolution
Implement secrets management
Detect schema drift by using SQL Database Projects
Update a SQL database project and deploy changes
Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners

Integrate SQL solutions with Azure services

Implement AI capabilities in database solutions (25–30%)

Design and implement models and embeddings

Evaluate external models, including multimodal, multilanguage, sizes, and structured output
Create and manage external models
Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry
- Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry – Part 1
- Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry – Part 2
Identify which columns to include in embeddings
Design and implement chunks for embeddings
Generate embeddings

Design and implement intelligent search

Design and implement retrieval-augmented generation (RAG)

DP-800 Practice Exams

Important DP-800 Resources

Link to the free, comprehensive, self-paced course on Microsoft Learn:
Course: Develop AI-enabled database solutions

Course DP-800T00-A: Develop AI-enabled database solutions – Training | Microsoft Learn

This course has 3 learning paths. The 3 learning paths and their modules are listed with links below:

(1) Design and develop database solutions

This learning path has 4 modules:
(i) Design and implement database objects with SQL
(ii) Implement programmability objects with SQL
(iii) Write advanced T-SQL code
(iv) Implement SQL solutions by using AI-assisted tools

(2) Secure, optimize, and deploy database solutions

This learning path has 4 modules:
(i) Implement data security and compliance with SQL
(ii) Optimize database performance
(iii) Implement CI/CD by using SQL Database Projects
(iv) Integrate SQL solutions with Azure services

(3) Implement AI capabilities in database solutions

This learning path has 3 modules:
(i) Design and implement models and embeddings with SQL
(ii) Design and implement intelligent search with SQL
(iii) Design and implement RAG with SQL

Courses:

These are two highly rated courses for DP-800 on Udemy:

Good luck to you passing the DP-800 Exam!
However, the more preparation you have, the less luck you will need. 🙂

Visit this post to see the list of all the certification preparation hubs available on The Data Community.

Database Administration, Databases, DP-800, Microsoft Certification August 1, 2026

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!"
}

		

			
CREATE LOGIN appuser
WITH PASSWORD='MyPassword!';

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:

Environment	Database
Development	Dev SQL
Test	Test SQL
Production	Production 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

Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

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:

Connects to an existing SQL Server or Azure SQL Database.
Reads the database schema.
Extracts supported objects.
Creates corresponding .sql files.
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:

Parse SQL files
Validate syntax
Resolve dependencies
Build the database model
Detect conflicts
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

Warning	Error
Build succeeds	Build fails
Potential issue	Must be fixed
Deployment possible	Deployment blocked
Review recommended	Immediate 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 August 1, 2026

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 Development	SQL Database Projects
Direct changes in SSMS	Changes made in project files
Difficult to track history	Full Git history
Manual deployments	Automated deployments
Hard to validate	Build-time validation
Production-first changes	Development-first workflow
Error detection during deployment	Error 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:

Create project
Choose SQL Server platform
Add database objects
Build project
Resolve validation errors
Generate DACPAC
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:

Traditional	SDK-Style
Large XML	Minimal XML
Manual file inclusion	Automatic discovery
Windows-focused	Cross-platform
Older MSBuild	Modern SDK
More maintenance	Less maintenance
Limited CLI support	Excellent 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-Time	Runtime
During build	During execution
Finds schema errors early	Errors appear after deployment
Faster troubleshooting	Production outages possible
Safer deployments	Higher 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

Database Administration, Databases, DP-800, Microsoft Certification August 1, 2026

Recommend database configurations (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%)
   --> Optimize database performance
      --> Recommend database configurations

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

Proper database configuration is one of the most effective ways to achieve high performance, scalability, availability, and cost efficiency. Even well-designed databases and optimized queries can perform poorly if the underlying database configuration is not appropriate for the workload.

The DP-800: Developing AI-Enabled Database Solutions exam expects candidates to understand how to recommend database configurations for SQL Server, Azure SQL Database, Azure SQL Managed Instance, Microsoft Fabric SQL Database, and other SQL-based data platforms. Rather than simply changing code, developers should be able to identify when performance issues can be addressed through configuration changes involving compute resources, storage, memory, indexing strategies, concurrency, automatic tuning, and database compatibility settings.

A well-configured database should balance:

Performance
Scalability
Security
High availability
Cost
Maintainability

Why Database Configuration Matters

Database configuration directly affects:

Query execution speed
Transaction throughput
Concurrent user capacity
AI workload responsiveness
Resource utilization
Operational costs
System reliability

Poor configurations can result in:

Long-running queries
Excessive locking
Deadlocks
High CPU utilization
Memory pressure
Storage bottlenecks
Increased cloud costs

Understand the Workload

Before recommending a configuration, identify the workload characteristics.

Questions include:

Is the workload transactional (OLTP)?
Is it analytical (OLAP)?
Is it mixed?
Is it AI-enabled?
How many concurrent users exist?
What is the expected database size?
Is low latency required?
Are workloads predictable or bursty?

Understanding the workload guides all subsequent configuration decisions.

Choose the Appropriate SQL Platform

Microsoft offers several SQL deployment options.

SQL Server

Best for:

On-premises deployments
Complete administrative control
Highly customized environments

Developer considerations:

Hardware sizing
Memory configuration
Storage layout
Backup strategy

Azure SQL Database

Best for:

Cloud-native applications
Fully managed environments
Elastic scaling
Minimal administration

Features include:

Automatic tuning
Automatic backups
Built-in high availability
Automatic patching

Azure SQL Managed Instance

Best for:

Existing SQL Server applications
High compatibility
Managed platform
Near full SQL Server feature support

Microsoft Fabric SQL Database

Best for:

Analytics
AI-enabled workloads
Integrated Microsoft Fabric solutions
Modern cloud-native architectures

Compute Configuration

Choosing the proper compute tier significantly affects performance.

Azure SQL offers multiple purchasing models.

DTU Model

Combines:

CPU
Memory
Storage I/O

into a single performance unit.

Advantages:

Simple sizing
Easier cost estimation

Disadvantages:

Less granular control

vCore Model

Separates:

CPU
Memory
Storage

Advantages:

More flexibility
Better workload tuning
Easier migration from SQL Server

The DP-800 exam generally emphasizes the vCore model because it provides greater control over resource allocation.

Service Tiers

Azure SQL Database supports multiple service tiers.

General Purpose

Suitable for:

Typical business applications
Moderate workloads
Cost-sensitive deployments

Business Critical

Provides:

Low latency
Faster storage
Multiple replicas
High availability

Ideal for:

Mission-critical applications
High transaction workloads

Hyperscale

Designed for:

Very large databases
Rapid storage growth
Read scale-out
High-performance cloud workloads

Serverless vs. Provisioned Compute

Serverless

Advantages:

Auto-scaling
Auto-pausing
Cost savings
Ideal for intermittent workloads

Suitable for:

Development environments
Departmental applications
Variable workloads

Provisioned

Advantages:

Predictable performance
Always available
Consistent response times

Suitable for:

Production systems
High-volume applications
Mission-critical workloads

Storage Configuration

Storage performance greatly affects database responsiveness.

Recommendations include:

Premium SSD storage
Sufficient IOPS
Low latency
Adequate capacity planning

Avoid running databases near storage limits.

TempDB Configuration (SQL Server)

TempDB supports:

Temporary tables
Sort operations
Hash joins
Version store
Snapshot isolation

Best practices include:

Multiple TempDB data files
Equal file sizes
Fast storage
Proper autogrowth settings

Although Azure SQL manages TempDB automatically, understanding these concepts remains valuable.

Database Compatibility Level

SQL Server compatibility levels determine optimizer behavior and available features.

Newer compatibility levels provide:

Improved query optimization
New T-SQL features
Better cardinality estimation
Performance enhancements

However, compatibility changes should be tested because query plans may change.

Automatic Tuning

Azure SQL Database supports automatic tuning features.

These include:

CREATE INDEX
DROP INDEX
FORCE LAST GOOD PLAN

Benefits include:

Improved query performance
Reduced manual administration
Automatic regression correction

Developers should understand when automatic tuning is appropriate and how to monitor its recommendations.

Intelligent Query Processing

Recent SQL Server versions include Intelligent Query Processing (IQP).

Features include:

Memory Grant Feedback
Batch Mode on Rowstore
Scalar UDF Inlining
Table Variable Deferred Compilation
Parameter Sensitive Plan Optimization

These features improve query performance without requiring application changes.

Configure Appropriate Indexes

Configuration recommendations often involve indexing.

Common index types include:

Clustered indexes
Nonclustered indexes
Filtered indexes
Columnstore indexes
XML indexes
Spatial indexes
Full-text indexes

Recommendations depend on workload characteristics.

For example:

OLTP systems benefit primarily from clustered and nonclustered indexes, while analytical workloads often benefit from columnstore indexes.

Partition Large Tables

Partitioning improves manageability and can improve query performance when queries access only specific partitions.

Benefits include:

Faster maintenance
Improved archiving
Reduced I/O
Partition elimination

Partitioning is especially useful for:

Sales history
Audit logs
Time-series data
IoT data

Optimize Concurrency

Database configuration affects concurrent users.

Recommendations include:

Appropriate transaction isolation levels
Snapshot Isolation
Read Committed Snapshot Isolation (RCSI)
Short transactions
Efficient indexing

Reducing blocking improves application scalability.

Configure Memory Usage

Memory influences:

Buffer cache
Query execution
Sort operations
Hash joins
Plan cache

For SQL Server:

Configure:

Maximum Server Memory
Minimum Server Memory

Avoid allowing SQL Server to consume all available system memory.

Azure SQL manages memory automatically.

Configure Database Files

Best practices include:

Multiple data files for very large databases
Appropriate autogrowth settings
Fixed-size growth increments
Avoid very small autogrowth values
Separate data and log files (SQL Server)

Poor autogrowth settings can increase fragmentation.

Statistics Configuration

Query optimization depends heavily on statistics.

Recommendations include:

Enable AUTO_CREATE_STATISTICS
Enable AUTO_UPDATE_STATISTICS
Update statistics after major data changes

Outdated statistics frequently result in poor execution plans.

High Availability Configuration

Configuration should match business requirements.

Options include:

Always On Availability Groups
Azure SQL built-in HA
Geo-replication
Auto-failover groups
Read replicas

Choose configurations based on:

Recovery Time Objective (RTO)
Recovery Point Objective (RPO)

AI Workload Considerations

AI-enabled applications often perform:

Vector searches
Embedding generation
Semantic search
Retrieval-Augmented Generation (RAG)
JSON processing

Recommendations include:

Sufficient memory
Fast storage
Columnstore indexes for analytics
Azure AI Search integration
Read replicas for heavy query workloads

Monitor Before Recommending Changes

Performance recommendations should be evidence-based.

Useful monitoring tools include:

Query Store
Execution Plans
Azure Monitor
SQL Insights
Dynamic Management Views (DMVs)
Performance Dashboard
Extended Events
Intelligent Insights (Azure SQL)

Common Configuration Mistakes

Avoid:

Choosing Business Critical for low-volume applications
Underprovisioning CPU
Ignoring storage latency
Disabling automatic statistics
Excessive indexing
Using outdated compatibility levels without testing
Poor TempDB configuration
Unlimited autogrowth
Ignoring Query Store recommendations
Not monitoring workload trends

Best Practices

Size resources based on workload characteristics.
Prefer the vCore purchasing model when granular control is needed.
Enable automatic tuning where appropriate.
Monitor Query Store regularly.
Keep statistics current.
Configure indexes based on workload patterns.
Test compatibility level changes before production deployment.
Use Business Critical only when required.
Consider serverless compute for intermittent workloads.
Use Hyperscale for very large databases.
Continuously monitor performance and adjust configurations.

DP-800 Exam Tips

Remember these key points for the exam:

Understand when to recommend General Purpose, Business Critical, or Hyperscale service tiers.
Know the differences between DTU and vCore purchasing models.
Understand when serverless compute is appropriate.
Automatic tuning can create indexes, remove unused indexes, and correct query regressions.
Query Store is one of the primary tools for identifying performance problems.
Statistics and indexes are fundamental to query optimization.
Compatibility level influences the query optimizer and available SQL features.
Database recommendations should always be based on observed workload characteristics and performance metrics.

Practice Exam Questions

Question 1

A database experiences unpredictable traffic during business hours but is often idle overnight. Which Azure SQL compute option is likely to provide the best balance between performance and cost?

A. Business Critical with maximum vCores

B. Hyperscale

C. Serverless compute

D. Dedicated SQL Server on a virtual machine

Answer: C

Explanation: Serverless compute automatically scales resources and can pause during periods of inactivity, reducing costs while still supporting variable workloads.

Question 2

A company requires extremely low latency and high availability for a mission-critical online transaction processing (OLTP) application. Which Azure SQL service tier should be recommended?

A. General Purpose

B. Business Critical

C. Basic

D. Serverless

Answer: B

Explanation: Business Critical uses local SSD storage, multiple replicas, and built-in high availability, making it ideal for latency-sensitive, mission-critical workloads.

Question 3

Which Azure SQL purchasing model provides independent control over CPU, memory, and storage resources?

A. DTU

B. Elastic Pool

C. vCore

D. Consumption

Answer: C

Explanation: The vCore model allows independent configuration of compute and storage resources, making it suitable for workload-specific optimization.

Question 4

Which SQL Server feature automatically recommends creating or dropping indexes and can force the last known good execution plan?

A. SQL Server Agent

B. Query Notifications

C. Extended Events

D. Automatic Tuning

Answer: D

Explanation: Automatic Tuning can recommend and apply index changes and automatically correct certain query regressions by forcing a previously successful execution plan.

Question 5

A developer notices that query execution plans are using outdated data distribution estimates after a large data import. Which recommendation is most appropriate?

A. Disable Query Store

B. Shrink the database

C. Update database statistics

D. Reduce TempDB size

Answer: C

Explanation: Accurate statistics help the query optimizer estimate row counts correctly and generate efficient execution plans.

Question 6

Which feature should be reviewed first when investigating consistently slow queries in Azure SQL Database?

A. SQL Server Configuration Manager

B. Query Store

C. Windows Event Viewer

D. Azure Key Vault

Answer: B

Explanation: Query Store captures execution plans, runtime statistics, and query history, making it one of the best tools for diagnosing performance problems.

Question 7

A database stores several years of sales history, but most queries retrieve only recent records. Which configuration recommendation can improve performance and simplify maintenance?

A. Disable indexing

B. Reduce available memory

C. Partition the table by date

D. Increase transaction isolation to SERIALIZABLE

Answer: C

Explanation: Partitioning large tables by date enables partition elimination, reducing I/O and improving maintenance operations such as archiving.

Question 8

Which database configuration recommendation helps reduce blocking while supporting high levels of concurrent read activity?

A. Enable Read Committed Snapshot Isolation (RCSI)

B. Disable indexes

C. Increase autogrowth frequency

D. Force table scans

Answer: A

Explanation: RCSI uses row versioning, allowing readers to access consistent data without blocking writers, thereby improving concurrency.

Question 9

A development team is selecting a compatibility level for a SQL Server database. What is the primary benefit of using a newer compatibility level after proper testing?

A. It automatically encrypts all database data.

B. It enables newer query optimizer improvements and T-SQL features.

C. It eliminates the need for indexes.

D. It disables Query Store.

Answer: B

Explanation: Newer compatibility levels introduce optimizer enhancements, improved cardinality estimation, and access to newer T-SQL functionality. Testing is important because execution plans may change.

Question 10

A database administrator configures SQL Server with unrestricted memory usage on a shared server hosting several applications. What is the most likely recommendation?

A. Continue using the default settings.

B. Increase TempDB file count only.

C. Disable automatic statistics.

D. Configure Maximum Server Memory to reserve memory for the operating system and other applications.

Answer: D

Explanation: Configuring Maximum Server Memory prevents SQL Server from consuming all available system memory, helping maintain overall server stability and ensuring sufficient resources remain available for the operating system and other applications.

Go to the DP-800 Exam Prep Hub main page

Data Governance, Data Security, Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Implement auditing – Part 3 (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 data security and compliance
      --> Implement auditing

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.

In Parts 1 and 2, you learned how SQL Server auditing works, how Azure SQL auditing integrates with Azure services, and how auditing supports compliance, monitoring, and forensic investigations. This final section summarizes the topic, compares auditing with related security features, presents real-world scenarios, and concludes with 10 DP-800-style practice exam questions.

Auditing vs. Other SQL Security Features

Understanding the differences between SQL Server security features is critical for the DP-800 exam.

Feature	Purpose	Protects Data?	Records Activity?
SQL Server Audit	Records security events	No	Yes
Dynamic Data Masking	Obscures sensitive data	Yes	No
Row-Level Security	Restricts row access	Yes	No
Always Encrypted	Encrypts sensitive columns	Yes	No
Transparent Data Encryption (TDE)	Encrypts database files	Yes	No
SQL Server Permissions	Controls access	Yes	No
Microsoft Defender for SQL	Detects suspicious activity	Indirectly	Partially

A common exam question is determining which technology satisfies a particular requirement:

Need to record who accessed payroll data? → Auditing
Need to hide Social Security numbers? → Dynamic Data Masking
Need to encrypt credit card numbers? → Always Encrypted
Need users to see only their own records? → Row-Level Security
Need protection for database files at rest? → Transparent Data Encryption

SQL Server Audit Workflow

A simplified auditing workflow is shown below.

			
User Action
     │
     ▼
SQL Server
     │
     ▼
Audit Specification
(Server or Database)
     │
     ▼
SQL Server Audit
     │
     ▼
Audit Target
(File, Azure Storage,
Log Analytics, Event Hub)
     │
     ▼
Investigation /
Compliance Reporting

		

Common Audited Events

Organizations commonly audit:

Authentication

Successful logins
Failed logins
Password changes
Login creation
Login deletion

Administrative Changes

CREATE DATABASE
DROP DATABASE
ALTER DATABASE
CREATE LOGIN
ALTER LOGIN
Server role changes

Security Changes

GRANT
DENY
REVOKE
Permission changes
Role membership changes

Data Access

SELECT
INSERT
UPDATE
DELETE
EXECUTE

Typically, organizations only audit access to sensitive tables rather than every table in the database.

Schema Changes

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE PROCEDURE
ALTER PROCEDURE
CREATE VIEW

Real-World Scenario 1

A healthcare provider stores patient records in Azure SQL Database.

Requirements:

Record every UPDATE made to patient records.
Retain logs for seven years.
Alert security personnel when permission changes occur.

Real-World Scenario 2

A financial institution experiences unauthorized data modifications.

Requirements:

Determine who modified account balances.
Determine when modifications occurred.
Review executed SQL statements.

Solution:

Query audit logs using:

sys.fn_get_audit_file() (SQL Server)
Log Analytics (Azure SQL)
Azure Storage audit files

Review:

Login name
Timestamp
Statement
Database
Object
Session ID

Real-World Scenario 3

A company wants to monitor privileged users only.

Instead of auditing every database action:

Audit:

Login events
Role changes
Permission changes
ALTER statements
DROP statements

This minimizes performance impact while providing meaningful security visibility.

Compliance Mapping

Requirement	SQL Auditing Helps?
Determine who accessed sensitive data	Yes
Record failed logins	Yes
Detect unauthorized permission changes	Yes
Track schema modifications	Yes
Recover deleted data	No
Encrypt stored data	No
Prevent unauthorized access	No (permissions control access)

Remember:

Auditing provides evidence, not protection.

Performance Best Practices

For production environments:

✔ Audit only important events.

✔ Avoid auditing every SELECT statement unless required.

✔ Archive logs regularly.

✔ Protect audit files with appropriate permissions.

✔ Monitor storage consumption.

✔ Review audit logs routinely.

✔ Test audit configurations before production deployment.

✔ Separate audit storage from transaction log storage whenever practical.

DP-800 Exam Tips

Be comfortable answering questions about:

Server Audit vs. Database Audit Specification
Azure SQL auditing
Audit destinations
Log Analytics
Azure Storage
Event Hubs
Microsoft Sentinel
Azure Monitor
Compliance scenarios
Investigating suspicious activity
Performance implications of auditing

Quick Review

Remember these key concepts:

Topic	Key Point
SQL Server Audit	Defines where audit data is stored
Server Audit Specification	Audits server-level events
Database Audit Specification	Audits database-level events
Azure Storage	Long-term audit storage
Log Analytics	Search and analyze audit events
Event Hubs	Stream audit events
Azure Monitor	Alerting and dashboards
Microsoft Sentinel	SIEM and threat investigation
Defender for SQL	Threat detection
`sys.fn_get_audit_file()`	Reads SQL Server audit files

Common DP-800 Pitfalls

Avoid these misconceptions:

Auditing does not encrypt data.
Auditing does not prevent unauthorized access.
Auditing is not a replacement for backups.
Auditing does not replace Microsoft Defender for SQL.
Dynamic Data Masking does not record access.
Always Encrypted does not log who viewed data.

Practice Exam Questions

Question 1

A company must determine who modified salary information in the Employees table. Which SQL Server feature should be implemented?

A. Transparent Data Encryption

B. SQL Server Audit

C. Dynamic Data Masking

D. Row-Level Security

Answer: B

Explanation:

SQL Server Audit records database activity, including UPDATE operations, allowing administrators to identify who modified data, when the modification occurred, and which statement was executed. The other options protect or restrict data but do not record user activity.

Question 2

Which SQL Server object specifies where audit records are written?

A. Database Audit Specification

B. Server Audit Specification

C. SQL Server Audit

D. Audit Action Group

Answer: C

Explanation:

The SQL Server Audit object defines the audit destination, such as a file, Windows Security Log, or Windows Application Log. Audit specifications determine which events are captured.

Question 3

An organization wants to search audit logs using Kusto Query Language (KQL). Which Azure service should store the audit data?

A. Azure Storage

B. Event Hubs

C. Log Analytics Workspace

D. Azure Key Vault

Answer: C

Explanation:

Log Analytics stores audit data in a format that supports KQL queries, dashboards, alerts, and Azure Monitor integration. Azure Storage is intended for long-term retention rather than interactive querying.

Question 4

Which audit specification captures database-level activities such as SELECT, UPDATE, and DELETE?

A. Server Audit

B. Database Audit Specification

C. Audit Target

D. Server Audit Specification

Answer: B

Explanation:

Database Audit Specifications capture actions performed within a database, including DML operations and permission changes. Server Audit Specifications capture server-level activities.

Question 5

Which Azure service is primarily intended for streaming audit events to external monitoring systems in near real time?

A. Azure Storage

B. Azure Files

C. Log Analytics

D. Azure Event Hubs

Answer: D

Explanation:

Azure Event Hubs provides scalable event streaming for integration with SIEM platforms, custom monitoring solutions, and security tools. It is optimized for real-time event ingestion.

Question 6

Which function is commonly used to read SQL Server audit files?

A. OPENROWSET()

B. sys.fn_get_audit_file()

C. sp_readaudit

D. sys.fn_audit_log()

Answer: B

Explanation:

sys.fn_get_audit_file() is the built-in table-valued function used to read SQL Server audit files and return audit events in a queryable format.

Question 7

A security administrator needs immediate notification whenever database permissions change. Which solution best meets this requirement?

A. Configure auditing with Log Analytics and Azure Monitor alerts.

B. Disable auditing and use transaction logs.

C. Store audit files only in Azure Storage.

D. Enable Transparent Data Encryption.

Answer: A

Explanation:

Auditing records permission changes, while Azure Monitor can generate alerts based on those audit events stored in Log Analytics. Azure Storage alone does not provide real-time alerting.

Question 8

Which statement correctly describes SQL Server auditing?

A. It encrypts sensitive columns.

B. It prevents unauthorized access to data.

C. It automatically restores deleted records.

D. It records security-related database and server activity.

Answer: D

Explanation:

Auditing records activities for monitoring, compliance, and investigation. It does not encrypt data, restore deleted records, or enforce permissions.

Question 9

Which audit target is generally recommended by Microsoft for most on-premises production SQL Server environments?

A. File

B. Windows Security Log

C. Windows Application Log

D. Azure Event Hubs

Answer: A

Explanation:

File targets provide excellent performance, scalability, and flexibility. They are the recommended destination for most production SQL Server deployments.

Question 10

Which Microsoft security service uses audit information to help detect suspicious database activity and investigate incidents?

A. Azure Backup

B. Microsoft Sentinel

C. SQL Server Agent

D. Azure Resource Manager

Answer: B

Explanation:

Microsoft Sentinel consumes audit logs from services such as Azure SQL Database to correlate events, detect threats, automate investigations, and assist security analysts. It complements auditing by providing advanced security analytics rather than simply recording events.

Final DP-800 Takeaways

For the DP-800 exam, remember these core principles:

SQL Server Audit defines where audit records are stored.
Server Audit Specifications capture server-level activities such as logins and server role changes.
Database Audit Specifications capture database-level activities such as data access and schema changes.
Azure Storage is ideal for long-term retention.
Log Analytics enables interactive querying, dashboards, and Azure Monitor alerts.
Azure Event Hubs supports real-time streaming to external systems.
Microsoft Sentinel extends auditing with SIEM capabilities, threat detection, and incident response.
Auditing provides accountability, supports compliance, and enables forensic investigations, but it does not replace encryption, access control, or threat protection technologies.

Go to the DP-800 Exam Prep Hub main page

Data Security, Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Implement secure database access, including passwordless (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 data security and compliance
      --> Implement secure database access, including passwordless

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

One of the primary responsibilities of a SQL AI Developer is ensuring that applications and users access databases securely. As organizations move toward cloud-native architectures and zero-trust security models, traditional username-and-password authentication is increasingly being replaced by more secure alternatives such as passwordless authentication, Microsoft Entra ID (formerly Azure Active Directory), managed identities, and service principals.

The DP-800 exam expects candidates to understand how to design secure authentication and authorization strategies for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric SQL solutions. Candidates should also understand when to use SQL authentication versus Microsoft Entra authentication, how passwordless authentication works, and how applications securely connect to databases without embedding secrets.

Authentication vs. Authorization

A common exam objective is distinguishing authentication from authorization.

Authentication answers the question:

Who are you?

Authentication verifies the identity of a user or application.

Examples include:

Microsoft Entra ID login
SQL login
Windows Authentication
Managed Identity
Service Principal

Authorization answers the question:

What are you allowed to do?

Authorization determines permissions after authentication succeeds.

Examples include:

SELECT permission
EXECUTE permission
Database roles
Row-Level Security (RLS)
Object-level permissions

Authentication always occurs before authorization.

Types of Database Authentication

SQL Server supports multiple authentication methods.

Authentication Method	Typical Usage
Windows Authentication	On-premises Active Directory environments
SQL Authentication	Username and password stored in SQL Server
Microsoft Entra Authentication	Azure SQL Database and Fabric
Managed Identity	Azure-hosted services
Service Principal	Automated applications and DevOps
Passwordless Authentication	Microsoft Entra authentication without passwords

SQL Authentication

SQL Authentication uses a SQL login and password stored by SQL Server.

Example:

			
CREATE LOGIN SalesUser
WITH PASSWORD = 'StrongPassword123!';

Advantages:

Easy to configure
Supported by virtually every SQL client
Independent of Active Directory

Disadvantages:

Password management required
Password rotation required
Secrets must often be stored in applications
Higher risk of credential theft

Microsoft recommends minimizing the use of SQL authentication whenever possible, particularly in Azure environments.

Windows Authentication

Windows Authentication uses Active Directory credentials.

Advantages:

Integrated security
Single sign-on (SSO)
Centralized identity management
Kerberos authentication
Password policies enforced automatically

Common connection string:

Integrated Security=True;

This is the preferred authentication method for on-premises SQL Server environments.

Microsoft Entra Authentication

Microsoft Entra ID is Microsoft’s cloud identity provider and is the preferred authentication mechanism for Azure SQL services.

Benefits include:

Single Sign-On (SSO)
Multi-Factor Authentication (MFA)
Conditional Access
Centralized identity management
Passwordless authentication support
Identity governance
Integration with Microsoft Fabric

Users authenticate through Microsoft Entra instead of SQL logins.

Example workflow:

			
User
     │
Microsoft Entra ID
     │
Azure SQL Database

		

Passwordless Authentication

Passwordless authentication eliminates traditional passwords while maintaining strong identity verification.

Instead of passwords, authentication may use:

Windows Hello for Business
Microsoft Authenticator
FIDO2 Security Keys
Passkeys
Biometric authentication
Managed Identities
Microsoft Entra tokens

Benefits include:

Eliminates password theft
Prevents password reuse
Reduces phishing attacks
Removes password rotation requirements
Improves user experience

Microsoft strongly recommends passwordless authentication whenever possible.

How Passwordless Authentication Works

Instead of sending a password:

			
Application
      │
Obtains Microsoft Entra access token
      │
Azure SQL Database validates token
      │
Connection established

		

The database trusts Microsoft Entra rather than validating a stored password.

Managed Identity

Managed Identity is one of the most important DP-800 topics.

A Managed Identity is an identity automatically managed by Azure for Azure resources.

Examples:

Azure App Service
Azure Functions
Azure Virtual Machines
Azure Container Apps
Azure Kubernetes Service
Azure Logic Apps

Instead of storing credentials:

			
Application
        ↓
Managed Identity
        ↓
Microsoft Entra ID
        ↓
Azure SQL Database

		

No passwords are stored.

Advantages of Managed Identity

Benefits include:

No stored passwords
Automatic credential rotation
Short-lived access tokens
Integrated with Microsoft Entra
Easier compliance
Reduced security risk

This is Microsoft’s recommended approach for Azure-hosted applications.

Service Principals

A Service Principal represents an application rather than a person.

Common uses include:

CI/CD pipelines
Azure DevOps
GitHub Actions
Background services
Automation scripts

Service principals authenticate through Microsoft Entra and can access Azure SQL databases securely.

Access Tokens

Modern Azure SQL authentication uses OAuth access tokens.

Instead of:

			
Username
Password

Applications obtain:

Microsoft Entra Access Token

The token:

Has a limited lifetime
Cannot be reused indefinitely
Reduces credential theft
Supports Conditional Access policies

Configuring Microsoft Entra Authentication

Typical steps include:

Configure a Microsoft Entra administrator for the SQL server.
Create Microsoft Entra users or groups.
Create contained database users.
Assign database roles.
Grant required permissions.

Example:

			
CREATE USER [Alice@contoso.com]
FROM EXTERNAL PROVIDER;

Grant role:

			
ALTER ROLE db_datareader
ADD MEMBER [Alice@contoso.com];

No SQL password is required.

Contained Database Users

Contained database users simplify authentication.

Advantages:

No SQL login required
Database portability
Simplified Azure SQL deployments
Works well with Microsoft Entra identities

Example:

			
CREATE USER [Developers]
FROM EXTERNAL PROVIDER;

Secure Connection Strings

Avoid storing:

			
Server=myserver;
User ID=admin;
Password=Password123;

Instead, use Microsoft Entra authentication.

Example (.NET):

Authentication=Active Directory Default;

The application automatically acquires an access token using the available identity.

Connection Security

Authentication should be combined with encrypted network connections.

Best practices include:

Require TLS encryption
Validate server certificates
Encrypt all client-server communication
Disable legacy protocols

Azure SQL encrypts client connections by default.

Principle of Least Privilege

Applications should receive only the permissions they require.

Example:

Application needs:

Execute stored procedures

Application does not need:

ALTER DATABASE
CONTROL
db_owner

Using least privilege minimizes security risks.

Passwordless Authentication with Azure Services

Many Azure services automatically support Managed Identity.

Example:

			
Azure Function
       │
Managed Identity
       │
Microsoft Entra
       │
Azure SQL Database

		

No secrets are stored in code or configuration files.

Microsoft Fabric Integration

Microsoft Fabric integrates closely with Microsoft Entra ID.

Fabric workloads support:

Microsoft Entra authentication
Single Sign-On
Role-based access
Passwordless identity
Unified identity management

DP-800 candidates should understand that Fabric relies heavily on Microsoft Entra identities rather than SQL logins.

Security Best Practices

Microsoft recommends:

Prefer Microsoft Entra authentication over SQL authentication.
Use passwordless authentication whenever possible.
Enable Multi-Factor Authentication (MFA).
Use Managed Identity for Azure-hosted applications.
Use Service Principals for automation.
Avoid embedding credentials in source code.
Store secrets in Azure Key Vault if passwords or keys are unavoidable.
Rotate credentials regularly when passwords must be used.
Use TLS encryption for all database connections.
Follow the principle of least privilege.
Audit authentication events regularly.
Use Conditional Access policies to protect administrative accounts.

Common DP-800 Exam Scenarios

You may be asked to determine:

Which authentication method is most secure.
When to use Managed Identity.
When to use Microsoft Entra authentication.
How passwordless authentication works.
When SQL Authentication is appropriate.
How applications connect without passwords.
How service principals authenticate.
How contained database users simplify Azure SQL deployments.
How to eliminate secrets from connection strings.
How to secure Azure-hosted AI applications accessing SQL databases.

DP-800 Exam Tips

Remember these key points:

Microsoft Entra ID is the preferred authentication mechanism for Azure SQL.
Passwordless authentication reduces phishing and credential theft.
Managed Identities eliminate stored passwords.
Service Principals authenticate applications and automation.
SQL Authentication still exists but is less secure.
Authentication verifies identity; authorization controls permissions.
Use least privilege for both users and applications.
Azure SQL supports OAuth access tokens instead of passwords.
Fabric uses Microsoft Entra authentication extensively.

Practice Exam Questions

Question 1

Which authentication method is Microsoft’s recommended approach for Azure-hosted applications connecting to Azure SQL Database?

A. Managed Identity

B. SQL Authentication

C. Windows Authentication

D. Shared SQL Administrator account

Correct Answer: A

Explanation:
Managed Identity eliminates the need to store credentials, automatically manages identity, and integrates with Microsoft Entra ID, making it Microsoft’s preferred authentication method for Azure-hosted applications.

Question 2

What is the primary purpose of passwordless authentication?

A. Improve query performance

B. Eliminate traditional passwords while securely verifying identity

C. Replace authorization

D. Encrypt database backups

Correct Answer: B

Explanation:
Passwordless authentication replaces passwords with stronger authentication mechanisms such as biometrics, security keys, Microsoft Authenticator, or access tokens, reducing the risk of credential theft.

Question 3

Which statement correctly distinguishes authentication from authorization?

A. Authentication determines database roles; authorization creates logins.

B. Authentication encrypts data; authorization decrypts it.

C. Authentication verifies identity, while authorization determines what actions are permitted.

D. Authentication assigns object permissions, while authorization validates passwords.

Correct Answer: C

Explanation:
Authentication confirms who a user or application is, whereas authorization determines what resources and operations that authenticated identity may access.

Question 4

A development team wants to eliminate database passwords from application configuration files. Which solution best meets this requirement?

A. Store SQL passwords in source code.

B. Use SQL Authentication with stronger passwords.

C. Share one administrator account among all applications.

D. Use Microsoft Entra authentication with Managed Identity.

Correct Answer: D

Explanation:
Managed Identity allows applications to authenticate without storing passwords or secrets, significantly improving security and simplifying credential management.

Question 5

Which authentication method is commonly used for automated CI/CD pipelines and background services?

A. Windows Authentication

B. Service Principal

C. SQL Authentication

D. Database Owner account

Correct Answer: B

Explanation:
Service Principals represent applications rather than users and are commonly used by automation tools such as Azure DevOps and GitHub Actions.

Question 6

Which feature is automatically provided by Managed Identity?

A. Automatic query tuning

B. Automatic index creation

C. Automatic credential rotation

D. Automatic data encryption

Correct Answer: C

Explanation:
Managed Identity automatically handles credential creation and rotation, eliminating the need for administrators or developers to manage passwords.

Question 7

Which SQL statement creates a Microsoft Entra user in an Azure SQL Database?

CREATE LOGIN Alice WITH PASSWORD='Password123';

CREATE USER Alice WITHOUT LOGIN;

CREATE USER [Alice@contoso.com] FROM EXTERNAL PROVIDER;

CREATE ROLE Alice;

Correct Answer: C

Explanation:
The FROM EXTERNAL PROVIDER clause creates a contained database user that authenticates through Microsoft Entra ID rather than a SQL login.

Question 8

Which security principle recommends granting only the permissions required for a user or application to perform its work?

A. Ownership chaining

B. Principle of least privilege

C. Password complexity

D. Data masking

Correct Answer: B

Explanation:
Least privilege minimizes security risks by limiting permissions to only those necessary for the required tasks.

Question 9

Which authentication mechanism does Azure SQL Database use with Microsoft Entra authentication?

A. Static passwords

B. Kerberos tickets only

C. SQL login hashes

D. OAuth access tokens

Correct Answer: D

Explanation:
Microsoft Entra authentication relies on OAuth access tokens, which are short-lived and securely validated by Azure SQL Database.

Question 10

Why is Microsoft Entra authentication generally preferred over SQL Authentication for Azure SQL Database?

A. It requires longer passwords.

B. It supports centralized identity management, MFA, Conditional Access, and passwordless authentication.

C. It eliminates database roles.

D. It removes the need for database permissions.

Correct Answer: B

Explanation:
Microsoft Entra authentication provides enterprise-grade identity management features, including Single Sign-On, Multi-Factor Authentication, Conditional Access, centralized administration, and support for passwordless authentication, making it more secure than traditional SQL Authentication.

Go to the DP-800 Exam Prep Hub main page

Data Development, Database Administration, Databases, SQL August 10, 2023

SQL Tips: How to rename a column in a table – Oracle database – Oracle SQL

At times you will need to change the name of a column in an existing table. If you are not changing the data type, it is just one statement / step that needs to be executed. However, I strongly recommend that you also do a backup step, especially if you’re making the change in a production environment, just in case of an unexpected issue.

If you choose to do the backup, you may perform this with a “create-table-as-select” statement in this form:

create table [table_name_backup] as select * from [table_name];

Here is an example of the above statement:

create table EMPLOYEES_BKUP as select * from EMPLOYEES;

Now that the table you are modifying is all backed up, you can proceed to rename the column.

The rename SQL statement would take this form:

alter table [table_name] rename column [existing_column_name] to [new_column_name];

An example of the statement:

alter table EMPLOYEES rename column SEX to GENDER;

Thanks for reading! I hope you found this information useful.

Data Development, Data Governance, Database Administration, Databases July 22, 2023July 22, 2023

Oracle Error when table or other object has the same name as the schema name

We recently had a situation where a procedure was running fine in 2 environments but was failing in another. During debugging, it was determined that if the schema prefix was removed from the procedure call, it would run fine, otherwise it fails.

The following error was produced:

ERROR at line 1:

ORA-06550: line 1, column 14:

PLS-00302: component ‘MyProcedure’ must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

After some research, the DBA found a web post that indicated that this error is generated if you have an object with the same name as the schema.

You can check if you have any such objects by running this SQL command:

SQL> select * from dba_objects where object_name = ‘Your_Schema_Name’;

(of course, where “Your_Schema_Name” is the actual name of your schema)

If you do, then you should rename the object or remove it if it is no longer needed. Of course, if it is a valid object that is being used, you will need to rename it in all the places in which it is being used.

Thanks for reading! Good luck on your data journey!

Business Intelligence Platform, Data Integration, Database Administration, Performance Tuning November 13, 2019

BI Application getting ORA-02391 error

Last week we rolled out a new dashboard that uses a new data source.
In one of our BI environments, the application was throwing an error:
“ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit at OCI call OCISessionBegin“

This is an Oracle Database error, and not an error directly from the BI Application.

For the “ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit” error …
The Cause is: An attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSIONS_PER_USER clause of the user profile.
And the Action for resolution is: End one or more concurrent sessions or ask the database administrator to increase the SESSIONS_PER_USER limit of the user profile.

Turns out the SESSIONS_PER_USER parameter was set too low; it was set to 3 for the user being used to access the database from the BI application. This error could have also been observed from an ETL tool accessing the database with an ID with the same parameter setting.

One of the DBAs bumped this parameter up to 30 for the user, and that resolved the issue.
We requested for this change to be done on the BI application databases in all the environments – Development, Test, QA, and Production.

Although all seems to be well, we will now monitor to see how many sessions the application is using and if there is any negative impact on the source application. This will allow us to determine if we need to make any other adjustments.

Thanks for reading. I hope you found this information useful.

Welcome to the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub!

Audience profile (from Microsoft’s site)

Skills at a glance (as specified in the official study guide)

Topic-by-Topic Exam Content

Design and develop database solutions (35–40%)

Design and implement database objects

Implement programmability objects

Write advanced T-SQL code

Design and implement SQL solutions by using AI-assisted tools

Secure, optimize, and deploy database solutions (35–40%)

Implement data security and compliance

Optimize database performance

Implement CI/CD by using SQL Database Projects

Integrate SQL solutions with Azure services

Implement AI capabilities in database solutions (25–30%)

Design and implement models and embeddings

Design and implement intelligent search

Design and implement retrieval-augmented generation (RAG)

DP-800 Practice Exams

Important DP-800 Resources

Link to the free, comprehensive, self-paced course on Microsoft Learn: Course: Develop AI-enabled database solutions

Link to the certification page:

Link to the study guide:

YouTube resources:

Courses:

Introduction

What Are Secrets?

Why Secrets Management Is Important

Common Security Risks

Principles of Secrets Management

Azure Key Vault

Types of Objects in Azure Key Vault

Secrets

Keys

Certificates

Secret Lifecycle

Secret Versioning

Managed Identity

Types of Managed Identity

System-Assigned Managed Identity

User-Assigned Managed Identity

Microsoft Entra ID Authentication

Secrets in SQL Database Projects

Secrets in Azure DevOps

Secrets in GitHub

GitHub Actions Example

Azure DevOps Example

Secret Rotation

Access Control

RBAC vs Access Policies

Azure RBAC

Access Policies

Secret Auditing

CI/CD Pipeline Integration

Environment-Specific Secrets

Secure Connection Strings

Preventing Secret Leakage

Common Mistakes

Best Practices

DP-800 Exam Tips

Practice Exam Questions

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Introduction

Importing an Existing Database into a SQL Database Project

Reverse Engineering a Database

Source Control Integration

Branching Strategies

Feature Branches

Release Branches

Database References

Example of a Cross-Database Reference

Link to the free, comprehensive, self-paced course on Microsoft Learn:
Course: Develop AI-enabled database solutions