Category: SQL

AI-200, azure, Databases, Microsoft Certification, NoSQL, SQL August 15, 2026

Implement connection optimization to improve throughput and minimize latency (AI-200 Exam Prep)

This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Database for PostgreSQL
      --> Implement connection optimization to improve throughput and minimize latency

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.

Overview

Connection management is an important part of application performance when working with Azure Database for PostgreSQL. An application can have well-designed SQL, appropriate indexes, and sufficient compute resources and still experience poor performance if it creates too many database connections, repeatedly establishes short-lived connections, or communicates with the database across a high-latency network path.

For the AI-200 exam, the key idea is:

Optimize how applications establish, reuse, and manage PostgreSQL connections before simply increasing the database’s connection limit.

Connection optimization involves several complementary strategies:

Use connection pooling.
Reuse established connections rather than repeatedly creating them.
Avoid excessive concurrent connections.
Place applications and databases appropriately within Azure.
Use private networking where appropriate.
Configure connection and pool sizes based on workload.
Use appropriate timeout and retry behavior.
Monitor connection utilization and resource consumption.
Understand how Azure’s built-in PgBouncer works.
Design serverless applications carefully because they can create connection bursts.

Azure Database for PostgreSQL Flexible Server provides built-in PgBouncer to help with connection pooling. Azure’s current guidance specifically recommends using PgBouncer rather than simply increasing max_connections when more connection capacity is needed.

1. Why Database Connections Affect Performance

A PostgreSQL connection is not free.

When an application establishes a connection, PostgreSQL must perform connection setup, authentication, session initialization, and resource allocation. PostgreSQL uses a process-based architecture, so maintaining large numbers of connections consumes server resources.

This becomes particularly important for applications that repeatedly perform operations such as:

Open connection.
Execute one query.
Close connection.
Repeat thousands of times.

The database may spend substantial resources managing connections rather than processing useful database work.

Azure specifically notes that large numbers of connections can increase CPU utilization and contribute to problems such as memory pressure, disk contention, and lock contention. Short-lived connections are particularly problematic because connection establishment and termination occur frequently.

Connection overhead

Conceptually:

			
Application
    |
    | Establish connection
    v
PostgreSQL
    |
    | Authenticate / initialize session
    |
    | Execute query
    |
    | Return results
    |
    | Close connection
    v
Application

		

If this happens for every operation, the overhead can become significant.

A better architecture is:

			
Application
    |
    v
Connection Pool
    |
    +---- Existing PostgreSQL connection
    |
    +---- Existing PostgreSQL connection
    |
    +---- Existing PostgreSQL connection
    |
    v
Azure Database for PostgreSQL

		

The application obtains an existing connection, uses it, and returns it to the pool.

2. Connection Pooling

Connection pooling is one of the most important concepts for this exam topic.

A connection pool maintains a collection of already-established database connections.

Instead of creating a new connection for every database operation, an application:

Requests a connection from the pool.
Uses the connection.
Completes the transaction or operation.
Returns the connection to the pool.

The connection remains available for reuse.

Without pooling

			
Request 1 → Create connection → Query → Close
Request 2 → Create connection → Query → Close
Request 3 → Create connection → Query → Close
Request 4 → Create connection → Query → Close

With pooling

			
Request 1 ─┐
Request 2 ─┤
Request 3 ─┼→ Connection Pool → Reusable DB connections
Request 4 ─┘

This reduces connection establishment overhead and can significantly improve throughput for workloads containing many small or short-lived operations.

3. Client-Side Connection Pooling

There are two important approaches to pooling:

Client-side/application pooling
Server-side pooling with PgBouncer

Client-side pooling is implemented by the application framework or PostgreSQL driver.

For example, a web application might maintain a pool containing a limited number of PostgreSQL connections.

Suppose an application receives 500 simultaneous HTTP requests.

It does not necessarily need 500 PostgreSQL connections.

Instead:

			
500 application requests
          |
          v
   Connection Pool
          |
          +---- Connection 1
          +---- Connection 2
          +---- Connection 3
          ...
          +---- Connection 20

		

Requests can share the available database connections as they become available.

Benefits

Client-side pooling can:

Reduce connection establishment overhead.
Reduce authentication overhead.
Reduce database resource consumption.
Improve application throughput.
Reduce latency for short database operations.
Protect the database from excessive connection creation.

A particularly important point for the exam is that pool size should not simply be set equal to the maximum number of application requests.

A pool containing thousands of connections can itself become a performance problem.

4. Azure Database for PostgreSQL Built-In PgBouncer

Azure Database for PostgreSQL Flexible Server provides built-in PgBouncer as an optional connection-pooling solution.

PgBouncer is a lightweight connection pooler positioned between the application and PostgreSQL.

Conceptually:

			
Application
    |
    | Many client connections
    v
+----------------+
|   PgBouncer    |
| Connection Pool|
+----------------+
    |
    | Fewer PostgreSQL connections
    v
PostgreSQL Server

		

This allows many client connections to be handled without requiring an equivalent number of active PostgreSQL server connections.

Azure’s built-in PgBouncer is available for General Purpose and Memory Optimized compute tiers and can be used with public or private networking.

5. PgBouncer Port 6432

When using the built-in PgBouncer service, applications connect through port:

6432

The standard PostgreSQL connection uses:

5432

So a conceptual connection configuration is:

			
Direct PostgreSQL:
server.postgres.database.azure.com:5432
Through PgBouncer:
server.postgres.database.azure.com:6432

Azure’s current documentation states that PgBouncer uses port 6432 and the same hostname as the PostgreSQL server.

Exam tip

If a question asks how to route an Azure Database for PostgreSQL application through the built-in PgBouncer service, port 6432 is an important detail to recognize.

6. PgBouncer Transaction Pooling

The built-in PgBouncer configuration uses transaction pooling by default.

In transaction pooling, a PostgreSQL server connection is assigned to a client for the duration of a transaction.

After the transaction completes, the server connection can be reused by another client.

Conceptually:

			
Client A
   |
   | BEGIN
   | SQL
   | SQL
   | COMMIT
   |
   v
Connection returned to pool
Client B
   |
   | BEGIN
   | SQL
   | COMMIT
   |
   v
Same server connection can be reused

		

This is highly effective for applications with many concurrent clients but relatively short transactions.

Azure’s current PgBouncer configuration documentation identifies transaction as the default pgbouncer.pool_mode.

7. PgBouncer Client Connections vs. PostgreSQL Connections

This distinction is especially important for exam questions.

Suppose an application has:

5,000 client connections

That does not mean PostgreSQL must execute 5,000 database sessions simultaneously.

PgBouncer can accept many client connections while maintaining a smaller number of actual PostgreSQL server connections.

The pooler can queue clients while database connections are busy.

Therefore:

Increasing the number of client connections does not automatically increase the number of PostgreSQL connections actually executing work.

Azure documents separate PgBouncer settings for client connections and server-side pool size, including pgbouncer.max_client_conn and pgbouncer.default_pool_size.

8. Do Not Simply Increase `max_connections`

A common mistake is to encounter:

FATAL: sorry, too many clients already.

and respond by increasing PostgreSQL’s max_connections dramatically.

This is generally not the preferred solution.

Every PostgreSQL connection consumes resources, whether it is actively executing a query or sitting idle.

Increasing max_connections can therefore make the underlying resource problem worse.

Azure recommends using PgBouncer instead when additional connection capacity is required and specifically recommends conservative pooling values followed by monitoring.

Better approach

Instead of:

			
More connections
       ↓
Increase max_connections
       ↓
More memory/resource consumption

		

Prefer:

			
Many application requests
       ↓
Connection pooling
       ↓
Controlled number of database connections
       ↓
Better resource utilization

		

9. Choosing an Appropriate Pool Size

A connection pool should be sized based on:

Application concurrency.
Query duration.
Transaction duration.
Database compute capacity.
CPU utilization.
Memory availability.
Workload characteristics.
Number of application instances.

A larger pool isn’t automatically better.

Consider:

Pool = 10 connections

If queries are short and the database is adequately sized, this may be sufficient.

Increasing the pool to:

Pool = 500 connections

could actually make performance worse if those connections compete for CPU, memory, locks, or I/O.

Azure’s current guidance recommends conservative PgBouncer values and monitoring resource utilization and application performance rather than blindly maximizing connection counts.

10. Connection Pooling in Scaled-Out Applications

This becomes particularly important in cloud applications.

Imagine an application running on 20 instances.

If every instance creates a pool of 50 connections:

			
20 application instances
×
50 connections each
=
1,000 potential connections

		

If the application scales to 100 instances:

100 × 50 = 5,000 connections

This can unexpectedly overwhelm the database.

Therefore, pool sizing must consider the total number of application instances, not just the pool size configured in one instance.

Exam scenario

If an Azure application automatically scales from 5 instances to 50 instances, a fixed connection pool size can multiply database connections dramatically.

The correct response is often to:

Reduce per-instance pool sizes.
Use connection pooling appropriately.
Use PgBouncer when appropriate.
Monitor total database connections.
Avoid simply raising max_connections.

11. Serverless Applications and Connection Bursts

Serverless applications require special attention.

Azure Functions and similar platforms can scale out rapidly.

For example:

			
Normal:
5 function instances
× 10 DB connections
= 50 connections

During a traffic spike:

			
100 function instances
× 10 DB connections
= 1,000 connections

This can create a connection storm.

Recommended design

Use:

Connection pooling where appropriate.
Conservative pool sizes.
PgBouncer when appropriate.
Efficient transaction design.
Connection reuse.
Appropriate application scaling limits.
Monitoring and alerting.

The goal is to allow application scalability without allowing database connections to grow uncontrollably.

12. Connection Churn

Connection churn refers to repeatedly opening and closing database connections.

High connection churn can be especially harmful when connections are short-lived.

For example:

			
Open → Query → Close
Open → Query → Close
Open → Query → Close
Open → Query → Close
...

		

The database spends resources repeatedly creating and destroying connections.

Instead:

			
Create pool
     ↓
Reuse connection
     ↓
Execute transaction
     ↓
Return connection
     ↓
Reuse connection

		

Azure specifically identifies frequent short-duration connections as a source of performance degradation.

Key exam concept

If the question describes:

Many short-lived connections
High connection counts
High CPU associated with connection activity
Connection establishment overhead
Web applications with many concurrent requests

Think:

Connection pooling

13. Application Location Matters

Connection optimization isn’t limited to the database itself.

Network distance affects latency.

An application running in one Azure region while its database is in another region introduces network latency for every database interaction.

For example:

			
Application
    |
    | Long network path
    v
PostgreSQL

		

is generally less desirable than:

			
Application
    |
    | Short network path
    v
PostgreSQL

		

Azure recommends considering client and network characteristics, including where clients are located and whether requests cross regions or availability zones.

General principle

Place latency-sensitive application components close to the database.

This is particularly important for applications that perform many sequential database operations.

14. Availability Zones and Latency

Azure Database for PostgreSQL Flexible Server supports deployment within availability zones and zone-redundant high availability.

For latency-sensitive applications, the placement of the application relative to the database should be considered.

However, don’t confuse high availability with performance optimization.

Zone-redundant HA primarily provides resilience by maintaining a standby in another availability zone. It is not a mechanism for making ordinary queries faster.

A test question might present:

An application requires low latency but also requires zone-redundant HA.

The appropriate design should balance:

Application location.
Primary database location.
Availability-zone architecture.
Required resilience.
Network latency.

15. Private Networking

Azure Database for PostgreSQL Flexible Server supports:

Private access through virtual network integration.
Public access with allowed IP addresses.
Public access plus private endpoints in supported configurations.

For applications hosted in Azure, private networking can provide a secure network path and can be part of an overall architecture designed for predictable connectivity.

With private access, Azure resources communicate with the PostgreSQL server through private IP addresses within the virtual network architecture.

Important distinction

Do not assume:

“Private networking automatically makes every query faster.”

Network latency depends on architecture and physical/network topology.

The more useful exam principle is:

Use an appropriate network topology and avoid unnecessary network distance or cross-region traffic.

16. DNS and Connection Reliability

Applications should use the PostgreSQL server’s fully qualified domain name (FQDN) rather than hard-coded IP addresses.

This is especially important because managed services can change underlying infrastructure.

A connection string should conceptually look like:

			
Host=myserver.postgres.database.azure.com
Port=5432
Database=mydatabase
User Id=...
Password=...
SSL Mode=Require

		

rather than relying on a fixed IP address.

Using the service hostname allows Azure to manage underlying infrastructure changes without requiring application code to change.

17. TLS and Connection Overhead

Azure Database for PostgreSQL uses TLS/SSL for data in transit, with TLS 1.2 and later supported.

Encryption is an important security requirement, but TLS also introduces some connection-handshake overhead.

This is another reason connection pooling is valuable.

Instead of repeatedly paying connection-establishment costs:

			
TLS handshake
Authentication
Session initialization
Query
Close

		

the application can establish connections and reuse them.

Thus, pooling can improve performance while allowing secure TLS connections to remain in use.

18. Connection Timeouts

Connection optimization also involves appropriate timeout settings.

A connection timeout controls how long an application waits while establishing a connection.

A command/query timeout controls how long an operation is allowed to execute.

These are different concepts.

Connection timeout

Can I connect to PostgreSQL?

Command timeout

How long should I allow this query to execute?

Pool wait timeout

How long should I wait for a connection from the pool?

Understanding these distinctions is useful when diagnosing latency.

A long connection timeout does not make a connection faster. It merely allows the application to wait longer before failing.

19. Retries and Transient Failures

Cloud applications should be designed to tolerate transient failures.

For example:

			
Application
     |
     | Connection attempt
     X
Transient network failure
     |
     v
Retry with appropriate backoff

		

Retries should be:

Limited.
Controlled.
Appropriate for the operation.
Implemented with exponential backoff where appropriate.
Combined with connection pooling.

Avoid retry storms

If thousands of application requests all fail simultaneously and immediately retry:

			
Failure
 ↓
1,000 retries
 ↓
Database/network overload
 ↓
More failures
 ↓
1,000 more retries

		

This can make an outage worse.

A better approach uses controlled retries and backoff.

20. Connection Pooling and Transactions

Application code should release pooled connections promptly.

A common pattern is:

			
Acquire connection
    ↓
Begin transaction
    ↓
Execute operations
    ↓
Commit / Rollback
    ↓
Release connection

		

Avoid holding a database connection while performing unrelated work.

For example, this is inefficient:

			
Acquire DB connection
       ↓
Call external AI service
       ↓
Wait 10 seconds
       ↓
Perform database query
       ↓
Release connection

		

The connection is unavailable to other requests while the application waits.

A better approach is:

			
Call AI service
       ↓
Receive result
       ↓
Acquire DB connection
       ↓
Perform database transaction
       ↓
Release connection

		

This maximizes connection reuse.

21. Avoid Long-Running Transactions

Long transactions can reduce the effectiveness of connection pooling.

If a transaction remains open for an extended period, its database connection remains occupied.

For example:

			
Connection Pool
   |
   +-- Connection 1 → long transaction
   +-- Connection 2 → available
   +-- Connection 3 → available
   +-- Connection 4 → available

		

As more connections become tied up in long-running transactions, other requests may have to wait.

Therefore:

Keep transactions as short as practical.

This is particularly important in high-concurrency applications.

22. PgBouncer Configuration to Know

Several PgBouncer settings are useful to recognize for the AI-200 exam.

Setting	Purpose
`pgbouncer.enabled`	Enables built-in PgBouncer
`pgbouncer.pool_mode`	Controls when server connections can be reused
`pgbouncer.default_pool_size`	Number of server connections allowed per user/database pool
`pgbouncer.max_client_conn`	Maximum number of client connections
`pgbouncer.min_pool_size`	Maintains a minimum number of server connections
`pgbouncer.query_wait_timeout`	Maximum time a query can wait for execution assignment
`pgbouncer.server_idle_timeout`	Controls how long an idle server connection remains before being dropped
`pgbouncer.max_prepared_statements`	Controls protocol-level prepared statement tracking in supported pooling modes

Current Azure documentation lists transaction pooling as the default pool mode, a default default_pool_size of 50, and a default max_client_conn of 5,000. These are service configuration defaults and should not be interpreted as universal recommendations for every workload.

23. Monitoring Connections

Connection optimization should be based on measurement rather than guesswork.

Useful things to monitor include:

Active connections.
Idle connections.
Connection creation rate.
Connection wait time.
CPU utilization.
Memory utilization.
Query duration.
Transaction duration.
Storage I/O.
Application response time.
Pool utilization.
PgBouncer metrics.

Azure Database for PostgreSQL provides monitoring and alerting capabilities, including host metrics and slow-query logging.

Built-in PgBouncer can also expose metrics for active connections, idle connections, pooled connections, and connection pools when the appropriate PgBouncer diagnostics settings are enabled.

24. Diagnosing Connection-Related Performance Problems

When an application is slow, don’t immediately assume the SQL query is the problem.

A useful troubleshooting sequence is:

Step 1: Check application latency

Determine whether the delay occurs:

Before database access.
While waiting for a connection.
During query execution.
While receiving results.

Step 2: Check connection counts

Look for:

Excessive connections.
Rapid connection growth.
Many idle connections.
Connection-limit errors.

Step 3: Check connection churn

Determine whether the application repeatedly creates and destroys connections.

Step 4: Check pool configuration

Look at:

Pool size.
Maximum pool size.
Pool wait time.
Connection lifetime.
Number of application instances.

Step 5: Check database resources

Look at:

CPU.
Memory.
Storage.
IOPS.
Query performance.

Step 6: Check network topology

Determine whether traffic crosses:

Regions.
Availability zones.
Unnecessary network boundaries.

Step 7: Optimize the actual workload

Only after understanding the bottleneck should you consider:

Query optimization.
Index changes.
Compute scaling.
Storage changes.
Architecture changes.

25. Connection Optimization Strategy

A practical strategy for Azure Database for PostgreSQL is:

                    Application
                         |
                         v
                Application Pool
                         |
                         v
                  PgBouncer
                         |
                         v
             Azure PostgreSQL
                         |
              +----------+----------+
              |                     |
            CPU                   Storage

Then optimize each layer:

Application

Reuse connections.
Avoid connection churn.
Keep transactions short.
Configure reasonable pool sizes.
Avoid holding connections while performing unrelated work.

Pooling

Use client-side pooling where appropriate.
Use Azure’s built-in PgBouncer when appropriate.
Understand transaction pooling.
Monitor pool utilization.

Network

Place applications close to the database.
Avoid unnecessary cross-region communication.
Use appropriate private networking.
Use the database FQDN.

Database

Don’t blindly increase max_connections.
Scale compute when CPU/memory is genuinely the bottleneck.
Optimize expensive queries.
Monitor resource utilization.

26. Common AI-200 Exam Traps

Trap 1: “Increase `max_connections`“

Usually not the best first answer.

Think: connection pooling.

Trap 2: “Create a connection for every request”

Usually inefficient.

Think: reuse connections through pooling.

Trap 3: “Use the largest possible pool”

Incorrect.

Think: appropriately sized pool based on workload and database capacity.

Trap 4: “PgBouncer increases database processing capacity”

Not exactly.

PgBouncer improves connection management and allows many clients to share a smaller number of database connections. It does not magically increase the CPU or query-processing capacity of PostgreSQL.

Trap 5: “More connections always means more throughput”

False.

Too many connections can cause contention and resource pressure.

Trap 6: “Private networking automatically reduces latency”

Not necessarily.

Private networking provides an appropriate secure connectivity architecture, but actual latency depends on network topology and location.

Trap 7: “Connection timeout controls query execution time”

False.

Connection timeout and query/command timeout address different stages of database interaction.

Trap 8: “Connection pooling eliminates the need to optimize SQL”

False.

Pooling solves connection-management overhead. Poor SQL can still consume substantial CPU, memory, I/O, and locks.

27. Key Takeaways for the AI-200 Exam

Remember these principles:

Connection establishment has a cost.
Connection pooling reduces connection churn.
Reuse connections rather than repeatedly creating them.
Don’t equate application concurrency with database connection count.
Avoid blindly increasing max_connections.
Azure Database for PostgreSQL Flexible Server provides built-in PgBouncer.
The built-in PgBouncer endpoint uses port 6432.
Transaction pooling is the default PgBouncer pool mode.
Pool size should be based on workload and database capacity.
Scaled-out applications multiply connection counts.
Serverless applications can cause connection bursts.
Keep transactions short.
Don’t hold connections while waiting on unrelated operations.
Keep latency-sensitive applications geographically and architecturally close to the database.
Monitor connection counts, CPU, memory, latency, and pool utilization.
Use retries carefully to avoid retry storms.
Use the database FQDN rather than hard-coded IP addresses.
Connection pooling complements—not replaces—query and database optimization.

Practice Exam Questions

Question 1

An AI-powered web application uses Azure Database for PostgreSQL. During periods of high traffic, the application creates thousands of short-lived database connections. CPU utilization on the PostgreSQL server increases significantly even though the queries themselves are relatively simple.

What should you implement first?

A. Connection pooling
B. Increase the PostgreSQL max_connections setting substantially
C. Disable TLS for database connections
D. Move the database to a larger storage account

Answer: A

Explanation:
Connection establishment and termination consume database resources. Connection pooling allows established connections to be reused, reducing connection churn and improving throughput. Increasing max_connections can increase resource consumption rather than solve the underlying problem.

Question 2

An application uses Azure Database for PostgreSQL Flexible Server and Azure’s built-in PgBouncer. The application must connect through the PgBouncer endpoint rather than directly to PostgreSQL.

Which port should the application use?

A. 443
B. 5432
C. 8080
D. 6432

Answer: D

Explanation:
The standard PostgreSQL endpoint uses port 5432. Azure’s built-in PgBouncer service uses port 6432. The application can use the PostgreSQL server hostname while changing the port to 6432.

Question 3

A web application is deployed across 30 instances. Each instance maintains a connection pool with a maximum of 100 PostgreSQL connections. During scaling events, the database experiences connection pressure.

What is the most likely cause?

A. PostgreSQL automatically duplicates every database row
B. TLS encryption prevents connection reuse
C. PgBouncer automatically disables indexes
D. The application-level pool size is multiplied across application instances

Answer: D

Explanation:
Connection pools are generally maintained per application instance. Thirty instances with a potential 100 connections each could create as many as 3,000 application-side connections. Pool sizing must therefore consider the total number of instances.

Question 4

An application frequently opens a PostgreSQL connection, executes one short query, and immediately closes the connection. The pattern occurs thousands of times per minute.

Which change is most likely to improve throughput?

A. Increase the number of database connections created per request
B. Increase storage capacity
C. Disable connection authentication
D. Reuse connections through a connection pool

Answer: D

Explanation:
The workload exhibits high connection churn. Connection pooling allows existing connections to be reused, avoiding repeated connection establishment and teardown.

Question 5

A development team encounters the following error on an Azure Database for PostgreSQL server:

FATAL: sorry, too many clients already.

The team wants to support more application clients without unnecessarily increasing the number of active PostgreSQL server connections.

What should they consider?

A. Azure Database for PostgreSQL built-in PgBouncer
B. Increasing the number of database indexes
C. Disabling SSL/TLS
D. Converting all queries to stored procedures

Answer: A

Explanation:
PgBouncer can accept many client connections while managing a smaller pool of PostgreSQL server connections. Azure recommends PgBouncer as a connection-management solution rather than simply increasing max_connections.

Question 6

An application acquires a PostgreSQL connection from its pool and then calls an external AI service that takes 15 seconds to respond. The application keeps the database connection checked out during those 15 seconds.

What is the primary concern?

A. PostgreSQL automatically deletes the connection
B. The connection remains occupied unnecessarily and reduces pool availability
C. The database will automatically increase its CPU capacity
D. The AI service will execute the PostgreSQL transaction

Answer: B

Explanation:
A pooled connection should generally be held only while database work is being performed. Holding connections during unrelated long-running operations reduces the number of connections available to other requests and can increase latency.

Question 7

An AI application has its compute resources in one Azure region and its Azure Database for PostgreSQL server in a distant region. The application performs many sequential database calls, and network latency is a major contributor to response time.

Which architectural change is most likely to reduce network latency?

A. Increase max_connections
B. Increase the PostgreSQL database password length
C. Place latency-sensitive application and database resources closer together
D. Increase the connection pool to several thousand connections

Answer: C

Explanation:
Reducing network distance can reduce round-trip latency for database operations. Increasing connection counts does not solve geographic network latency and may introduce additional resource contention. Azure explicitly identifies client location and cross-region traffic as factors in PostgreSQL performance.

Question 8

Which statement best describes transaction pooling in PgBouncer?

A. A PostgreSQL server connection can be reused after a client’s transaction completes
B. Every client permanently receives its own PostgreSQL server process
C. Every SQL statement requires a new physical database server
D. All application clients must share one PostgreSQL connection

Answer: A

Explanation:
In transaction pooling, a server-side PostgreSQL connection is associated with a client for the duration of a transaction and can subsequently be reused. Azure’s built-in PgBouncer uses transaction pooling by default.

Question 9

An administrator wants to improve PostgreSQL performance and notices that the database has a very high max_connections value. Many of the connections become active simultaneously during traffic spikes.

What is the primary concern with simply increasing max_connections further?

A. It automatically disables connection pooling
B. It prevents PostgreSQL from using indexes
C. It forces all queries to become distributed queries
D. More connections can increase memory and other resource consumption and cause performance problems

Answer: D

Explanation:
Each PostgreSQL connection consumes resources. A high number of active connections can increase memory and CPU pressure and contribute to contention. Azure specifically advises against simply increasing max_connections and recommends connection pooling such as PgBouncer when additional connection capacity is needed.

Question 10

