Free DP-800 Exam Braindumps
Developing AI-Enabled Database Solutions Questions 33–40 (page: 6)

Explanation:

Scenario: Fabrikam identifies the following development requirements: provide the functionality to retrieve all the transactions of a given patient between two dates, showing a running total.
Must use the PARTITION BY and ORDER BY clauses within the OVER function,
Box 1: FUNCTION A FUNCTION to return the required rows.
Box 2: PARTITION BY t.CustomerID Use PARTITION BY t.CustomerID to restrict the sum calculation to only that specific patient.
Incorrect: * GROUP BY BY t.CustomerID PARTITION BY t.CustomerID is superior to GROUP BY because it calculates the running total while retaining individual transaction rows. GROUP BY would collapse the data into a single summary row, making it impossible to see the itemized transactions required for a "running total" view.
Box 3: ORDER BY t.TransactionDate, t.TransactionID Using ORDER BY t.TransactionDate is essential for a "running" total.Calculates the sum based on time rather than random row order.

Reference:

https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql

Explanation:

Scenario: You have an on-premises application named TransactionProcessing that uses a hard-coded username and password in a connection string to access DB1. Security Requirements: Fabrikam identifies the following security requirements: The TransactionProcessing application must use a passwordless connection to DB1.
The most appropriate security solution for an on-premises application connecting to an Azure SQL Database without passwords is Microsoft Entra ID authentication. Specifically, you should implement one of the following Entra-based mechanisms depending on your application's environment and architecture:
*-> 1. Microsoft Entra Service Principal with Certificate This is the standard "passwordless" approach for non-Azure (on-premises) applications.
How it works: You register the application in Microsoft Entra ID. Instead of a client secret (password), you use a
certificate for authentication.
Benefit: No secrets are stored in your code or configuration files. The application uses the certificate to obtain an access token from Entra ID to connect to the database.
2. Microsoft Entra Integrated Authentication
3. Managed Identity (via Azure Arc) For a more modern approach, you can register your on-premises server with Azure Arc.
How it works: Azure Arc extends Azure's Managed Identity capabilities to on-premises hardware.
Benefit: This is the "gold standard" for passwordless security, as Azure handles the identity, rotation, and lifecycle of the credentials automatically.

Reference:

https://learn.microsoft.com/en-us/azure/developer/java/spring-framework/migrate-sql-database-to-passwordless-connection

Explanation:

Scenario: The subscription contains an Azure SQL database named DB1. You create an SDK-style SQL database project in Microsoft Visual Studio Code named Database.sqlproj and add the project to a GitHub repository.
Box 1: push
To complete the GitHub Actions workflow for an Azure SQL database project, use the push event to trigger the build and the build command to compile the .sqlproj.
Box 2: build Database.sqlproj -c Release

Reference:

https://stackoverflow.com/questions/53473804/build-sqlproj-on-azure-devops

Explanation:

To use a Managed Identity with sys.sp_invoke_external_rest_endpoint in SQL Server, you need to configure your database-scoped credential with the following specific values:
Required Settings WITH IDENTITY: Must be set to Managed Identity. SECRET: Must be set to SECRET = N'{"resourceid":"<value>"}'.
Example Statement CREATE DATABASE SCOPED CREDENTIAL [MyManagedIdentityCredential] WITH IDENTITY = 'Managed Identity', SECRET = N'{"resourceid": "https://azure.com"}'; -- Or your specific resource URI
Quick Breakdown Identity: Managed Identity tells SQL Server to use the instance's assigned Azure identity rather than a static key or token.
Secret: The resourceid inside the JSON string specifies the Audience (resource) for which the Managed Identity is requesting an access token (e.g., Azure Storage, Key Vault, or a custom API).

Reference:

Explanation:

This setup is a solid way to bridge the gap between your SQL updates and your vector index. By using CDC (Change Data Capture), you avoid the heavy lifting of scanning the entire table for changes, which keeps your database CPU overhead low.
To make this work efficiently within the Azure Functions app, you’ll typically want to: Batch the updates: Instead of triggering a function for every single row change, process small batches of CDC records to reduce the number of calls to your embedding model (like OpenAI).
Use an Upsert logic: In your function, ensure the code handles both new inserts and updates by overwriting the existing embedding for a specific Article ID in the database.
Implement a "Cool-down" or Timer: If the "frequent updates" happen in bursts, you can use an Azure Service Bus or Storage Queue between the CDC process and the Function to smooth out the processing load.

Reference:

Explanation:

To use GitHub Copilot Chat effectively in this environment without exposing sensitive data, you should focus your prompts entirely on the schema and logic rather than the data itself. Since Copilot Chat can "see" your open files, the best approach is to provide the table structures as DDL (Data Definition Language) statements or a simplified description.
Steps to generate the stored procedure: Define the Schema: Open a new SQL file in your Codespace. Paste the CREATE TABLE scripts (without any data) for your two tables.
Prompt Copilot: Use the Chat view to request the procedure.
Example Prompt: "Based on the table definitions in my open file, write a T-SQL stored procedure that joins TableA and TableB on [Join Column]. Include logic to filter by [Parameter] and ensure no PII columns are included in the SELECT statement."
Review for PII/Secrets: Before executing, manually verify that the generated code doesn't include hardcoded secrets or call PII columns you intended to omit.
Security Check: Because you are in a Codespace, ensure your .env files or connection strings are in your .gitignore so they aren't indexed by Copilot.

Reference:

Explanation:

Box 1: Enable a managed identity and use Microsoft Entra authentication.
Ensure that sales-api connects to SalesDB by using passwordless authentication.
To ensure your Azure App Service connects to an Azure SQL Database using passwordless authentication, you must use Managed Identities and Microsoft Entra authentication. This process eliminates the need to store credentials in your code or connection strings.
1. Enable Managed Identity on the App Service You must first give your app an identity that Azure SQL can recognize.
2. Configure Microsoft Entra Admin for Azure SQL The SQL Server needs an Entra administrator to manage identity-based access.
3. Grant Database Permissions to the Managed Identity You must manually create a user in the database for the app's identity. This cannot be done via the portal's IAM roles; it requires a SQL command.
Box 2: Create a private endpoint and disable public network access. Ensure that all the database traffic remains within the subscription.
To ensure all database traffic between your Azure App Service and Azure SQL Database remains within your subscription and virtual network, you should implement an Azure Private Endpoint.
While Service Endpoints also keep traffic on the Microsoft backbone, they still resolve to a public IP address. A Private Endpoint assigns a private IP address from your subnet directly to the database, ensuring the connection is fully isolated from the public internet.

Reference:

Explanation:

Box 1: get fetch origin
To keep your feature branch up-to-date and get your SQL project ready for review, you can use the following GitHub CLI (gh) and Git commands. GitHub CLI Workflow Script
# 1. Switch to your feature branch git checkout feature/name_of_feature
# 2. Pull the latest changes from main and merge them into your feature branch # This ensures your SQL schema changes are compatible with the current production state git fetch origin git merge origin/main
Box 2: git merge origin/main
git merge origin/main: This is critical for SQL projects. It allows you to resolve any schema conflicts (like two
people modifying the same table) locally before the PR is even reviewed.
Box 3: gh pr create
# 3. Push your updated feature branch to GitHub git push origin feature/name_of_feature
# 4. Create the Pull Request # This opens the PR from your feature branch into main gh pr create \ --title "Feature: Update SQL Schema for [Feature Name]" \ --body "Brief description of the changes to the SDK-style SQL project." \ --base main \ --head feature/name_of_feature
gh pr create: This command replaces the need to visit the GitHub website. --base: The target branch (where the code is going). --head: Your source branch (where the code is coming from).

Reference: