Launch Deal · 50% off 1st month ·

1.8 — Design SQL Solutions with AI-Assisted Tools

DP-800 Domain 1 · Skill 1.8

What You Need to Know

DP-800 expects you to use GitHub Copilot and Copilot in Fabric productively and safely: enable them, steer them with instruction files, connect them to databases through the Model Context Protocol (MCP), and understand the security impact.

Enabling the tools

Tool How it's enabled
GitHub Copilot (in VS Code or SSMS) A Copilot license for the user (or an organization policy that grants one). In VS Code, the MSSQL extension adds SQL-aware chat with your connected database schema as context
Copilot in Fabric A Fabric admin turns on the tenant setting for Copilot and Azure OpenAI-powered features. The workspace must be on a paid Fabric capacity

Steering Copilot with instruction files

File Scope
.github/copilot-instructions.md Repository-wide instructions added to every chat request: naming conventions, "always use parameterized T-SQL", target platform, preferred patterns
.github/instructions/*.instructions.md Path-specific instructions with an applyTo glob in the front matter, for example rules that apply only to **/*.sql
.github/prompts/*.prompt.md Reusable prompt files for repeated tasks, such as "write tSQLt tests for this procedure"

Good instructions are short, specific and verifiable: "Use datetime2(3), never datetime." "Every procedure starts with SET NOCOUNT ON; SET XACT_ABORT ON;."

Models and MCP tools in a chat session

  • In a Copilot chat you can pick the model and choose which tools it may call.
  • MCP servers expose tools such as "list tables", "run read-only query" or "describe schema" to the agent. Examples are a SQL MCP server (Data API builder can act as one), the MSSQL extension's tools, and Fabric endpoints such as a lakehouse MCP endpoint.
  • MCP servers are configured per workspace (for example in .vscode/mcp.json) with an endpoint URL and authentication. Turn on only the tools a task needs.

Security impact

Risk Mitigation
Schema, sample data or secrets sent as prompt context Don't paste secrets or production data. Use non-production connections for AI-assisted development
An agent running destructive SQL through an MCP tool Read-only database users for MCP connections, least-privilege roles, and approval before each tool call
Generated code with vulnerabilities (dynamic SQL injection, missing permission checks) Review everything; insist on parameters; run static analysis and tests in CI
Prompt injection through data the agent reads Treat retrieved data as untrusted; limit what tools can do
Licensing and data residency Use organization-approved tools, with the tenant settings and data-processing terms they come with

Key Concept: Steer Copilot with instruction files, connect it to data through MCP with least privilege and approvals, and never trust generated SQL without review and tests.

Exam Traps

  • Giving the MCP connection a db_owner login "for convenience"
  • Putting connection strings or keys in copilot-instructions.md
  • Expecting Copilot in Fabric to work on a workspace with no paid capacity, or with the tenant setting off
  • Accepting generated dynamic SQL that concatenates user input

Check your understanding

Practice Scenario

Developers want GitHub Copilot agent mode in VS Code to query a shared development database through an MCP server, to explore its schema and sample data. What is the safest way to set up the MCP connection?

Report issue

Log in to save your progress.

You've finished the free part of DP-800

The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.