A serverless AI application experiences sudden traffic spikes. Each newly created application instance establishes several PostgreSQL connections immediately. During scale-out events, the database reaches its connection limit.

Which design change is most appropriate?

A. Configure every serverless instance to create more connections
B. Use controlled connection pooling and carefully manage per-instance connection limits
C. Remove all database indexes
D. Increase query timeouts so connections remain open longer

Answer: B

Explanation:
Serverless scale-out can multiply connection counts quickly. Controlled pooling and conservative per-instance connection limits help prevent connection storms. PgBouncer can also be considered when appropriate. Increasing the number of connections per instance would make the problem worse.

Final Exam Perspective

For this AI-200 objective, think of connection optimization as a resource-management problem rather than simply a database configuration problem.

When you see an exam scenario involving:

Many clients + short-lived connections + high latency + connection errors

your thought process should be:

			
Are connections being reused?
        ↓
Is connection pooling configured?
        ↓
Is the pool appropriately sized?
        ↓
Would PgBouncer help?
        ↓
Are too many application instances creating connections?
        ↓
Is the application close enough to PostgreSQL?
        ↓
Are transactions short?
        ↓
Are CPU, memory, and query performance actually the bottleneck?

		

The most important rule to remember is:

Don’t solve connection pressure by blindly adding more database connections. Control and reuse connections, keep transactions efficient, minimize unnecessary network latency, and scale the database only when monitoring demonstrates that database resources—not connection management—are the actual bottleneck.

This distinction is especially important for AI workloads because AI applications frequently combine highly concurrent APIs, serverless processing, vector/database operations, and external AI-service calls. Efficient connection management helps keep the database available for the work that actually matters.

Go to the AI-200 Exam Prep Hub main page

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.

azure, Databases, DP-800, Microsoft Certification, SQL August 1, 2026August 1, 2026

Handle changes by using change event streaming (CES), change data capture (CDC), Change Tracking, Azure Functions with SQL trigger binding, or Azure Logic Apps (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%)
   --> Integrate SQL solutions with Azure services
      --> Handle changes by using change event streaming (CES), change data capture (CDC), Change Tracking, Azure Functions with SQL trigger binding, or Azure Logic Apps

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 applications rarely operate in isolation. A single database update often needs to trigger downstream actions such as updating search indexes, synchronizing data warehouses, refreshing caches, sending notifications, invoking APIs, or triggering AI pipelines.

Microsoft SQL Server and Azure SQL provide several mechanisms to detect and react to data changes. The DP-800 exam expects candidates to understand the capabilities, strengths, limitations, and appropriate use cases for each technology.

The primary technologies include:

Change Data Capture (CDC)
Change Tracking
Change Event Streaming (CES)
Azure Functions with SQL Trigger Binding
Azure Logic Apps

Understanding when and why to use each technology is more important than memorizing implementation details.

Why Change Detection Matters

Applications often need to know when data changes occur without continuously querying every table.

Examples include:

Synchronizing CRM and ERP systems
Triggering AI workflows after new customer data arrives
Updating recommendation engines
Refreshing search indexes
Sending order confirmation emails
Replicating data into Microsoft Fabric
Populating analytical data lakes
Updating Power BI semantic models

Without an efficient change detection mechanism, applications would have to repeatedly scan entire tables, resulting in:

Poor performance
Increased costs
Higher latency
Unnecessary resource utilization

Overview of Available Technologies

Technology	Detects Inserts	Updates	Deletes	Provides Changed Values	Typical Use
Change Tracking	Yes	Yes	Yes	No	Lightweight synchronization
Change Data Capture	Yes	Yes	Yes	Yes	ETL and replication
Change Event Streaming	Yes	Yes	Yes	Event stream	Event-driven architectures
Azure Functions SQL Trigger	Yes	Yes	Yes	Current row	Serverless processing
Azure Logic Apps	Yes	Yes	Yes	Depends on connector	Workflow automation

Change Data Capture (CDC)

What is CDC?

Change Data Capture records every data modification that occurs within selected database tables.

Unlike Change Tracking, CDC stores:

The type of operation
Before and after values (where applicable)
Transaction information
Log Sequence Numbers (LSNs)
Timestamps

CDC reads changes directly from the SQL Server transaction log instead of requiring application modifications.

How CDC Works

User modifies data.
SQL writes changes to the transaction log.
CDC captures the changes.
Changes are written into CDC system tables.
Applications or ETL tools read the captured changes.

			
Application
      │
      ▼
SQL Table
      │
Transaction Log
      │
      ▼
CDC Capture Process
      │
      ▼
CDC Change Tables
      │
      ▼
ETL / Azure Data Factory / Fabric

		

Information Stored by CDC

For every change, CDC stores:

Insert
Update
Delete
Transaction sequence
Changed columns
Original values
New values
Commit time
Log sequence number

This provides a complete history of modifications.

Advantages of CDC

Minimal application changes

Applications continue performing normal INSERT, UPDATE, and DELETE operations.

Incremental processing

Instead of processing millions of rows:

			
Yesterday:
10 million rows
Today:
Only 1,250 rows changed
CDC processes only 1,250 rows.

		

This dramatically improves ETL performance.

Supports Historical Analysis

CDC retains detailed change history.

Example:

Customer Name

Original:

John Smith

Updated:

John A. Smith

CDC preserves both versions.

Common CDC Use Cases

Azure Data Factory incremental loads
Microsoft Fabric ingestion
Data warehouse updates
Database replication
AI training pipelines
Audit solutions
Event publishing
Synchronizing microservices

Limitations

CDC:

Uses additional storage
Requires SQL Agent jobs (SQL Server)
Introduces some overhead
Retention must be managed
Generates additional transaction log activity

Change Tracking

What is Change Tracking?

Change Tracking is a lightweight feature that records which rows have changed, but does not store the actual changed values.

Instead, it stores metadata indicating:

Row changed
Row deleted
Version number

Applications retrieve the latest row directly from the table.

How Change Tracking Works

Instead of saving old values:

CustomerID 101 changed.

The application retrieves:

			
SELECT *
FROM Customers
WHERE CustomerID = 101

Only the current version is available.

Advantages

Very lightweight.

Minimal storage.

Minimal performance impact.

Simple synchronization.

Fast processing.

Limitations

Cannot determine:

Old value

↓

New value

Only knows:

Row changed

No historical audit.

No before-and-after comparison.

Best Use Cases

Mobile synchronization

Offline applications

Client synchronization

Web applications

Caching

Incremental refresh

Applications only needing current data

CDC vs Change Tracking

Feature	CDC	Change Tracking
Detect Inserts	Yes	Yes
Detect Updates	Yes	Yes
Detect Deletes	Yes	Yes
Stores Old Values	Yes	No
Stores New Values	Yes	No
Historical Data	Yes	No
Storage Usage	Higher	Lower
ETL Friendly	Excellent	Limited
Synchronization	Good	Excellent
Auditing	Excellent	Poor

Choosing Between CDC and Change Tracking

Choose CDC when:

Building ETL pipelines
Loading data warehouses
Creating audit systems
Tracking complete history
AI model retraining
Replication

Choose Change Tracking when:

Synchronizing mobile devices
Synchronizing applications
Detecting row changes only
Performance is critical
History is unnecessary

Change Event Streaming (CES)

What is Change Event Streaming?

Change Event Streaming is an event-driven approach that publishes database changes as events immediately after they occur.

Instead of applications polling for changes:

			
Did anything change?
Did anything change?
Did anything change?

The database immediately emits an event.

Event-Driven Architecture

			
INSERT Order
      │
      ▼
Database
      │
      ▼
Event Published
      │
 ┌────┼────┐
 ▼    ▼    ▼
Function
Logic App
Service Bus

		

One database change can notify many downstream services simultaneously.

Advantages

Near real-time processing

Low latency

Highly scalable

Excellent for cloud-native applications

Supports asynchronous processing

Works well with event hubs and messaging systems

Common Scenarios

Order processing

Inventory updates

Recommendation engines

AI pipelines

Search indexing

Notifications

Microservices

IoT

Streaming analytics

Benefits over Polling

Polling example:

Check database every minute

Potential issues:

Delayed processing
Unnecessary database queries
Higher compute costs

Event streaming:

			
Change occurs
↓
Immediate notification

Much more efficient.

Azure Functions with SQL Trigger Binding

Overview

Azure Functions provide a serverless compute platform capable of automatically executing code when database changes occur.

SQL Trigger Binding enables Azure Functions to react to SQL data modifications without requiring custom polling logic.

Typical workflow:

			
Database Change
↓
SQL Trigger
↓
Azure Function
↓
Business Logic

		

Common Scenarios

Automatically:

Send emails
Generate invoices
Update search indexes
Invoke AI models
Call REST APIs
Update Cosmos DB
Write to Azure Storage
Publish Service Bus messages

Benefits

Serverless

Automatic scaling

Pay only for executions

Minimal infrastructure management

Easy integration with Azure services

Supports event-driven architectures

Example Scenario

A customer places an order.

INSERT Orders

The SQL trigger starts an Azure Function.

The function:

Validates inventory
Sends confirmation email
Updates recommendation engine
Notifies shipping
Publishes event

No manual polling required.

Azure Logic Apps

What Are Logic Apps?

Azure Logic Apps are low-code workflow automation services that integrate SQL databases with hundreds of Microsoft and third-party services.

Rather than writing custom code, workflows are built visually.

Example:

			
SQL Row Updated
↓
Logic App
↓
Teams Notification
↓
Outlook Email
↓
SharePoint Update
↓
CRM Update

		

Common SQL Integrations

SQL Server

Azure SQL Database

Microsoft Dataverse

Dynamics 365

Salesforce

Microsoft Teams

SharePoint

Azure Storage

Azure Service Bus

Azure Event Grid

Power Automate

Typical Workflow

			
Customer Created
↓
Logic App
↓
Create CRM Record
↓
Send Welcome Email
↓
Create Help Desk Ticket
↓
Notify Sales Team

		

Advantages

Low-code

Rapid development

Hundreds of connectors

Visual designer

Built-in retry policies

Error handling

Scheduling

Monitoring

Enterprise integration

Limitations

Logic Apps are ideal for orchestration and workflow automation but are not intended for high-throughput transactional processing where custom code or event streaming solutions may provide better scalability and lower latency.

Choosing the Right Technology

Requirement	Recommended Solution
Incremental ETL	CDC
Data Warehouse Loading	CDC
Audit History	CDC
Mobile Sync	Change Tracking
Cache Refresh	Change Tracking
Event-Driven Processing	Change Event Streaming
Serverless Business Logic	Azure Functions SQL Trigger
Workflow Automation	Azure Logic Apps
AI Pipeline Trigger	Azure Functions or CES
Multi-System Integration	Logic Apps

Best Practices

Enable Only What You Need

Enable CDC or Change Tracking only on tables that require change detection.

Monitor Storage

CDC tables can grow quickly.

Implement retention policies and cleanup jobs.

Prefer Event-Driven Architectures

Avoid continuous polling whenever possible.

Use:

CES
Azure Functions
Event Grid
Service Bus

for scalable cloud-native applications.

Separate Operational and Analytical Workloads

Use CDC to move transactional data into analytical platforms instead of querying production systems directly.

Secure Integration Endpoints

Protect Azure Functions and Logic Apps using:

Microsoft Entra ID
Managed identities
Azure Key Vault
Least privilege access
Network restrictions where appropriate

Monitor Reliability

Track:

Failed executions
Retry attempts
Dead-letter queues
Function failures
Logic App run history
Event delivery failures

DP-800 Exam Tips

Remember these common exam distinctions:

CDC records complete data changes, including inserted, updated, and deleted values, making it ideal for ETL, auditing, and replication.
Change Tracking records only that a row changed, making it a lightweight solution for synchronization scenarios.
Change Event Streaming supports near real-time, event-driven architectures by publishing change events to downstream consumers.
Azure Functions with SQL Trigger Binding are best when database changes should execute custom serverless code automatically.
Azure Logic Apps are the preferred choice for orchestrating business workflows and integrating SQL databases with Azure and third-party services through low-code connectors.
When selecting a technology, evaluate latency requirements, scalability, historical tracking needs, operational overhead, and integration requirements rather than choosing a single solution for every scenario.

Summary

Modern SQL applications extend well beyond traditional databases, serving as event sources for cloud-native architectures, AI pipelines, analytics platforms, and business workflows. Microsoft provides several complementary technologies to detect and process database changes, each optimized for different scenarios.

For the DP-800 exam, you should understand not only how these technologies work, but also when to choose one over another. CDC excels at incremental ETL and auditing, Change Tracking offers lightweight synchronization, Change Event Streaming enables real-time event-driven systems, Azure Functions execute custom business logic in response to changes, and Azure Logic Apps simplify workflow automation across enterprise services.

A solid understanding of these tools will help you design scalable, maintainable, and performant AI-enabled database solutions in Azure.

Practice Exam Questions

Question 1

