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?
Log in to save your progress.
Sources
- Adding repository custom instructions for GitHub Copilot, GitHub Docs
- GitHub Copilot for the MSSQL extension in VS Code, Microsoft Learn
- Use MCP servers in VS Code, VS Code documentation
- Overview of Copilot in Fabric, Microsoft Learn
- SQL MCP Server (Data API builder), Microsoft Learn
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.
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.