Developing AI-Enabled Database Solutions · 38% of the exam

Secure, optimize, and deploy database solutions: free practice questions

5 sample questions from our 95-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Microsoft exam.

1. An engineering team has a SQL Database Project using the SDK-style model. A developer has introduced a new schema change on a feature branch that renames the column dbo.Orders.CustomerRef to dbo.Orders.CustomerID. During the CI pipeline's schema comparison step, the deployment fails with a warning about potential data loss. What is the MOST appropriate way to handle this change safely in the project?

A. Add a pre-deployment script that renames the column using sp_rename, and remove the column rename from the object definition file.✓ Correct
B. Set the deployment option BlockOnPossibleDataLoss to False in the publish profile to suppress the warning.
C. Delete the existing table definition and recreate the table with the new column name in the project.
D. Use a post-deployment script to copy data from CustomerRef to CustomerID after the schema is applied.

Explanation

Column renames cannot be handled declaratively by a SQL Database Project because the deployment engine sees a dropped column (CustomerRef) and a new column (CustomerID) — it cannot infer a rename, leading to data loss. The correct approach is to use a pre-deployment script that executes sp_rename to rename the column before the declarative schema is compared, then keep the project definition aligned. Option B suppresses the warning but does not prevent actual data loss — the column will be dropped and recreated as empty. Option C deletes and recreates the table, causing data loss. Option D uses a post-deployment script to move data, but by that point the original column has already been dropped and the data is lost.

2. A developer is configuring Data API builder (DAB) for a Microsoft Fabric SQL Database table named dbo.Products. She wants clients to be able to retrieve paginated results and also perform create and update operations, but she does not want clients to delete records. Which combination of permissions should she configure for the table entity in the DAB configuration file?

A. actions: ["*"]
B. actions: ["read", "create", "update"]✓ Correct
C. actions: ["read", "create", "update", "execute"]
D. actions: ["read", "create", "delete"]

Explanation

DAB entity permissions are controlled by specifying an actions array. To allow read (paginated GET), create (POST), and update (PATCH/PUT) while blocking delete, the correct value is ["read", "create", "update"]. Option A uses the wildcard "*", which grants all actions including delete — violating the requirement. Option C adds "execute", which grants stored procedure execution rights and is unnecessary here. Option D includes "delete" and excludes "update", the opposite of the stated requirement.

3. An e-commerce application uses Azure SQL Database. During peak sales events, the support team reports that some read-only reporting queries are being blocked by long-running write transactions, causing report timeouts. The transactions must remain serializable for write operations. You need to allow reporting queries to read committed data without being blocked by writers and without requiring changes to the write transaction logic. What should you do?

A. Set the database option READ_COMMITTED_SNAPSHOT to ON so that READ COMMITTED readers use row versioning instead of shared locks✓ Correct
B. Change the reporting queries to use the NOLOCK table hint to skip all locking
C. Set the default transaction isolation level to SERIALIZABLE for all sessions
D. Enable the ALLOW_SNAPSHOT_ISOLATION database option and change the reporting queries to use SNAPSHOT isolation

Explanation

Enabling READ_COMMITTED_SNAPSHOT (RCSI) changes the behavior of the default READ COMMITTED isolation level so that readers use a version of the row from the version store instead of acquiring shared locks. This eliminates reader-writer blocking without any changes to the write transaction logic or the reporting queries (they continue to run under READ COMMITTED by default). Why the distractors are wrong: - Using NOLOCK (READUNCOMMITTED) allows dirty reads of uncommitted data, which can return inconsistent or phantom data — unacceptable for financial/reporting use cases and is generally considered unsafe practice. - Increasing the default isolation to SERIALIZABLE would cause more blocking and locking, making the problem worse, not better. - Snapshot isolation (ALLOW_SNAPSHOT_ISOLATION) also uses row versioning and avoids blocking, but it requires every reporting query to explicitly specify WITH (SNAPSHOT) or for sessions to set 'SET TRANSACTION ISOLATION LEVEL SNAPSHOT' — meaning application changes ARE required, which the scenario disallows.

4. An application team is configuring Data API builder (DAB) to expose a Microsoft Fabric SQL Database table. They require that the REST endpoint for the dbo.Orders entity supports server-side pagination and that clients cannot request more than 100 rows per page. Which configuration property controls the maximum page size per request in DAB?

A. options.default-page-size in the runtime section of dab-config.json
B. options.max-page-size in the entity's REST configuration section of dab-config.json✓ Correct
C. rest.max-response-size-mb in the global runtime configuration of dab-config.json
D. pagination.limit in the entity's GraphQL configuration section of dab-config.json

Explanation

In DAB's dab-config.json, the per-entity REST configuration supports an 'options.max-page-size' property (under the runtime host section or per-entity overrides) that caps the number of rows a client may request. This directly enforces the 100-row ceiling. 'options.default-page-size' sets the default but does not enforce a hard maximum — a client could still override it. 'rest.max-response-size-mb' is a payload size limit measured in megabytes, not a row count limit. 'pagination.limit' in the GraphQL section applies to GraphQL queries, not REST endpoints, and is a different configuration path.

5. A SaaS application hosted on Azure App Service connects to an Azure SQL Database. The development team wants to eliminate database connection string secrets from all configuration files and environment variables. The App Service is in the same Azure subscription as the SQL Database. Which two actions must the team take to enable passwordless access from the App Service to the SQL Database? (Select TWO)

A. Enable a system-assigned managed identity on the Azure App Service✓ Correct
B. Store the SQL connection string in Azure Key Vault and reference it as an App Service application setting
C. Grant the App Service managed identity the db_datareader and db_datawriter roles (or equivalent) in the SQL Database using a CREATE USER … FROM EXTERNAL PROVIDER statement✓ Correct
D. Create a SQL login for the managed identity in the master database and assign it the sysadmin server role
E. Set the SQL Database firewall to allow all Azure services by toggling 'Allow Azure services and resources to access this server'

Explanation

Enabling a system-assigned managed identity on the App Service (option A) creates an Azure AD identity for the service. Granting that identity database permissions via CREATE USER … FROM EXTERNAL PROVIDER and a role assignment (option C) allows the App Service to authenticate using its token—no password needed. Option B still involves a secret in Key Vault (the connection string contains credentials), which doesn't eliminate secrets from the auth flow. Option D would require a SQL login, which is password-based and grants excessive sysadmin privilege. Option E (firewall rule) controls network access, not authentication—it does not remove the need for credentials.

90 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free