A company loads data from an Azure SQL Database into a Microsoft Fabric warehouse every hour. The ETL process should retrieve only rows that have changed since the previous load, including the previous and new values of updated rows.

Which technology should you recommend?

A. Change Tracking

B. Change Data Capture (CDC)

C. Azure Logic Apps

D. Azure Functions with SQL Trigger Binding

Correct Answer: B

Explanation

CDC is specifically designed for incremental data movement scenarios. It captures inserts, updates, and deletes directly from the transaction log and stores detailed information about each change, including before and after values where applicable.

Why the other options are incorrect:

A: Change Tracking identifies changed rows but does not store previous values.
C: Logic Apps orchestrate workflows but do not capture database changes.
D: Azure Functions respond to events but are not intended to maintain historical change data for ETL.

Question 2

A mobile application periodically synchronizes customer records with an Azure SQL Database. The application only needs to know which rows have changed since the last synchronization and does not require historical values.

Which feature is most appropriate?

A. Change Event Streaming

B. Azure Functions SQL Trigger

C. Change Tracking

D. CDC

Correct Answer: C

Explanation

Change Tracking is optimized for synchronization scenarios. It records that rows have changed while minimizing storage and processing overhead.

Why the other options are incorrect:

A: CES is designed for event-driven architectures.
B: Azure Functions execute custom code rather than maintaining synchronization metadata.
D: CDC stores detailed change history, which is unnecessary here.

Question 3

An online retailer wants every new order inserted into the Orders table to immediately trigger inventory updates, shipping notifications, and fraud detection.

Which solution best supports this requirement?

A. Scheduled polling queries

B. Change Tracking

C. Change Event Streaming (CES)

D. Nightly ETL jobs

Correct Answer: C

Explanation

CES enables near real-time event publishing whenever database changes occur. Multiple downstream systems can subscribe to the same event without repeatedly querying the database.

Why the other options are incorrect:

A: Polling introduces unnecessary latency and database load.
B: Change Tracking is intended for synchronization rather than event processing.
D: Nightly ETL introduces unacceptable delays.

Question 4

A database update should automatically execute custom C# code that calls several REST APIs and writes audit information to Azure Storage.

Which Azure service should you recommend?

A. Azure Functions with SQL Trigger Binding

B. CDC

C. Change Tracking

D. SQL Agent Job

Correct Answer: A

Explanation

Azure Functions with SQL Trigger Binding automatically execute custom code when qualifying database changes occur, making them ideal for serverless business logic.

Why the other options are incorrect:

B: CDC records changes but does not execute code.
C: Change Tracking simply records row modifications.
D: SQL Agent jobs rely on scheduled execution rather than event-driven processing.

Question 5

Which statement correctly compares Change Tracking and Change Data Capture?

A. CDC captures complete change history while Change Tracking records only that rows changed.

B. Change Tracking captures previous values while CDC does not.

C. Both features store identical information.

D. CDC only tracks INSERT operations.

Correct Answer: A

Explanation

CDC stores detailed information about every change, including inserts, updates, deletes, timestamps, and transaction metadata. Change Tracking only identifies which rows have changed.

The remaining options are incorrect because they reverse the capabilities or incorrectly describe CDC.

Question 6

A business analyst wants to automate the following workflow without writing custom code:

Detect a new customer record.
Send an Outlook email.
Post a Microsoft Teams notification.
Update a SharePoint list.

Which solution is the best choice?

A. CDC

B. Azure Logic Apps

C. Change Tracking

D. SQL CLR

Correct Answer: B

Explanation

Azure Logic Apps provide low-code workflow automation with hundreds of built-in connectors, making them ideal for orchestrating business processes across Microsoft services.

Why the other options are incorrect:

A: CDC captures changes but does not automate workflows.
C: Change Tracking only records modified rows.
D: SQL CLR requires custom coding and is not intended for cloud workflow automation.

Question 7

A development team currently polls the database every minute to determine whether new records have been inserted.

What is the primary disadvantage of this design?

A. It reduces database normalization.

B. It prevents indexing.

C. It increases transaction isolation.

D. It generates unnecessary database workload and introduces latency.

Correct Answer: D

Explanation

Polling repeatedly queries the database even when no changes exist, increasing resource consumption while delaying event processing.

Event-driven solutions such as CES or Azure Functions eliminate this inefficiency.

Question 8

Which technology is most appropriate when an organization must maintain a complete historical record of all row changes for regulatory auditing?

A. Azure Logic Apps

B. Change Tracking

C. Change Data Capture

D. Azure Functions

Correct Answer: C

Explanation

CDC preserves detailed information about inserts, updates, deletes, transaction sequence numbers, and timestamps, making it ideal for compliance and auditing.

The other technologies either automate workflows or identify changes without preserving historical values.

Question 9

Which feature is specifically intended to minimize synchronization overhead by storing only metadata about changed rows?

A. Azure Functions SQL Trigger

B. Change Tracking

C. Change Event Streaming

D. Azure Event Grid

Correct Answer: B

Explanation

Change Tracking records lightweight metadata that indicates which rows have changed, allowing applications to retrieve only the latest row versions.

The other options serve different purposes:

Azure Functions execute code.
CES publishes events.
Event Grid distributes events but does not track database modifications.

Question 10

A solution architect is selecting a technology for an event-driven microservices architecture. Multiple independent services must react immediately whenever product inventory changes.

Which solution best satisfies this requirement?

A. Nightly ETL processing

B. Change Tracking

C. Database polling every five minutes

D. Change Event Streaming (CES)

Correct Answer: D

Explanation

CES is designed for event-driven systems where multiple subscribers consume database change events in near real time. It minimizes latency and reduces unnecessary database queries.

Why the other options are incorrect:

A: Nightly processing is far too slow.
B: Change Tracking is intended for synchronization rather than event broadcasting.
C: Polling introduces unnecessary workload and delays.

Exam Tips

For the DP-800 exam, remember these key distinctions:

Change Data Capture (CDC) is best for incremental ETL, auditing, replication, and historical change tracking.
Change Tracking is designed for lightweight synchronization when only the fact that a row changed is needed.
Change Event Streaming (CES) enables near real-time event-driven architectures by publishing database changes to downstream consumers.
Azure Functions with SQL Trigger Binding are ideal for executing custom serverless code in response to database changes.
Azure Logic Apps provide low-code workflow automation for integrating Azure SQL with Microsoft and third-party services.
On the exam, Microsoft often presents multiple technologies that could work. Choose the one that best aligns with the business requirement, considering factors such as latency, historical tracking, automation, scalability, and operational overhead, rather than selecting the most feature-rich option.

Go to the DP-800 Exam Prep Hub main page

azure, Databases, DP-800, Microsoft Certification, SQL August 1, 2026August 1, 2026

Recommend Azure Monitor configurations, including Application Insights and Log Analytics (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%)
   --> Integrate SQL solutions with Azure services
      --> Recommend Azure Monitor configurations, including Application Insights and Log Analytics

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 SQL applications extend far beyond storing and retrieving data. Today’s applications often expose APIs, integrate with AI services, support microservices, and serve users around the world. As systems become more distributed, monitoring application health, database performance, security, and user activity becomes increasingly important.

Azure Monitor is Microsoft’s unified monitoring platform for collecting, analyzing, visualizing, and acting upon telemetry from Azure resources, applications, virtual machines, containers, databases, and on-premises environments. For SQL AI developers preparing for the DP-800 certification, understanding Azure Monitor—and specifically Application Insights and Log Analytics—is essential for designing highly observable, reliable, and performant database solutions.

The DP-800 exam expects candidates to know when and how to recommend monitoring configurations that support troubleshooting, performance optimization, security monitoring, operational excellence, and AI-enabled database applications.

Understanding Azure Monitor

Azure Monitor is a comprehensive monitoring service that provides:

Metrics collection
Log collection
Distributed tracing
Alerting
Dashboards
Workbooks
Performance analytics
Diagnostic settings
Resource health monitoring

Azure Monitor collects telemetry from virtually every Azure service, including:

Azure SQL Database
Azure SQL Managed Instance
SQL Server on Azure VM
Azure App Service
Azure Functions
Azure Kubernetes Service (AKS)
Azure Container Apps
Data API Builder (DAB)
Azure OpenAI
Azure AI Search
Microsoft Fabric
Virtual Machines

Azure Monitor Architecture

A simplified monitoring architecture looks like this:

			
Applications
       │
Databases
       │
Azure Services
       │
Diagnostic Settings
       │
Azure Monitor
       │
 ┌───────────────┐
 │ Metrics       │
 │ Logs          │
 │ Traces        │
 │ Alerts        │
 └───────────────┘
       │
Application Insights
       │
Log Analytics
       │
Dashboards / Alerts / Workbooks

		

Core Azure Monitor Components

Azure Monitor consists of several integrated services.

Metrics

Metrics are numerical measurements collected at regular intervals.

Examples include:

CPU utilization
Memory usage
DTU utilization
vCore utilization
Storage usage
Active sessions
Requests per second
Response times

Metrics are lightweight and optimized for near real-time monitoring.

Logs

Logs contain detailed event information.

Examples:

SQL errors
Login attempts
Application exceptions
API requests
Deadlocks
Security events
Query execution details

Logs support historical analysis and forensic investigations.

Alerts

Azure Monitor alerts notify administrators when predefined conditions occur.

Examples include:

CPU > 80%
Database unavailable
Deadlock detected
Slow API response
Failed deployments
Authentication failures

Alerts can trigger:

Email
SMS
Azure Functions
Logic Apps
Webhooks
ITSM integrations

Dashboards

Dashboards combine metrics and logs into a centralized monitoring view.

Typical dashboard elements include:

Database performance
API latency
Error rates
Availability
Query duration
Resource utilization

What Is Application Insights?

Application Insights is an Azure Monitor feature designed to monitor applications.

It automatically collects telemetry such as:

HTTP requests
Dependencies
SQL calls
Exceptions
Page views
Response times
Availability tests
Distributed traces

Application Insights helps developers understand application behavior rather than infrastructure performance alone.

Telemetry Collected by Application Insights

Application Insights automatically captures:

Requests

Every REST or GraphQL request can be monitored.

Information includes:

URL
Duration
Response code
Success or failure
Timestamp

Dependencies

Dependencies include calls made by applications to external resources.

Examples:

Azure SQL Database
Azure OpenAI
Azure AI Search
Storage Accounts
REST APIs
Service Bus
Cosmos DB

Dependency tracking identifies slow downstream services.

Exceptions

Application Insights records:

SQL exceptions
.NET exceptions
Java exceptions
Node.js exceptions
Python exceptions

Developers can investigate stack traces and failure frequency.

Performance Counters

Examples include:

CPU
Memory
Thread count
Request queue
Process utilization

Availability Tests

Availability tests periodically verify that applications remain accessible.

Types include:

URL ping tests
Multi-step web tests (legacy)
Standard availability tests

Useful for:

REST APIs
Data API Builder endpoints
Web applications

Distributed Tracing

Modern applications often involve:

Application

↓

REST API

↓

Data API Builder

↓

Azure SQL Database

↓

Azure OpenAI

↓

Azure AI Search

Application Insights correlates all these operations into a single transaction, allowing developers to trace requests end-to-end.

Benefits include:

Root cause analysis
Performance bottleneck identification
Dependency tracking
Service latency analysis

What Is Log Analytics?

Log Analytics is Azure Monitor’s centralized log repository and query engine.

Logs from multiple Azure resources are stored in a Log Analytics Workspace.

Examples include:

SQL diagnostics
Application Insights logs
Azure Activity Logs
VM logs
Azure Firewall logs
Microsoft Defender logs

Log Analytics Workspaces

A Log Analytics Workspace stores telemetry collected across Azure.

Benefits include:

Centralized logging
Long-term retention
Cross-resource analysis
Kusto Query Language (KQL) support
Security investigations

Multiple Azure resources can send data to a single workspace.

Kusto Query Language (KQL)

Log Analytics uses KQL for querying data.

Example:

			
requests
| where success == false
| order by timestamp desc

Example:

			
dependencies
| summarize avg(duration) by target

Example:

			
exceptions
| summarize count() by type

The DP-800 exam expects familiarity with Log Analytics and awareness that KQL is the query language used to analyze collected telemetry.

Diagnostic Settings

Azure resources send telemetry through Diagnostic Settings.

Diagnostic Settings determine where logs are stored.

Possible destinations include:

Log Analytics Workspace
Storage Account
Event Hub
Partner solutions

For Azure SQL Database, diagnostic logs commonly include:

SQLInsights
Automatic tuning
Deadlocks
Query Store Runtime Statistics
Errors
Wait statistics
Timeouts

Monitoring Azure SQL Database

Important Azure SQL metrics include:

CPU percentage
DTU percentage
vCore utilization
Data IO
Log IO
Storage percentage
Sessions
Workers
Connections

These metrics help identify capacity issues before users experience failures.

Monitoring Data API Builder (DAB)

DAB deployments should enable:

Request logging
Response times
Authentication failures
GraphQL execution errors
REST endpoint usage
SQL dependency tracking

Application Insights provides excellent visibility into DAB performance.

Monitoring AI-Enabled SQL Applications

Applications integrating Azure OpenAI or Azure AI Search should monitor:

API latency
Request failures
Token usage (where available)
Dependency duration
Timeout frequency
Retry attempts

Dependency tracking in Application Insights helps identify whether delays originate from the database or external AI services.

Azure Monitor Alerts

Common production alerts include:

Condition	Alert
CPU > 80%	Warning
DTU > 90%	Critical
Deadlock detected	Critical
Failed SQL login	Security
API response > 2 seconds	Warning
Storage > 85%	Capacity alert
Application unavailable	Critical

Alerts should prioritize actionable events while minimizing alert fatigue.

Workbooks

Azure Monitor Workbooks create interactive reports using:

Metrics
Logs
Charts
Maps
Tables
KQL queries

Typical workbook examples:

SQL performance dashboard
API performance trends
AI service latency
Database growth analysis
Security monitoring

Retention Policies

Organizations should configure log retention based on:

Compliance requirements
Storage costs
Investigation needs
Security policies

Short retention reduces storage costs, while longer retention supports audits and forensic analysis.

Best Practices for Monitoring SQL Solutions

Microsoft recommends:

Enable Application Insights for applications.
Send diagnostic logs to Log Analytics.
Enable distributed tracing.
Configure proactive alerts.
Monitor dependencies.
Use dashboards for operational visibility.
Review telemetry regularly.
Monitor failed authentication attempts.
Monitor slow SQL queries.
Use KQL for troubleshooting.

Common DP-800 Exam Scenarios

You may be asked to determine:

Which monitoring service collects application telemetry.
When to use Application Insights versus Log Analytics.
How to troubleshoot slow SQL queries.
Which service stores centralized logs.
How to monitor Data API Builder.
Which service provides distributed tracing.
How to configure alerts for production systems.
Which Azure Monitor feature supports long-term log analysis.

DP-800 Exam Tips

Remember these key points:

Azure Monitor is the overarching monitoring platform.
Application Insights monitors application performance and dependencies.
Log Analytics centralizes logs and supports KQL queries.
Diagnostic Settings send Azure resource logs to destinations such as Log Analytics.
Application Insights supports distributed tracing.
Azure Monitor Alerts automate operational notifications.
Workbooks provide customizable dashboards and reports.
Azure SQL Database metrics help identify capacity and performance issues.
Use Application Insights to monitor Data API Builder and AI-enabled applications.
KQL is the primary language for querying Log Analytics data.

Practice Exam Questions

Question 1

A company wants to monitor the performance of a .NET application that accesses Azure SQL Database through Data API Builder. The solution must automatically capture request latency, SQL dependencies, exceptions, and distributed traces.

Which Azure service should you recommend?

A. Azure Storage Explorer

B. Azure Monitor Metrics

C. Application Insights

D. Azure Advisor

Answer: C

Explanation: Application Insights is designed to monitor application performance by collecting requests, dependencies, exceptions, distributed traces, and performance telemetry automatically.

Question 2

Your organization needs a centralized repository for logs collected from Azure SQL Database, Azure App Service, Azure Functions, and Application Insights.

Which Azure service should you use?

A. Azure Log Analytics Workspace

B. Azure Backup

C. Azure Key Vault

D. Azure Files

Answer: A

Explanation: A Log Analytics Workspace provides centralized storage and analysis for telemetry collected from multiple Azure resources.

Question 3

An administrator wants to query failed HTTP requests over the past 24 hours using Kusto Query Language (KQL).

Which Azure service provides this capability?

A. Azure Portal Metrics Explorer

B. Azure Cost Management

C. Azure Monitor Alerts

D. Log Analytics

Answer: D

Explanation: Log Analytics stores log data and enables querying through Kusto Query Language (KQL) for detailed analysis and troubleshooting.

Question 4

A development team wants to receive an email whenever Azure SQL Database CPU utilization exceeds 85% for more than five minutes.

Which Azure Monitor feature should be configured?

A. Diagnostic Settings

B. Azure Policy

C. Azure Monitor Alerts

D. Application Insights Availability Tests

Answer: C

Explanation: Azure Monitor Alerts evaluate metric or log conditions and can notify administrators through email, SMS, webhooks, or automated workflows.

Question 5

Which Azure Monitor feature is responsible for routing Azure SQL Database diagnostic logs to a Log Analytics Workspace?

A. Azure Monitor Metrics

B. Diagnostic Settings

C. Availability Tests

D. Resource Locks

Answer: B

Explanation: Diagnostic Settings configure where Azure resource logs are sent, including Log Analytics Workspaces, Storage Accounts, and Event Hubs.

Question 6

A developer needs to identify which downstream dependency is causing increased response times in an AI-enabled application.

Which Application Insights capability should they use?

A. Backup Reports

B. Dependency Tracking

C. Cost Analysis

D. Resource Graph

Answer: B

Explanation: Dependency Tracking records calls to Azure SQL Database, Azure OpenAI, Azure AI Search, REST APIs, and other services, making it easier to identify performance bottlenecks.

Question 7

Your organization wants to monitor whether a public REST endpoint remains accessible from multiple geographic regions.

Which Application Insights feature is most appropriate?

A. Live Metrics

B. Snapshot Debugger

C. Availability Tests

D. Smart Detection

Answer: C

Explanation: Availability Tests periodically check endpoint accessibility and response times from multiple locations, helping detect outages before users report them.

Question 8

Which Azure Monitor capability provides end-to-end visibility by correlating requests across multiple services such as Data API Builder, Azure SQL Database, and Azure OpenAI?

A. Azure Advisor

B. Distributed Tracing

C. Cost Management

D. Azure Policy

Answer: B

Explanation: Distributed Tracing correlates operations across application components, enabling developers to follow a single request through multiple services and identify performance bottlenecks.

Question 9

A database administrator wants to build an interactive dashboard that combines charts, tables, KQL queries, and performance metrics into a single operational view.

Which Azure Monitor feature should be recommended?

A. Azure Workbooks

B. Azure Bastion

C. Microsoft Purview

D. Azure Resource Graph

Answer: A

Explanation: Azure Workbooks create interactive monitoring dashboards that combine metrics, logs, charts, visualizations, and KQL queries for operational reporting.

Question 10

An organization wants to monitor a production SQL solution while minimizing unnecessary notifications that could overwhelm administrators.

Which recommendation represents a monitoring best practice?

A. Generate alerts for every informational event.

B. Disable monitoring during peak usage.

C. Configure actionable alerts based on meaningful thresholds and business impact.

D. Collect only CPU metrics.

Answer: C

Explanation: Effective monitoring focuses on actionable alerts that indicate genuine operational issues. Carefully chosen thresholds reduce alert fatigue while ensuring that critical events receive timely attention.

Go to the DP-800 Exam Prep Hub main page

azure, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Expose database objects, stored procedures, and views, including GraphQL relationships (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%)
   --> Integrate SQL solutions with Azure services
      --> Expose database objects, stored procedures, and views, including GraphQL relationships

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 applications rarely communicate directly with a database. Instead, they interact with APIs that expose only the data and operations that applications require. Microsoft’s Data API builder (DAB) provides a secure and efficient way to expose Azure SQL Database, Azure SQL Managed Instance, SQL Server, and Azure Database for PostgreSQL as REST and GraphQL APIs without requiring developers to build custom API services.

One of the primary responsibilities of a SQL AI Developer is deciding which database objects should be exposed, how they should be exposed, and how relationships between entities should be represented, particularly in GraphQL.

For the DP-800 exam, candidates should understand how to expose:

Tables
Views
Stored procedures
Relationships between entities
GraphQL navigation
REST resources
Security considerations
Performance considerations

Why Expose Database Objects?

Instead of allowing applications to connect directly to a database, organizations commonly expose selected database objects through APIs because APIs provide:

Better security
Controlled access
Versioning
Authentication
Authorization
Business logic abstraction
Simplified client development

Rather than allowing direct SQL access, applications interact with HTTP endpoints such as:

GET /api/Products

or GraphQL queries like:

			
query {
  products {
    ProductID
    Name
    Price
  }
}

		

Objects That Can Be Exposed

Microsoft Data API builder can expose several database object types.

1. Tables

Tables are the most common objects exposed.

Example:

			
Products
Customers
Orders
Employees

Each table becomes an entity.

Example DAB configuration:

			
{
  "entities": {
    "Products": {
      "source": "Products"
    }
  }
}

		

REST endpoints generated:

			
GET /api/Products
POST /api/Products
PATCH /api/Products
DELETE /api/Products

GraphQL automatically generates:

			
products
product_by_pk

and corresponding mutations.

2. Views

Views provide a secure way to expose pre-filtered or joined data.

Example:

vwSalesSummary

Instead of exposing many tables, clients consume the view.

Benefits include:

Simplified queries
Hidden table structure
Security abstraction
Read-only reporting

Example:

			
CustomerName
OrderCount
TotalSales

instead of requiring joins.

Views are especially useful for reporting applications.

3. Stored Procedures

Stored procedures expose business logic rather than raw tables.

Example:

EXEC usp_CreateOrder

Instead of allowing clients to insert rows manually.

Advantages include:

Validation
Business rules
Transactions
Consistent processing

Data API builder supports stored procedures as API operations.

Example REST endpoint:

POST /api/CreateOrder

Why Use Stored Procedures?

Stored procedures provide:

Better security
Centralized business rules
Reduced network traffic
Transaction handling
Parameter validation

Example:

Instead of:

			
Insert Order
Insert Items
Update Inventory
Calculate Discount
Commit Transaction

		

The application calls:

CreateOrder()

The stored procedure performs every operation safely.

Exposing Views vs Tables

Tables	Views
Raw data	Processed data
Often updateable	Often read-only
Complete schema	Simplified schema
Less abstraction	Greater abstraction
Better for CRUD	Better for reporting

Exposing Stored Procedures

Stored procedures typically become REST POST operations because they execute actions.

Example:

			
POST
/api/ProcessPayment

Input:

			
{
   "OrderID":1054
}

The procedure performs the transaction.

GraphQL Relationships

One of GraphQL’s greatest advantages is navigating relationships between entities.

Instead of making several REST calls:

			
Customers
Orders
OrderDetails

GraphQL can retrieve all related information in one request.

