How I Built an AI-Driven SQL Server Audit Solution with Just Copilot and PowerShell

Running SQL Server health checks across a multi-server estate shouldn't feel like herding cats. But if you're still manually executing Glenn Berry's diagnostic scripts on each box, collecting output, and stitching together reports in spreadsheets — you know exactly what I mean.

Across my client estate — spanning 10 to 50+ instances per organisation — I've executed these health checks hundreds of times. Running the same 15 scripts on every server, every week, was eating half a day per client. At that scale, inefficiency compounds fast. The scripts themselves are gold — Glenn's work and the Brent Ozar First Responder kit are industry standard for a reason. But the process around them was broken.

Here's how I built an automated audit solution using nothing more than Copilot Basic, PowerShell, and SSMS Central Management Server. No Pro subscription. No additional IDEs. Just a repeatable two-session workflow that any DBA can replicate.

The Problem: Great Scripts, Painful Process

Every DBA knows the drill. After 15+ years troubleshooting SQL Server performance, I've seen teams waste countless hours on manual health checks. You've got your health check scripts lined up:

• Server configuration checks
• Wait stats analysis
• Missing indexes
• Blocking chains
• Memory pressure indicators
• I/O subsystem metrics

Glenn Berry's scripts cover this brilliantly. The Fast Responder kit gives you the decision trees. But executing them across a CMS-registered server list, collecting output, normalising formats, and building actionable reports? That's manual labour that doesn't scale.

I needed something that could:

• Query all registered servers from CMS automatically
• Execute the diagnostic scripts in sequence
• Capture output to structured files
• Flag exceptions without drowning me in false positives
• Run on a schedule without babysitting

And I wanted to build it without learning a new framework or buying expensive tooling.

Session 1: Discovery — Let Copilot Gather the Intelligence

The first session is purely about discovery. I'm not asking Copilot to build anything yet. I'm asking it to help me understand what I'm working with.

