Building a Practical SQL Server Audit Method for Constrained Estates

Some SQL Server estates arrive in a tidy state: documented, monitored, consistently patched, and looked after by someone who knows exactly why each setting exists.

Many do not.

More often, especially in smaller businesses and long-running internal systems, SQL Server has been installed by whoever was available at the time. A vendor. A developer. An infrastructure engineer. A capable generalist, but not a DBA. Years later, the estate is still running, but nobody has a clean view of what is configured, what is risky, what has drifted, and what needs attention first.

That is the problem this method is designed for.

Not "how do I build a shiny monitoring product?" Not "how do I replace commercial tooling?" The goal is simpler and more useful: how do I build a repeatable audit method for SQL Servers that have grown organically, often under tight operational constraints, without needing a full platform rollout before I can see what is going on?

Here's how I built that kind of audit workflow using tools most DBAs already have access to: Copilot Basic, PowerShell, SSMS Central Management Server, dbatools, and Glenn Berry-style diagnostic queries. No Pro subscription. No new monitoring agent. No grand architecture project before the first useful finding.

The Problem: Inherited Estates Need Structure

Every DBA knows the drill. You inherit a set of servers and the first question is rarely "which dashboard should we buy?" It is usually:

• What SQL Server versions are we running?
• Which instances are under memory pressure?
• Where are waits getting worse?
• Which databases have risky configuration?
• Which jobs are failing quietly?
• Which servers have no obvious owner?
• What can be fixed quickly without creating change-control drama?

After 15+ years troubleshooting SQL Server performance, I've seen the same pattern repeatedly: the organisation does not need a perfect solution on day one. It needs a reliable way to build a picture of the estate and turn that picture into action.

The usual health-check ingredients are familiar:

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

Glenn Berry's diagnostic queries are excellent for this. The issue is not the diagnostic logic. The issue is the operating model around it: executing checks across a CMS-registered server list, collecting output, normalising formats, spotting exceptions, and producing something an IT lead can actually use.

That manual labour does not scale, and it is exactly where a constrained, PowerShell-first audit method helps.

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, installing agents, or buying expensive tooling before I had proved the audit method was useful.

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-style SQL Server diagnostic 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 becomes genuinely valuable. You're not installing new software on production servers. You're using AI to help shape scripts around the tools you already have permission to use. The intelligence comes from the prompt-and-review process; the execution uses your existing, approved stack.

For DBAs in locked-down environments, this workflow isn't just convenient. It is often the only realistic path to better evidence without waiting months for a new monitoring platform, agent rollout, or security review.

And this is where SQLOPTIMISE can help: If even Copilot-assisted DIY is difficult in your environment (no internet access from admin machines, strict change control, limited permissions, or no internal DBA capacity), we can help design and run an audit process that fits the constraints instead of pretending they do not exist.

What This Gets You

The end result isn't just a script. It is a practical audit method for estates that need order before they need another dashboard:

• 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, repeatable evidence. Manual spot checks do not scale. A consistent audit method does.

And critically: this is accessible to any DBA with a Copilot Basic subscription and the usual SQL Server administration tooling. You don't need Pro features. You don't need additional IDEs or paid monitoring tools before you start. You need PowerShell, dbatools, SSMS with CMS configured, and the willingness to invest two sessions in building something better than ad hoc manual checks.

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 approach for larger estates, but this Copilot-built audit was the proof of concept that showed the method was worth the investment. It turned scattered manual checks into a repeatable process and gave me a defensible way to prioritise remediation.

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 work from evidence instead of inherited assumptions.

Next Steps

If you're looking after SQL Servers that were set up by non-DBAs, inherited without documentation, or constrained by change control, try this workflow. Two sessions. Copilot Basic. Your existing tools. Build enough structure to see what matters.

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]