Example:

			
query {
  customers {
    CustomerName
    orders {
        OrderID
        OrderDate
        orderDetails {
             ProductName
             Quantity
        }
    }
}

		

GraphQL traverses relationships automatically.

Understanding Relationships

Suppose the database contains:

			
Customers
Orders
Products
OrderDetails

Relationships:

			
Customer
   |
   | 1:M
   |
Orders
   |
   | 1:M
   |
OrderDetails
   |
   | M:1
   |
Products

		

GraphQL follows these relationships naturally.

One-to-Many Relationships

Example:

Customer

↓

Orders

Example query:

			
query{
 customers{
   CustomerName
   orders{
      OrderID
      OrderDate
   }
 }
}

		

The response includes each customer’s orders.

Many-to-One Relationships

Example:

OrderDetails

↓

Product

			
query{
 orderDetails{
    Quantity
    product{
       Name
       Price
    }
 }
}

		

Many-to-Many Relationships

Many-to-many relationships are typically implemented through junction tables.

Example:

			
Students
Courses
StudentCourses

GraphQL can expose navigation through the junction table.

REST vs GraphQL for Relationships

REST

			
GET Customers
GET Orders
GET OrderDetails

Multiple requests required.

GraphQL

One query retrieves everything.

Advantages:

Reduced network traffic
Less over-fetching
Less under-fetching
Better performance

Relationship Configuration in Data API Builder

Relationships are defined inside the configuration.

Example concept:

			
Customers
   hasMany
Orders

and

			
Orders
belongsTo
Customers

This allows nested GraphQL queries.

CRUD Support

Depending on configuration, exposed entities may support:

Create

POST

Read

GET

Update

			
PUT
PATCH

Delete

DELETE

Not every entity must support every operation.

For example:

Views

Read Only

Tables

Read + Write

Restricting Exposed Objects

Best practice is not to expose every table.

Expose only:

Required tables
Required views
Required procedures

Avoid exposing:

Audit tables
Internal configuration
Security tables
Temporary tables
Logging tables

Least privilege always applies.

Security Considerations

When exposing database objects:

Require HTTPS
Use Microsoft Entra authentication
Apply least privilege
Use role-based authorization
Expose only necessary objects
Validate procedure parameters
Avoid exposing sensitive columns
Audit endpoint usage

Performance Considerations

Good API design includes:

Return only needed fields
Use pagination
Cache reference data
Optimize SQL queries
Index frequently queried columns
Avoid unnecessary nested GraphQL queries
Use views for complex reporting

Common DP-800 Exam Tips

Know when to expose:

Object	Typical Use
Table	CRUD operations
View	Reporting and simplified queries
Stored Procedure	Business logic and transactions
GraphQL Relationship	Nested related data
REST Endpoint	Resource-oriented operations

Summary

For the DP-800 exam, you should understand that Data API builder can expose tables, views, and stored procedures as secure REST and GraphQL endpoints. Tables are commonly used for CRUD operations, views simplify reporting and hide underlying schemas, and stored procedures encapsulate business logic and transactional operations. GraphQL relationships allow clients to traverse related entities in a single request, reducing network calls and simplifying application development. Developers should expose only the objects required by the application, apply least-privilege security principles, and optimize endpoints for performance and maintainability.

Practice Exam Questions

Question 1

Your organization wants external applications to retrieve product information without exposing the underlying table structure or requiring complex joins. Which database object should you expose?

A. A view

B. A database trigger

C. A SQL Agent job

D. A temporary table

Correct Answer:

A. A view

Explanation

Views present a simplified, controlled representation of data by encapsulating joins and filters. They hide the underlying schema, making them ideal for reporting and read-only access. Triggers, SQL Agent jobs, and temporary tables are not intended to expose data to applications.

Question 2

Which type of database object is best suited for encapsulating business logic that performs multiple database operations within a single transaction?

A. A view

B. A stored procedure

C. A synonym

D. An index

Correct Answer:

B. A stored procedure

Explanation

Stored procedures centralize business logic, validate inputs, manage transactions, and execute multiple SQL statements as a single unit of work. Views are primarily for querying data, while synonyms and indexes do not execute business logic.

Question 3

An application uses GraphQL to retrieve customer information and all associated orders in a single request.

Which GraphQL capability makes this possible?

A. Automatic indexing

B. HTTP caching

C. Entity relationships

D. SQL triggers

Correct Answer:

C. Entity relationships

Explanation

GraphQL relationships allow clients to traverse related entities through nested queries, enabling retrieval of customers and their orders in a single request. This is one of GraphQL’s primary advantages over traditional REST APIs.

Question 4

A developer exposes a database table through Data API builder and wants clients to retrieve records using REST.

Which HTTP method should clients use?

A. DELETE

B. PATCH

C. POST

D. GET

Correct Answer:

D. GET

Explanation

REST uses the GET method to retrieve resources. POST creates resources, PATCH updates existing resources, and DELETE removes resources.

Question 5

Which object is most appropriate for exposing aggregated sales totals without allowing users to modify the underlying data?

A. A stored procedure

B. A table

C. A view

D. A trigger

Correct Answer:

C. A view

Explanation

Views are commonly used to expose aggregated or summarized information while hiding the complexity of the underlying tables. Many reporting views are read-only, preventing accidental modifications.

Question 6

A Data API builder configuration includes only the Products and Categories entities.

What happens if a client attempts to access the Employees table?

A. The request succeeds because all tables are exposed automatically.

B. The table is exposed only through GraphQL.

C. The request fails because Employees is not configured as an exposed entity.

D. Data API builder creates the endpoint automatically.

Correct Answer:

C. The request fails because Employees is not configured as an exposed entity.

Explanation

Data API builder exposes only the entities explicitly defined in its configuration. Objects not configured remain inaccessible through both REST and GraphQL endpoints.

Question 7

Why should developers avoid exposing every database table through REST or GraphQL endpoints?

A. Because GraphQL cannot access multiple tables.

B. To follow the principle of least privilege and reduce security risks.

C. Because Data API builder supports only five entities.

D. To improve SQL syntax compatibility.

Correct Answer:

B. To follow the principle of least privilege and reduce security risks.

Explanation

Exposing only required objects reduces the attack surface, protects sensitive data, and aligns with security best practices. Internal, audit, configuration, and security tables should generally remain inaccessible.

Question 8

Which GraphQL feature reduces the need for multiple REST API calls when retrieving related data?

A. Stored procedures

B. Pagination

C. HTTP status codes

D. Nested queries using relationships

Correct Answer:

D. Nested queries using relationships

Explanation

GraphQL allows nested queries that follow entity relationships, enabling clients to retrieve related objects in a single request. This minimizes network traffic and simplifies application development.

Question 9

Which database object is generally the best choice for exposing an operation that validates inventory, creates an order, updates stock levels, and commits the transaction?

A. A stored procedure

B. A view

C. A nonclustered index

D. A foreign key

Correct Answer:

A. A stored procedure

Explanation

Stored procedures encapsulate complex business processes, ensure transactional consistency, and centralize business rules. Views and indexes cannot perform transactional workflows.

Question 10

A GraphQL query retrieves customer information along with orders and order details.

What is the primary benefit of this approach compared to making several REST requests?

A. SQL Server automatically creates indexes.

B. Database permissions are no longer required.

C. Authentication becomes optional.

D. Multiple related resources can be retrieved in a single request, reducing network overhead.

Correct Answer:

D. Multiple related resources can be retrieved in a single request, reducing network overhead.

Explanation

GraphQL enables clients to retrieve exactly the required data—including related entities—in a single query. This reduces round trips, minimizes over-fetching and under-fetching, and often improves application performance.

Go to the DP-800 Exam Prep Hub main page

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

Configure REST or GraphQL endpoints (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%)
   --> Integrate SQL solutions with Azure services
      --> Configure REST or GraphQL endpoints

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 applications rarely connect directly to a database. Instead, they communicate with APIs that provide a secure, scalable, and well-defined interface for accessing and modifying data. Microsoft Data API Builder (DAB) simplifies this process by automatically exposing SQL Server and Azure SQL Database objects through REST and GraphQL endpoints with minimal custom code.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, candidates should understand how to configure and secure REST and GraphQL endpoints, determine when each API style is appropriate, configure authentication and authorization, expose database objects as entities, and optimize endpoint performance.

This topic builds on previous areas such as configuring entities and Data API Builder configuration files. While entities define what database objects are exposed, endpoints determine how applications interact with those objects.

Learning Objectives

After completing this topic, you should be able to:

Explain the purpose of REST and GraphQL endpoints.
Understand how Data API Builder exposes SQL data.
Configure REST endpoints.
Configure GraphQL endpoints.
Understand endpoint routing.
Configure CRUD operations.
Secure API endpoints.
Implement authentication and authorization.
Optimize endpoint performance.
Choose between REST and GraphQL for various scenarios.
Troubleshoot common endpoint issues.

Why APIs Are Important

Without APIs:

			
Application
↓
Direct Database Connection
↓
SQL Database

		

Applications require:

Database credentials
Knowledge of table structures
SQL query logic
Network connectivity to the database

This approach introduces security and maintenance challenges.

With Data API Builder:

			
Application
↓
REST / GraphQL API
↓
Data API Builder
↓
Azure SQL Database

		

Benefits include:

Simplified development
Better security
Centralized authentication
Controlled data exposure
Consistent API design
Easier scalability

Understanding REST

REST (Representational State Transfer) is an architectural style that exposes resources through HTTP methods.

Common HTTP verbs include:

Method	Purpose
GET	Retrieve data
POST	Create data
PUT	Replace an existing resource
PATCH	Update part of a resource
DELETE	Remove data

Example:

GET /api/products

returns:

			
[
  {
    "ProductID":1,
    "Name":"Laptop"
  }
]

		

REST Endpoint Structure

Typical endpoint format:

https://server/api/entity

Examples:

			
GET /api/customers
GET /api/orders
POST /api/products
PATCH /api/orders/25
DELETE /api/customers/10

		

REST uses URLs to identify resources.

Understanding GraphQL

GraphQL is a query language developed to allow clients to request exactly the data they require.

Unlike REST, GraphQL typically uses a single endpoint.

Example:

/graphql

The client submits queries.

Example:

			
query {
  products {
    Name
    Price
  }
}

		

Only the requested fields are returned.

REST vs. GraphQL

Feature	REST	GraphQL
Endpoints	Multiple	Usually one
Data returned	Fixed by endpoint	Client specifies fields
Over-fetching	Possible	Minimized
Under-fetching	Possible	Rare
CRUD support	Native HTTP verbs	Queries and mutations
Learning curve	Lower	Slightly higher
Caching	Excellent HTTP support	More complex

Neither approach is universally better.

Microsoft expects developers to choose the appropriate API based on application requirements.

Data API Builder Architecture

Data API Builder sits between applications and the database.

			
Application
↓
REST / GraphQL
↓
Data API Builder
↓
Azure SQL Database

		

Responsibilities include:

Endpoint generation
Authentication
Authorization
SQL execution
CRUD operations
Entity mapping
Relationship handling

Configuring REST Endpoints

REST endpoints are enabled within the Data API Builder configuration.

Developers specify:

Entity
Source table
Permissions
Allowed operations

Example concept:

			
Entity
↓
Customers
↓
REST Enabled

		

Automatically creates endpoints similar to:

			
GET /api/customers
POST /api/customers
PATCH /api/customers/15
DELETE /api/customers/15

Configuring GraphQL Endpoints

When GraphQL is enabled, Data API Builder generates a GraphQL schema automatically.

Example query:

			
query {
  customers {
      CustomerName
      City
  }
}

		

Mutation example:

			
mutation {
  createCustomer(...)
}

Developers do not manually write the GraphQL schema.

Endpoint Routing

Routing determines how incoming requests reach the appropriate entity.

REST example:

/api/products

Routes to:

Products Entity

GraphQL example:

/GraphQL

Routes all requests through:

GraphQL Engine

The GraphQL engine determines which entities participate in the query.

CRUD Operations

Data API Builder supports CRUD operations.

Operation	REST	GraphQL
Create	POST	Mutation
Read	GET	Query
Update	PATCH/PUT	Mutation
Delete	DELETE	Mutation

Organizations often disable unnecessary operations.

Example:

Internal reporting API:

Allowed:

Disabled:

POST
PATCH
DELETE

This reduces security risks.

Endpoint Configuration Best Practices

Microsoft recommends exposing only the endpoints required by the application.

Good practices include:

Enable only necessary entities.
Disable unnecessary CRUD operations.
Hide internal tables.
Use descriptive endpoint names.
Keep URL structures consistent.
Avoid exposing sensitive objects.

Authentication

Authentication answers:

Who is making the request?

Common authentication methods include:

Microsoft Entra ID
Managed Identity
JWT Bearer Tokens
OAuth 2.0
API keys (where appropriate)

Microsoft strongly recommends Microsoft Entra ID for Azure-hosted solutions.

Microsoft Entra ID Integration

Data API Builder integrates with Microsoft Entra ID.

Authentication flow:

			
User
↓
Microsoft Entra ID
↓
Access Token
↓
REST / GraphQL
↓
Data API Builder
↓
Azure SQL Database

		

Benefits include:

Single sign-on
Central identity management
Multi-factor authentication
Conditional Access
Token-based authentication

Authorization

Authentication determines identity.

Authorization determines permissions.

Example:

Developer:

Can:

Read
Update

Auditor:

Can:

Read only

Guest:

Can:

View public data only

Authorization should follow the principle of least privilege.

Endpoint Security

APIs should never expose more information than necessary.

Security recommendations include:

Use HTTPS exclusively.
Require authentication.
Use Microsoft Entra ID where possible.
Implement role-based authorization.
Validate client input.
Disable unused operations.
Avoid exposing sensitive columns.
Log API access.
Monitor suspicious activity.

Protecting Sensitive Data

Poor API:

			
Employee
Name
Salary
SSN
PasswordHash

		

Better API:

			
Employee
Name
Department
Title

Sensitive fields should remain inaccessible.

Often this is accomplished through:

Database views
Entity configuration
Role-based permissions

Error Handling

REST commonly returns HTTP status codes.

Examples:

Code	Meaning
200	Success
201	Created
400	Bad Request
401	Unauthorized
403	Forbidden
404	Not Found
500	Internal Server Error

Applications should use these responses to handle failures appropriately.

GraphQL Error Responses

GraphQL responses may contain both successful data and error information.

Example concept:

			
{
  "data": {
      "products": null
  },
  "errors": [
      {
          "message":"Unauthorized"
      }
  ]
}

		

Unlike REST, GraphQL often returns HTTP 200 while including error details in the response body.

Developers should inspect both the HTTP status code and the GraphQL response payload.

Performance Considerations

Well-designed endpoints improve application performance.

Recommendations include:

Return only required data.
Filter data at the database.
Use pagination.
Cache relatively static responses.
Index frequently searched columns.
Avoid returning excessively large result sets.
Reduce unnecessary joins.

GraphQL helps minimize over-fetching because clients specify the required fields.

REST Performance

REST benefits from mature HTTP infrastructure.

Advantages include:

Browser caching
Proxy caching
Azure Front Door
Azure API Management caching
CDN support

REST is often preferred for:

Public APIs
High-volume read workloads
Static content
Mobile applications

GraphQL Performance

GraphQL reduces unnecessary network traffic.

Instead of:

			
GET Customer
GET Orders
GET Products

A single GraphQL query can retrieve all related information.

Example:

			
{
  customer(id:1){
      Name
      Orders{
          OrderDate
          Total
      }
  }
}

		

This minimizes the number of client-server round trips.

Monitoring Endpoints

Production APIs should be monitored continuously.

Useful Azure services include:

Azure Monitor
Application Insights
Log Analytics
Azure API Management analytics

Monitor:

Request counts
Response times
Error rates
Authentication failures
Throughput
Latency

These metrics help identify bottlenecks and security issues.

Common DP-800 Exam Scenarios

You should be comfortable answering questions such as:

When should REST be preferred over GraphQL?
When is GraphQL more efficient than REST?
How are CRUD operations exposed through each API style?
Why should unnecessary CRUD operations be disabled?
Which authentication mechanism is recommended for Azure-hosted APIs?
How should endpoint authorization be implemented?
How can API performance be improved?
Why is HTTPS required?
How does GraphQL reduce over-fetching?
What monitoring information should be collected for production APIs?

DP-800 Exam Tips

Know the differences between REST and GraphQL.
Understand how Data API Builder automatically generates endpoints.
Remember that REST typically uses multiple endpoints, while GraphQL commonly uses a single endpoint.
Understand the mapping between CRUD operations and HTTP verbs.
Recognize the importance of Microsoft Entra ID authentication.
Apply least-privilege authorization principles.
Use HTTPS for all endpoint communication.
Know when GraphQL reduces over-fetching and when REST benefits from HTTP caching.
Understand how endpoint configuration affects security, scalability, and performance.

Summary

Configuring REST and GraphQL endpoints is a core competency for developers building modern SQL-backed applications with Microsoft Data API Builder. REST provides resource-oriented endpoints that align naturally with HTTP methods and benefit from widespread tooling and caching support. GraphQL offers a flexible query model that enables clients to retrieve exactly the data they need, reducing over-fetching and minimizing network traffic.

For the DP-800 exam, candidates should understand how Data API Builder automatically generates these endpoints from configured entities, how CRUD operations map to each API style, how to secure endpoints using Microsoft Entra ID and role-based authorization, and how to optimize performance through pagination, filtering, caching, and efficient query design. Mastering these concepts enables developers to build secure, scalable, and maintainable APIs that integrate SQL databases with modern cloud-native applications.

Practice Exam Questions

Question 1

You are deploying Microsoft Data API builder in front of an Azure SQL Database. The security team requires that users authenticate with Microsoft Entra ID before accessing either the REST or GraphQL endpoints.

Which authentication provider should you configure?

A. Anonymous authentication

B. Microsoft Entra ID authentication

C. Basic Authentication

D. SQL Authentication

Correct Answer:

B. Microsoft Entra ID authentication

Explanation

Microsoft Entra ID (formerly Azure Active Directory) is Microsoft’s recommended authentication mechanism for cloud services. Data API builder supports Microsoft Entra ID authentication, enabling secure token-based authentication for both REST and GraphQL endpoints.

Why the other answers are incorrect:

A: Anonymous authentication provides no identity validation.
C: Basic authentication transmits usernames and passwords and is generally discouraged.
D: SQL Authentication secures the database connection but is not intended for authenticating API consumers.

Question 2

A development team wants consumers of a REST endpoint to retrieve data using standard HTTP semantics.

Which HTTP method should clients use when reading data?

A. POST

B. PUT

C. GET

D. DELETE

Correct Answer:

C. GET

Explanation

REST follows standard HTTP conventions.

GET retrieves data.
POST creates resources.
PUT replaces existing resources.
DELETE removes resources.

Using the appropriate HTTP method improves interoperability and aligns with REST best practices.

Question 3

A GraphQL endpoint exposes Customer information.

A client application only requires the customer’s first name and email address.

What is the primary advantage of GraphQL in this scenario?

A. GraphQL automatically encrypts returned data.

B. GraphQL always executes faster than REST.

C. GraphQL allows clients to request only the required fields.

D. GraphQL eliminates authentication requirements.

Correct Answer:

C. GraphQL allows clients to request only the required fields.

Explanation

GraphQL enables clients to specify exactly which fields should be returned, reducing unnecessary data transfer and improving application efficiency.

The other options are incorrect because:

GraphQL does not provide encryption.
Performance depends on workload.
Authentication remains necessary.

Question 4

An organization wants to expose only the Products table through Data API builder.

The Orders and Customers tables must never be accessible.

What is the best configuration?

A. Configure only the Products entity in the DAB configuration.

B. Create views for all tables.

C. Grant db_owner permissions.

D. Disable GraphQL.

Correct Answer:

A. Configure only the Products entity in the DAB configuration.

Explanation

Only configured entities become accessible through DAB endpoints. Tables not defined in the configuration cannot be queried through the generated APIs.

Granting broad database permissions or disabling GraphQL does not prevent REST access.

Question 5

A developer receives HTTP 401 Unauthorized when calling a secured REST endpoint.

Which issue is the most likely cause?

A. The endpoint uses HTTPS.

B. The client failed to provide a valid authentication token.

C. The SQL query contains joins.

D. Pagination is enabled.

Correct Answer:

B. The client failed to provide a valid authentication token.

Explanation

HTTP 401 indicates that authentication failed or credentials were not supplied.

Typical causes include:

Missing bearer token
Expired token
Invalid token
Incorrect authentication configuration

The remaining options are unrelated to authentication failures.

Question 6

Your organization wants GraphQL clients to create new database records.

Which GraphQL operation should the clients perform?

A. Query

B. Subscription

C. Mutation

D. Schema

Correct Answer:

C. Mutation

Explanation

GraphQL defines three primary operation types:

Query → Read data
Mutation → Insert, update, or delete data
Subscription → Receive real-time updates (where supported)

Creating records is accomplished using mutations.

Question 7

An application experiences slower response times because every request repeatedly retrieves identical reference data.

Which feature would most likely improve endpoint performance?

A. Increase SQL authentication timeout.

B. Enable response caching where appropriate.

C. Replace GraphQL with SOAP.

D. Disable indexes.

Correct Answer:

B. Enable response caching where appropriate.

Explanation

Caching reduces repeated database reads for frequently requested data.

Benefits include:

Lower latency
Reduced database workload
Improved scalability

Disabling indexes would significantly reduce performance.

Question 8

Which statement best describes GraphQL schemas?

A. They define the structure of available queries, mutations, and data types.

B. They replace SQL indexes.

C. They encrypt REST endpoints.

D. They create database backups.

Correct Answer:

A. They define the structure of available queries, mutations, and data types.

Explanation

The GraphQL schema acts as the contract between clients and the API.

It specifies:

Available object types
Fields
Queries
Mutations
Relationships

It does not manage indexing, encryption, or backups.

Question 9

Your organization deploys Data API builder to production.

Which practice best protects REST and GraphQL endpoints?

A. Enable anonymous access for easier testing.

B. Store secrets directly in configuration files.

C. Require HTTPS and strong authentication.

D. Disable authorization checks.

Correct Answer:

C. Require HTTPS and strong authentication.

Explanation

Production APIs should always:

Use HTTPS
Authenticate users
Authorize requests
Protect credentials
Follow least-privilege principles

Anonymous access and embedded secrets introduce significant security risks.

Question 10

A developer modifies a Data API builder configuration file by adding a new entity.

What must occur before clients can use the new endpoint?

A. Restart or redeploy the Data API builder service so the updated configuration is loaded.

B. Rebuild the Azure SQL Database.

C. Delete the GraphQL schema.

D. Recreate the database indexes.

Correct Answer:

A. Restart or redeploy the Data API builder service so the updated configuration is loaded.

Explanation

After modifying the DAB configuration, the running service must reload the updated configuration. Depending on the hosting environment, this typically involves restarting the application or redeploying the container or service.

Database rebuilding, deleting the GraphQL schema, and recreating indexes are unrelated to exposing newly configured endpoints.

Exam Tips for DP-800

For the exam, you should be comfortable with:

Configuring REST and GraphQL endpoints using Microsoft Data API builder.
Understanding REST HTTP methods (GET, POST, PUT/PATCH, DELETE).
Understanding GraphQL queries, mutations, and schemas.
Configuring Microsoft Entra ID authentication.
Applying authorization using database permissions and DAB configuration.
Exposing only intended database objects.
Using HTTPS to secure endpoint communications.
Improving performance through caching and efficient endpoint design.
Deploying configuration changes safely.
Understanding the differences and appropriate use cases for REST versus GraphQL.

Go to the DP-800 Exam Prep Hub main page

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

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

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

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

Introduction

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

What Is a SQL Database Project?

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

A SQL Database Project typically contains:

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

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

Why Update the Database Project?

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

Typical changes include:

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

Example:

Original table:

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

		

Business requirement:

Store customer email addresses.

Updated project:

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

		

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

Typical Deployment Workflow

The recommended workflow is:

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

		

This workflow provides consistency, repeatability, and auditability.

Updating Database Objects

Developers modify individual object files.

For example:

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

		

Each object exists as its own SQL file.

Benefits include:

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

Schema Validation

Before deployment, the project should validate successfully.

Validation checks include:

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

Early validation prevents deployment failures.

Building the Project

Once validated, the project is built into a DACPAC.

A DACPAC contains:

Database schema
Metadata
Deployment model

It does not include:

User data
Transaction logs
Database backups

The DACPAC becomes the deployment artifact used throughout the pipeline.

What Happens During Deployment?

Deployment compares:

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

		

The deployment engine generates only the necessary changes.

Example:

Project:

EmailAddress column exists

Target database:

EmailAddress missing

Generated deployment:

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

Declarative Deployment Model

SQL Database Projects use a declarative deployment model.

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

Instead of:

Run these SQL commands.

You define:

The database should look like this.

The deployment engine determines the required SQL statements.

Incremental Deployments

Deployments are incremental.

Only differences are deployed.

If no differences exist:

No deployment changes

If one object changes:

Only that object is updated.

This minimizes deployment time and risk.

Deployment Reports

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

The report identifies:

New objects
Modified objects
Removed objects
Security changes
Dependency changes

Reviewing the report before production deployment is a best practice.

Deployment Scripts

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

Benefits include:

DBA review
Change approval
Compliance auditing
Troubleshooting
Rollback planning

Example workflow:

			
Build
↓
Generate Script
↓
Review
↓
Approve
↓
Deploy

		

Publish Profiles

A publish profile stores deployment settings.

Typical settings include:

Target server
Database name
Authentication
Deployment options
Object exclusions
Ignore settings

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

Deployment Options

Deployment options control deployment behavior.

Common examples include:

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

Proper configuration reduces deployment risk.

Handling Schema Drift

Before deployment, the deployment engine compares:

Project

↓

Production

If unexpected differences exist:

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

This helps prevent accidental overwriting of production changes.

Deploying Through CI/CD

Modern SQL deployments are automated.

Typical Azure DevOps or GitHub Actions workflow:

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

		

Automation reduces manual errors.

Safe Deployment Practices

Good deployment practices include:

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

Environment-Specific Deployments

The same DACPAC can deploy to:

Development
Test
QA
Staging
Production

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

Rollback Considerations

Unlike application deployments, database rollbacks can be difficult because:

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

Best practices include:

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

Common Deployment Problems

Missing Dependencies

Example:

Procedure references a table that does not exist.

Validation catches this before deployment.

Schema Drift

Someone manually modified production.

Deployment identifies unexpected differences.

Data Loss Warnings

Example:

			
ALTER TABLE Employee
DROP COLUMN Salary;

The deployment engine warns that existing data will be lost.

Permission Errors

The deployment account lacks sufficient permissions.

Required permissions often include:

ALTER
CREATE
DROP
EXECUTE
CONTROL (depending on deployment scope)

Using SqlPackage

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

Common actions include:

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

		

Examples:

Generate deployment report:

SqlPackage /Action:DeployReport

Generate deployment script:

SqlPackage /Action:Script

Publish:

SqlPackage /Action:Publish

Azure DevOps Integration

Azure DevOps pipelines commonly perform the following:

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

Approvals and gates help prevent accidental production deployments.

GitHub Actions Integration

GitHub Actions follows a similar workflow:

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

		

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

Best Practices

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

DP-800 Exam Tips

Remember these important exam points:

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

Practice Exam Questions

Question 1

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

A. Restart the SQL Server service.

B. Build and validate the SQL Database Project.

C. Export the production database.

D. Rebuild all indexes.

Answer: B

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

Question 2

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

A. BACPAC

B. MDF file

C. DACPAC

D. Transaction log

Answer: C

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

Question 3

What is the primary purpose of a deployment report?

A. To store backup data

B. To monitor CPU usage

C. To list planned schema changes before deployment

D. To compress the database

Answer: C

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

Question 4

Which deployment model is used by SQL Database Projects?

A. Declarative deployment

B. Manual migration

C. Script-first deployment

D. Procedural deployment

Answer: A

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

Question 5

Why are Publish Profiles useful?

A. They encrypt databases.

B. They permanently store passwords inside source code.

C. They save deployment settings for reuse.

D. They improve query execution plans.

Answer: C

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

Question 6

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

A. Delete all indexes.

B. Generate and review a deployment script.

C. Disable all constraints.

D. Shrink the database.

Answer: B

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

Question 7

Why is schema validation performed during the build process?

A. To increase transaction log size.

B. To encrypt the database.

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

D. To compress database files.

Answer: C

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

Question 8

Which statement best describes incremental deployment?

A. Every database object is recreated during each deployment.

B. Only security objects are deployed.

C. Data is copied without changing the schema.

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

Answer: D

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

Question 9

Which practice best supports reliable database deployments?

A. Making schema changes directly in production.

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

C. Editing production objects with SSMS only.

D. Avoiding source control.

Answer: B

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

Question 10

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

A. Manually execute SQL scripts on every server.

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

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

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

Answer: C

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

Go to the DP-800 Exam Prep Hub main page

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

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

Identify and resolve query performance issues, including blocking and deadlocks – 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%)
   --> Optimize database performance
      --> Identify and resolve query performance issues, including blocking and deadlocks

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.