I opened Copilot Basic in PowerShell ISE (nothing fancy — just what's already on my machine) and started with a simple prompt:

"I have SSMS Central Management Server configured with production servers registered under 'Production\SQLCluster'. Write a PowerShell script using dbatools that connects to each server and collects: server name, instance name, SQL Server version, total memory, CPU count, and uptime."

Copilot returned a script using Get-DbaServer and Invoke-DbaQuery to iterate through the CMS server list. I ran it against my estate and saved the output to JSON. This gave me a baseline inventory.

Next prompt:

"Using Glenn Berry's SQL Server Diagnostic Information queries as reference, write PowerShell commands that execute the following DMV queries against each server and export results to timestamped CSV files: sys.dm_os_wait_stats, sys.dm_db_missing_index_details, sys.dm_exec_query_stats top 20 by CPU."

Copilot doesn't have direct access to Glenn's scripts, but it knows the DMVs and patterns. I fed it the specific query names and it built the orchestration layer around them. Each query result exported to a dated CSV with server name prefixed.

By the end of session 1, I had:

• A complete server inventory
• Raw diagnostic data from every instance
• All output in structured, machine-readable format
• Zero manual copy-paste

Total time: 45 minutes. Most of that was reviewing Copilot's output and tweaking connection strings. Here's the key point: without Copilot, this discovery session would have taken half a day to a full day. With Copilot, it's done in under an hour. That's not cutting corners — that's working smarter.

Session 2: Build — Specify Constraints and Iterate

Now I had data. Session 2 is where I asked Copilot to build the actual audit solution — but with explicit constraints upfront.

My prompt:

"I have CSV files containing wait stats, missing indexes, and query stats from 15 production servers. Build a PowerShell script that:

1. Reads all CSVs from the data directory
2. Identifies servers with wait stats showing PAGEIOLATCH_* in top 5
3. Flags missing indexes with user_seeks > 1000 and avg_user_impact > 70
4. Generates a summary report highlighting only exceptions
5. Sends me an email if any server has critical findings

Constraints: Must run under dbatools module, no additional dependencies. Performance is critical — don't load all data into memory at once. Output should be HTML report with colour-coded severity."

Copilot generated a script that processed files sequentially, applied threshold filters, and built an HTML report with red/amber/green indicators. First version was close but loaded everything into arrays — I told it to stream instead. Second version handled it properly.

I then asked:

"Add a function that compares this week's results to last week's and highlights regressions — for example, wait stats that have increased by more than 20%."

This gave me trend analysis without building a full data warehouse. Simple file comparison, but now I could spot degrading performance before users complained.

By the end of session 2, I had:

• An automated audit script running against all servers
• Exception-based reporting (only what needs attention)
• Trend comparison week-over-week
• Email alerts for critical findings
• HTML reports I could forward to IT Directors

Total time: 90 minutes, including iteration and testing. Two and a half hours total to build a fully automated audit workflow. Without AI assistance, this would have taken days — possibly weeks — of manual scripting, testing, and debugging. For any DBA, that's a genuinely valuable return on investment.

Copilot as a Force Multiplier

Here's what surprised me about this workflow: Copilot didn't just write scripts faster than I could have alone. It helped me think of things I wouldn't have considered.

Examples from this build:

• Trend comparison: I was thinking "run the audit." Copilot suggested "compare this week to last week" — which caught degrading performance before users noticed.
• Exception filtering: I was planning to review all output. Copilot suggested "flag only what exceeds thresholds" — which turned 50 pages of data into 5 actionable items.
• HTML formatting: I was going to output CSVs. Copilot suggested "colour-code by severity" — which made the report instantly scannable for IT Directors.
• Edge cases: I forgot about connection timeouts. Copilot added retry logic. I forgot about file locking. Copilot added sequential processing.

This is the real value of AI-assisted development: it expands your solution space. You're not just automating what you already knew to do — you're discovering better approaches through the iteration process itself.

For DBAs considering whether to build this themselves: the 2-3 hour investment isn't just about getting a script. It's about having a collaborative partner that helps you think more comprehensively about the problem.

Why Two Sessions Matter

This separation — discovery first, build second — is deliberate. Here's why:

Session 1 is about understanding your environment. You're not committing to a solution yet. You're gathering intelligence: what servers exist, what data is available, what the actual pain points are. Copilot excels here because it can quickly generate exploratory scripts without you needing to remember every dbatools cmdlet.

Session 2 is where you design the solution with full context. You know what data you have. You know what constraints matter (performance, permissions, dependencies). You can specify thresholds and exceptions because you've seen the actual numbers.

Most DBAs skip session 1 and jump straight to "build me an audit tool." Then they get a generic solution that doesn't match their estate. By doing discovery first, you give Copilot the context it needs to build something that actually fits.

The Economics: Three Valid Paths

Let's be direct about your options. All three are legitimate — the right choice depends on your situation.

Option 1: Traditional DIY (Without AI)

• Time investment: Days to weeks of manual scripting, testing, and debugging
• Cost: If you bill at £75-150/hour (typical DBA contractor rates), you're looking at £600-1,200+ for a multi-day build
• Best for: DBAs who enjoy deep technical builds, have time to invest, and want complete control
• Reality check: For most working DBAs, this is prohibitive. The opportunity cost is too high.

Option 2: Copilot-Enabled DIY (Recommended for Most)

• Time investment: 2-3 hours to build the two-session workflow described above
• Cost: £225-450 of your time (at contractor rates) — or essentially free if you're a salaried employee investing in automation
• Best for: DBAs who want a customizable foundation, enjoy building automation, and have a few hours to invest
• The win: This is genuinely cost-effective. An employee spending 2-3 hours building this is making a smart investment that pays dividends for months. You get a tailored solution, you learn from the process, and you own the code.

Option 3: SQLOPTIMISE Done-for-You Audit

• Time investment: Zero build time. We execute, you receive results.
• Cost: Free Health Assessment (no cost, introductory) — or Specialist Audits (paid, for complex/ongoing requirements)
• Best for: DBAs who need results faster than 2-3 hours allows, want an independent expert perspective, face complex/locked-down environments where even Copilot DIY is challenging, or need ongoing specialist support
• The value: Speed, expertise, and an independent set of eyes. DBAs often miss issues in their own environments — we bring pattern recognition from hundreds of estates.

The key insight: Copilot-enabled DIY is a genuine win. If you have 2-3 hours to invest, build this yourself — you'll learn, you'll own the solution, and you'll have a foundation to extend. SQLOPTIMISE isn't "better than" building it yourself. We're an alternative for when you need results faster, need expert perspective, or face constraints that make DIY challenging.

Two ways to engage:

1. Free Health Assessment — Complimentary audit to show you what's possible. No obligation.
2. Specialist Audits — For complex environments, ongoing support, or when you need custom solutions built for your specific requirements. This takes time and costs money — because it's expert work tailored to your estate.

Working with Copilot as a DBA: What I Learned

A few patterns emerged that made this workflow effective:

Be specific about what you want. "Audit my servers" is too vague. "Find servers with PAGEIOLATCH waits in top 5 and missing indexes with impact over 70" gives Copilot something concrete to build.

Specify constraints upfront. Tell it about performance requirements, module dependencies, permission boundaries. Copilot adapts — if you say "must run under minimal permissions," it won't suggest sysadmin-only DMVs.

Iterate collaboratively. First draft is rarely perfect. I'd run the script, see what broke or what was inefficient, then ask Copilot to fix it. "This line is slow on large servers — rewrite to use streaming instead."

Use Copilot as a DMV reference. Even experienced DBAs don't memorise every system view. Copilot knows the schema and can write the JOIN conditions correctly first time.

Keep it lightweight. I'm not building an enterprise monitoring platform. I needed something that runs weekly, flags exceptions, and doesn't require a dedicated server. Copilot respected that scope when I stated it clearly.

Why This Matters in Restricted Environments

Not every DBA operates in a greenfield environment. Some of my clients work in:

• Defense contractors with air-gapped networks where downloading new tools requires security review cycles
• Healthcare organisations bound by HIPAA compliance that restricts what software can touch patient data systems
• Financial services with change control boards that take weeks to approve new monitoring agents
• Government estates where only pre-approved tools can be installed

In these environments, you can't just choco install your way to better automation. You're limited to what's already approved: typically PowerShell, SSMS, and maybe dbatools if it's been through the procurement gauntlet.

This is where Copilot Basic + GPT-5.5 becomes genuinely valuable. You're not installing new software. You're using AI to write better scripts with the tools you already have permission to use. The intelligence comes from Copilot; the execution uses your existing, approved stack.

For DBAs in locked-down environments, this workflow isn't just convenient — it's often the only path to automation without a six-month security review.

And this is where SQLOPTIMISE can help: If even Copilot DIY is challenging in your environment (no internet access, strict change control, no permissions to run AI-generated scripts), we can execute the audit externally and provide findings you can act on within your constraints.

What This Gets You

The end result isn't just a script — it's a foundation for proactive database management:

• Repeatable audits that run the same way every time — critical for compliance and change tracking
• Exception-based reporting so you focus on what actually needs attention — reducing alert fatigue
• Trend data to spot degradation before it becomes an incident — preventing 3am page-outs
• Documentation of your estate's health over time — invaluable for capacity planning and budget justification
• A starting point for further automation (remediation scripts, dashboard integration, Teams alerts)
• A learning experience — you'll understand your estate better after building this than you did before

Whether you build this yourself with Copilot or engage SQLOPTIMISE to run the audit, the key is having systematic, automated audits. Manual health checks don't scale. Automated audits do.

And critically: this is accessible to any DBA with a Copilot Basic subscription. You don't need Pro features. You don't need additional IDEs or paid monitoring tools. You need PowerShell, dbatools, SSMS with CMS configured, and the willingness to invest two sessions in building something better than manual execution.

The Scripts Aren't the Point

I'm not sharing the actual scripts here. Not because they're secret — but because the value isn't in the code itself. It's in the workflow. Your estate is different from mine. Your thresholds will differ. Your CMS structure, your permission model, your reporting requirements — all unique.

What matters is the method: discover first, build second, iterate with clear constraints, and keep it lightweight enough that you'll actually maintain it.

I've since moved to a more advanced solution (the aDBA platform handles this at scale), but this Copilot-built audit was the proof of concept that showed automation was worth the investment. It paid for itself in the first month by catching a memory leak trend that would have caused an outage within weeks.

What Systematic Auditing Uncovers

Here's a typical pattern from a recent client engagement:

The automated trend comparison flagged PAGEIOLATCH waits increasing week-over-week across a 35-server estate. Investigation revealed a missing index on a high-volume transaction table. Index deployment took 20 minutes. Query response times improved noticeably — enough that the client's development team reported faster page loads the same day.

The point isn't the specific improvement. Your numbers will differ based on your workload, schema design, and query patterns. The point is the method: systematic, automated auditing catches problems while they're still trends, not incidents. You get to choose when to act, instead of reacting to user complaints after the fact.

This is the value proposition: faster preparation, earlier detection, and the freedom to act on your timeline instead of reacting to outages.

Next Steps

If you're spending half-days running manual health checks across your SQL Server estate, try this workflow. Two sessions. Copilot Basic. Your existing tools. See what you can build.

Free SQL Server Health Assessment (Complimentary) — I'm offering a complimentary health assessment for the first 10 readers who book this month. This is your entry point — a no-obligation audit to show you what's possible.

You'll receive:

• Full execution of Glenn Berry's diagnostic suite across your estate
• Prioritised remediation list with effort/impact scoring
• Identification of quick wins (fixes you can implement same-day)
• 30-minute consultation call to walk through findings
• No obligation, no sales pitch — this is the same audit framework I use for retained clients

Why consider this? Three reasons:

1. Speed: Results in days, not the 2-3 hours of build time (plus ongoing maintenance)
2. Independent perspective: DBAs often miss issues in their own environments — fresh eyes help
3. Complex environments: If your estate has constraints that make DIY challenging, we handle the heavy lifting

Beyond the free assessment: For specialist audits, custom monitoring solutions, or ongoing support tailored to your specific requirements, we offer paid engagements. This is expert work that takes time and costs money — because it's built for your estate, not a generic template.

But if you have 2-3 hours and want to build this yourself: do it. You'll learn, you'll own the solution, and you'll have a foundation to extend. The free assessment is for those who prefer done-for-you over DIY, or want to see what's possible before committing to building it themselves.

Book your assessment.

Newsletter — Join 800+ SQL Server professionals receiving the monthly SQLOPTIMISE digest. You'll get:

• Performance tuning tips you can apply immediately
• Automation patterns (like this workflow) with full scripts
• Real-world DBA war stories and lessons learned
• Early access to new tools and service offerings

No AI-generated filler. No marketing spam. Just practical insights from 15+ years in the trenches. Subscribe here.

---

Gareth Huggins is founder and lead DBA at SQLOPTIMISE, specialising in performance tuning and automation for mission-critical SQL Server estates across finance, e-commerce, and SaaS. Since 2008, he's helped organisations reduce query response times, prevent outages before they impact users, and build automation that frees DBAs from repetitive manual work. He still believes the best monitoring tool is a well-written DMV query — but he's not opposed to having AI write the first draft.

---

[AI-assisted content, reviewed by DEX-CON-ONE]