What Is GitHub Copilot Agent Mode in SSMS?
GitHub Copilot Agent Mode brings autonomous, multi-step database operations directly into SQL Server Management Studio (SSMS). Announced in public preview in June 2026, this feature lets you describe a high-level goal in natural language — such as investigating a performance spike, analyzing a stored procedure, or finding failed SQL Agent jobs — and Copilot will work through the task by executing queries, reading system data, and iterating on its own outputs until the objective is met or your input is required.
How It Differs from Ask Mode
SSMS already had GitHub Copilot Chat in “Ask mode” — a read-only experience where Copilot generates T-SQL for you to review and run manually. Agent Mode goes further: it can execute queries and commands with your approval, perform schema changes if your login has permission, and automatically revise its approach when a query fails or results don’t satisfy the goal.
| Capability | Ask Mode | Agent Mode |
|---|---|---|
| Execution | Read-only queries only | Queries and commands execute with approval |
| Schema changes | Generates T-SQL for you to run | Can execute if you approve and have permission |
| Workflow | Single response per prompt | Multi-step, iterative until goal reached |
| Error handling | Manual retry | Auto-revises and retries on failure |
Prerequisites and Setup
To use Agent Mode, you need:
- SSMS 22.7 or later with the AI Assistance workload (installed via Visual Studio Installer)
- A GitHub account with Copilot access (or use Copilot for free in SSMS)
Once installed, open the chat pane (View > GitHub Copilot Chat), select Agent from the mode dropdown, and choose the Tools icon to see available tools. The sql-tools category provides predefined database operations; deselecting all tools disables Agent Mode functionality.
Example Prompts
Microsoft suggests prompts like:
In the WideWorldImporters database on the SalesPRD server, analyze theSales.usp_QuarterlySalesSummarystored procedure and its execution plan and tell me how to improve itThe AdventureWorks database on the SalesPRD server had high CPU between 1PM and 2PM today, investigate what caused itFind the SQL Agent jobs on the SalesPRD server that failed in the last 24 hours, analyze the history and explain what happened and how to fix the jobs
Always include the database or server name — Agent Mode does not inherit context from the active query editor.
Approval Workflow and Security
When Copilot is ready to execute a query or command, it pauses and asks for approval. You can choose Allow once, Allow for this session, or Allow always. The security boundary is SQL Server’s permission enforcement, not Copilot’s approval system — if your login lacks a permission, the operation fails regardless of Copilot’s willingness.
Why This Matters for Database Teams
Agent Mode shifts routine investigation and troubleshooting from manual query assembly to goal-directed AI assistance. For teams managing large SQL Server estates, this reduces the time spent writing repetitive diagnostic queries and lets senior engineers focus on architecture and optimization. The approval system keeps human oversight intact, while the iterative retry logic means Copilot often resolves multi-step problems without constant babysitting.
What to Watch For
- Permission sprawl: Granting broad approvals (e.g., “Allow always”) on production servers risks unintended changes. Use least-privilege logins.
- Context gaps: Agent Mode does not see your active query editor; always specify the target server and database explicitly.
- MCP tools: Model Context Protocol tools can extend Copilot to external services but are disabled by default — enable them deliberately.
Next Steps
- Review the Microsoft Learn documentation for full setup instructions
- Check your SSMS version (Help > About) and upgrade if below 22.7
- Test with non-production databases first to understand the approval flow