Best Practices for Preventing Performance Problems

The DP-800 exam emphasizes preventing problems rather than simply reacting to them.

Good database design, indexing, and application coding practices significantly reduce blocking, deadlocks, and poor query performance.

Design Tables Properly

Avoid:

excessively wide rows
unnecessary nullable columns
poor normalization
over-normalization requiring many joins

Good schema design leads to:

smaller pages
fewer logical reads
shorter lock durations

Use Appropriate Data Types

Poor choices increase memory usage.

Instead of:

NVARCHAR(MAX)

use

NVARCHAR(50)

when appropriate.

Benefits include:

reduced I/O
better index efficiency
improved cache utilization

Keep Transactions Short

One of the biggest causes of blocking is long-running transactions.

Bad:

			
BEGIN TRAN;
UPDATE Sales
SET Amount = Amount * 1.05;
WAITFOR DELAY '00:05:00';
COMMIT;

		

Locks remain active for five minutes.

Better:

			
BEGIN TRAN;
UPDATE Sales
SET Amount = Amount * 1.05;
COMMIT;

Commit Frequently

Instead of updating millions of rows in one transaction:

			
UPDATE LargeTable
SET Status = 'Complete';

process smaller batches.

Example:

			
WHILE 1=1
BEGIN
UPDATE TOP (1000) LargeTable
SET Status='Complete'
WHERE Status='Pending';
IF @@ROWCOUNT=0
BREAK;
END

		

Benefits:

shorter locks
reduced log growth
less blocking

Create Effective Indexes

Missing indexes often lead to:

table scans
excessive logical reads
blocking
CPU spikes

Create indexes on:

frequently filtered columns
join columns
ORDER BY columns

Example:

			
CREATE INDEX IX_OrderDate
ON Sales(OrderDate);

Avoid Too Many Indexes

Indexes improve reads.

Indexes slow:

INSERT
UPDATE
DELETE

Every modification updates every affected index.

Balance read performance against write performance.

Maintain Indexes

Over time indexes fragment.

Use:

			
ALTER INDEX ALL
ON Sales
REBUILD;

			
ALTER INDEX ALL
ON Sales
REORGANIZE;

Generally:

REORGANIZE for moderate fragmentation
REBUILD for heavy fragmentation

Write Efficient Queries

Avoid:

SELECT *

Use:

			
SELECT CustomerID,
       CustomerName

Benefits:

less network traffic
narrower execution plans
smaller memory grants

Filter Early

Instead of processing entire tables:

			
SELECT *
FROM Sales;

Use:

			
SELECT *
FROM Sales
WHERE OrderDate >= '2025-01-01';

Avoid Functions on Indexed Columns

Bad:

WHERE YEAR(OrderDate)=2025

This prevents index seeks.

Better:

			
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01'

Use EXISTS Instead of IN When Appropriate

Example:

			
WHERE EXISTS
(
SELECT *
FROM Orders
WHERE Orders.CustomerID=Customers.CustomerID
)

		

Often performs better on large datasets.

Parameter Sniffing

Parameter sniffing occurs when SQL Server optimizes a stored procedure using the first parameter values it receives.

Example:

EXEC GetOrders 1;

The plan is cached.

Later:

EXEC GetOrders 100000;

The same plan may perform poorly.

Possible solutions:

OPTION (RECOMPILE)
OPTIMIZE FOR
local variables
Query Store plan forcing

Monitor Wait Statistics

Wait statistics reveal what SQL Server spends time waiting on.

Common waits include:

Wait Type	Meaning
PAGEIOLATCH	Waiting for disk I/O
CXPACKET	Parallelism
LCK_M_X	Exclusive lock
LCK_M_S	Shared lock
WRITELOG	Log write bottleneck
SOS_SCHEDULER_YIELD	CPU pressure

Query:

			
SELECT *
FROM sys.dm_os_wait_stats;

Wait statistics help identify the true bottleneck before making changes.

Monitor Resource Usage

Useful DMVs include:

CPU:

sys.dm_exec_query_stats

Memory:

sys.dm_os_memory_clerks

Locks:

sys.dm_tran_locks

Sessions:

sys.dm_exec_sessions

Requests:

sys.dm_exec_requests

Query Store Best Practices

Enable Query Store on production databases.

Benefits:

captures historical plans
tracks regressions
compares runtime statistics
forces known good plans

Avoid disabling Query Store unless troubleshooting specific issues.

Azure SQL Automatic Performance Features

Azure SQL Database provides automatic tuning.

Features include:

Automatic index creation
Automatic index removal
Automatic plan correction
Automatic plan regression detection

These features reduce administrative effort.

Common DP-800 Exam Tips

Know the differences between:

Topic	Key Point
Blocking	Waiting for locks
Deadlock	Circular blocking; one transaction is terminated
Query Store	Historical performance monitoring
DMVs	Real-time diagnostic information
Execution Plans	Explain how SQL executes queries
Missing Index DMVs	Recommend useful indexes
Automatic Tuning	Azure SQL self-optimization
Snapshot Isolation	Reduces reader/writer blocking
Extended Events	Modern tracing tool
Parameter Sniffing	Cached plans may not fit all parameters

Summary

To excel in the DP-800 exam, you should be able to:

Interpret execution plans and identify expensive operators.
Use Query Store to identify regressions and force stable plans.
Query DMVs to diagnose slow-running queries, blocking, waits, and resource consumption.
Recognize and resolve blocking by shortening transactions, adding indexes, or using appropriate isolation levels.
Detect deadlocks with Extended Events, deadlock graphs, and system health sessions.
Understand common wait types and how they relate to CPU, I/O, memory, and locking issues.
Apply indexing, statistics maintenance, and efficient query-writing techniques to prevent performance problems.
Explain how Azure SQL automatic tuning can improve query performance and reduce administrative overhead.
Identify parameter sniffing scenarios and select appropriate mitigation strategies.

Practice Exam Questions

Question 1

A stored procedure performs well for some parameter values but poorly for others because SQL Server reuses a cached execution plan. Which performance issue is occurring?

A. Lock escalation

B. Parameter sniffing

C. Deadlocking

D. Page compression

Answer: B

Explanation:
Parameter sniffing occurs when SQL Server generates and caches an execution plan based on the first parameter values used. Subsequent executions with significantly different parameter values may reuse an inefficient plan, resulting in poor performance.

Question 2

A database administrator wants to reduce blocking caused by long-running UPDATE statements that affect millions of rows. Which approach is most effective?

A. Increase the database compatibility level

B. Disable Query Store

C. Process updates in smaller batches and commit frequently

D. Force all queries to use parallel execution

Answer: C

Explanation:
Breaking large modifications into smaller batches shortens transaction duration, releases locks more quickly, reduces transaction log growth, and minimizes blocking for other sessions.

Question 3

Which query is more likely to prevent SQL Server from performing an index seek on an indexed OrderDate column?

WHERE OrderDate >= '2025-01-01'

WHERE OrderDate BETWEEN '2025-01-01' AND '2025-12-31'

WHERE YEAR(OrderDate) = 2025

WHERE OrderDate < '2026-01-01'

Answer: C

Explanation:
Applying a function such as YEAR() to an indexed column makes the predicate non-SARGable, often preventing SQL Server from using an index seek and forcing an index or table scan instead.

Question 4

Which DMV provides information about current lock resources held by transactions?

A. sys.dm_exec_query_stats

B. sys.dm_os_wait_stats

C. sys.dm_exec_sessions

D. sys.dm_tran_locks

Answer: D

Explanation:
sys.dm_tran_locks displays active lock information, including lock types, resources, and owning sessions, making it valuable when investigating blocking.

Question 5

Why should developers avoid using SELECT * in production queries whenever possible?

A. It always causes deadlocks.

B. It automatically disables indexes.

C. It retrieves unnecessary columns, increasing I/O and network traffic.

D. It prevents Query Store from capturing execution statistics.

Answer: C

Explanation:
Selecting only the required columns reduces disk reads, network traffic, memory usage, and execution costs while allowing SQL Server to generate more efficient execution plans.

Question 6

A SQL Server database contains heavily fragmented indexes after months of frequent updates. Which maintenance task should typically be performed when fragmentation is high?

A. Update statistics only

B. Rebuild the indexes

C. Shrink the database

D. Clear the plan cache

Answer: B

Explanation:
An index rebuild recreates the index structure, removes fragmentation, and updates index statistics. It is generally recommended when fragmentation is significant.

Question 7

A developer notices frequent LCK_M_X waits in SQL Server. What do these waits indicate?

A. CPU saturation

B. Memory allocation failures

C. Sessions waiting for exclusive locks

D. Network latency

Answer: C

Explanation:
LCK_M_X wait types indicate sessions waiting to acquire exclusive locks that are currently held by other transactions, suggesting blocking.

Question 8

Which Azure SQL feature can automatically detect a query plan regression and restore a previously better-performing execution plan?

A. Intelligent Insights

B. Automatic Plan Correction

C. Azure Monitor Alerts

D. Elastic Jobs

Answer: B

Explanation:
Automatic Plan Correction, part of Azure SQL automatic tuning, identifies query regressions and can force a previously efficient execution plan automatically.

Question 9

Which practice best helps prevent blocking in high-concurrency OLTP systems?

A. Keep transactions as short as possible.

B. Disable indexes during business hours.

C. Increase page size.

D. Use SELECT * in all reporting queries.

Answer: A

Explanation:
Short transactions reduce the amount of time locks are held, allowing other sessions to access data sooner and minimizing blocking.

Question 10

A DBA wants to determine whether SQL Server is primarily waiting on disk I/O, locking, or CPU scheduling before making performance changes. Which diagnostic information should be examined first?

A. Database file sizes

B. Transaction log backup history

C. Wait statistics

D. Server collation settings

Answer: C

Explanation:
Wait statistics provide a high-level overview of where SQL Server spends its time waiting, making them one of the best starting points for diagnosing performance bottlenecks before making tuning decisions.

Go to the DP-800 Exam Prep Hub main page

Overview

1. Why Database Connections Affect Performance

Connection overhead

2. Connection Pooling

Without pooling

With pooling

3. Client-Side Connection Pooling

Benefits

4. Azure Database for PostgreSQL Built-In PgBouncer

5. PgBouncer Port 6432

Exam tip

6. PgBouncer Transaction Pooling

7. PgBouncer Client Connections vs. PostgreSQL Connections

8. Do Not Simply Increase max_connections

Better approach

9. Choosing an Appropriate Pool Size

10. Connection Pooling in Scaled-Out Applications

Exam scenario

11. Serverless Applications and Connection Bursts

Recommended design

12. Connection Churn

Key exam concept

13. Application Location Matters

General principle

14. Availability Zones and Latency

15. Private Networking

Important distinction

16. DNS and Connection Reliability

17. TLS and Connection Overhead

18. Connection Timeouts

Connection timeout

Command timeout

Pool wait timeout

19. Retries and Transient Failures

Avoid retry storms

20. Connection Pooling and Transactions

21. Avoid Long-Running Transactions

22. PgBouncer Configuration to Know

23. Monitoring Connections

24. Diagnosing Connection-Related Performance Problems

Step 1: Check application latency

Step 2: Check connection counts

Step 3: Check connection churn

Step 4: Check pool configuration

Step 5: Check database resources

Step 6: Check network topology

Step 7: Optimize the actual workload

25. Connection Optimization Strategy

Application

Pooling

Network

Database

26. Common AI-200 Exam Traps

Trap 1: “Increase max_connections“

Trap 2: “Create a connection for every request”

Trap 3: “Use the largest possible pool”

Trap 4: “PgBouncer increases database processing capacity”

Trap 5: “More connections always means more throughput”

Trap 6: “Private networking automatically reduces latency”

Trap 7: “Connection timeout controls query execution time”

Trap 8: “Connection pooling eliminates the need to optimize SQL”

27. Key Takeaways for the AI-200 Exam

Practice Exam Questions

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Final Exam Perspective

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

8. Do Not Simply Increase `max_connections`

Trap 1: “Increase `max_connections`“

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