Build the Fence Before Letting in the AI: My sp_Blitz App, Part 2

In Part 1, I built the fence. sp_Blitz writes to DBA.dbo.BlitzResults, BlitzReader can SELECT from that one table and nothing else, and Claude Code wrote a report that compares the last two runs. But I ran everything myself. Every sp_Blitz run, and every report.

Part 2 is going to take me out of the loop. sp_Blitz runs on a schedule, and Claude Code runs the report on a schedule, as BlitzReader, without me. And nothing runs as sysadmin that doesn't have to.

Component	Details
SQL Server	2025 CU7, Enterprise Developer, local dev instance
sqlcmd	ODBC, 17.0.1000.7
AI agent	Claude Code, desktop app
Project folder	C:\BlitzApp

Step 1: Update the Ground Rules

Part 1's CLAUDE.md said not to connect to any database. That changes today. It can connect one way, as one login. It can also write scripts that change the server, but it never runs them. I do:

# Ground rules

- Work only in this project folder.
- Connect to SQL Server only with sqlcmd, as BlitzReader:
  sqlcmd -S sqlfingersinc -U BlitzReader -d DBA
  The password comes from the SQLCMDPASSWORD environment variable. Never put it on the command line.
- All SQL you run is SELECT-only, against DBA.dbo.BlitzResults.
- Every query has a TOP clause.
- You may write scripts that change the server. You never run them. I do.
- Ask before running any command.
- Log every attempt in attempts.md: what you tried, what worked, what didn't.

Don't forget what we learned in Part 1. CLAUDE.md is guidance. SQL Server is the fence.

Step 2: Give It a Connection

The password doesn't go on the command line. Microsoft says -P is insecure and recommends the SQLCMDPASSWORD environment variable instead (sqlcmd utility).

Claude Code can set environment variables for its sessions with an env block in its settings. I put it in .claude/settings.local.json, which applies only to me, in this project (Claude Code settings):

{
  "env": {
    "SQLCMDPASSWORD": "[USE A STRONG PASSWORD]"
  }
}

To be clear, the agent can read that file. The password is still stored in plain text, just not exposed on the command line. That's acceptable for this local experiment, but it's not how I'd manage credentials in production. SQL Server restricts BlitzReader to SELECT on one table. That's the fence.

First connection test, in a new session with the folder set to BlitzApp and the mode set to Manual:

Read CLAUDE.md. Connect to SQL Server with sqlcmd as BlitzReader and run this query, then show me the result:

SELECT TOP (5) CheckDate, COUNT(*) AS Findings
FROM DBA.dbo.BlitzResults
GROUP BY CheckDate
ORDER BY CheckDate DESC;

It didn't connect:

ODBC Driver 18 encrypts by default and validates the server's certificate, and a fresh local install uses a self-signed one. The agent explained that, logged the attempt, offered two fixes, and asked before trying either. I chose -C, which tells sqlcmd to trust the server certificate without validating it (sqlcmd utility). The connection is still encrypted. For a local dev instance, that's a reasonable trade.

Look at what's not on that command line: a password. sqlcmd picked it up from SQLCMDPASSWORD, exactly as planned.

It worked. The agent logged in as BlitzReader, read the table, and returned the results. It asked for permission before writing what it had done to attempts.md. I also added the -C into the sqlcmd line in CLAUDE.md, so the agent will connect the same way from here on.

Step 3: A Least-Privilege Job Owner

A scheduled job needs an owner, and that owner shouldn't be me, because I'm sysadmin. Any T-SQL job step owned by a non-sysadmin runs as that owner (sp_add_jobstep), so the owner needs exactly what sp_Blitz needs -- and nothing more.

Brent's original documentation says sp_Blitz needs sysadmin, but I decided to grant BlitzRunner only the minimum perms needed to get the job done. I gave EXECUTE on sp_Blitz and INSERT on BlitzResults. I figured I'd run sp_Blitz twice, first as myself and then as BlitzRunner, and compare the output. But...

A hard stop. sp_Blitz wouldn't run at all, and nothing was written to the table. So I granted VIEW SERVER STATE and tried again. This time it ran, but only partially:

So, I can see right out the gate that my initial permissions weren't enough. From there, sp_Blitz's own output told me what to grant next. I enabled one permission at a time, testing after each change. Eventually, BlitzRunner returned the same findings as my sysadmin run, with one exception I'll get to below. Three things I learned along the way:

• VIEW SERVER STATE is the only hard requirement. Without it, sp_Blitz stops before writing anything. With it, a non-sysadmin run works to some extent, and sp_Blitz adds a 'Some Checks Skipped' row to say it couldn't run everything.
• BlitzRunner needs SELECT on the database where sp_Blitz is installed -- DBA, in my case. sp_Blitz builds its list of databases it may check using fn_my_permissions, passing each database's name. Any database missing from that list is skipped. Microsoft's documentation says the securable should be NULL for a database (fn_my_permissions). In my tests, the answer depended on the database sp_Blitz ran from. SELECT in msdb alone changed nothing. SELECT in DBA, where sp_Blitz is installed, brought every database back to the list, and the backup, Page Verification and Agent findings came back as well.
• The version check calls the rest of the kit. sp_Blitz checks the versions of the other First Responder Kit procedures by executing them. Without EXECUTE on each one, it raises a permission error and records a 'Version Check Failed' finding.

The final sp_Blitz permission set, one script:

USE master;
GO
CREATE LOGIN BlitzRunner
    WITH PASSWORD = '[USE A STRONG PASSWORD]',
    CHECK_POLICY = ON;

CREATE USER BlitzRunner FOR LOGIN BlitzRunner;

GRANT VIEW SERVER STATE TO BlitzRunner;
GRANT VIEW ANY DEFINITION TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_MSgetalertinfo TO BlitzRunner;
GO

USE msdb;
GO
CREATE USER BlitzRunner FOR LOGIN BlitzRunner;
GRANT SELECT TO BlitzRunner;
GO

USE DBA;
GO
CREATE USER BlitzRunner FOR LOGIN BlitzRunner;
GRANT SELECT TO BlitzRunner;
GRANT INSERT ON dbo.BlitzResults TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_Blitz           TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_BlitzAnalysis   TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_BlitzBackups    TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_BlitzCache      TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_BlitzFirst      TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_BlitzIndex      TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_BlitzLock       TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_BlitzWho        TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_DatabaseRestore TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_ineachdb        TO BlitzRunner;
GRANT EXECUTE ON dbo.sp_kill            TO BlitzRunner;
GO

Everything there is read access or permission to call a procedure. No writes outside of BlitzResults, but EXECUTE deserves a closer look. Depending on how a procedure is written and its execution context, it may perform operations the caller couldn't execute directly. I granted these permissions to get the First Responder Kit working under BlitzRunner, but that doesn't make every procedure harmless.

The proof. My sysadmin run against BlitzRunner's final run, compared on CheckID, DatabaseName and Finding:

Seen	       CheckID	DatabaseName	Finding
Baseline only	   156	NULL	Oct 22 2026  6:06PM
BlitzRunner only   156	NULL	Oct 22 2026  8:03PM
BlitzRunner only   223	NULL	Some Checks Skipped

CheckID 156 is the run date, so it never matches between runs. The other findings matched, but CheckID 223, 'Some Checks Skipped,' remained. That's important. The matching findings are encouraging, but I can't claim identical coverage until I know exactly what was skipped.

Step 4: Schedule sp_Blitz

In Part 1, I ran sp_Blitz by hand. This time I want it running on a schedule, and the job will be owned by BlitzRunner, the login we configured above in Step 3. It has substantially fewer privileges than sysadmin, but I will say that I haven't established that every permission granted is strictly necessary.

My CLAUDE.md lets the agent write the scripts, but not run them. And even if it tried, it would fail, because its only connection is BlitzReader -- who has only SELECT on BlitzResults. This is my request to Claude Code:

What it wrote:

EXEC msdb.dbo.sp_add_job
    @job_name = N'DBA - sp_Blitz to BlitzResults',
    @owner_login_name = N'BlitzRunner';

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DBA - sp_Blitz to BlitzResults',
    @step_name = N'Run sp_Blitz',
    @command = N'EXEC DBA.dbo.sp_Blitz
    @CheckUserDatabaseObjects = 0,
    @OutputDatabaseName = ''DBA'',
    @OutputSchemaName = ''dbo'',
    @OutputTableName = ''BlitzResults'';';

-- schedule: every 30 minutes

That's trimmed to the parts that matter. The full script also stops without making changes if BlitzRunner doesn't exist or the job is already there, and it does everything in one transaction so a failure leaves nothing behind. I didn't change any thing. I ran it in SSMS, started the job once by hand, and checked the job history:

'Executed as user: BlitzRunner.' The job runs under the dedicated login, not my sysadmin account.

Step 5: Schedule the Report

Half of the job is done. sp_Blitz runs every 30 minutes and writes its findings to BlitzResults, and I'm not involved at all. The last half is the report, but right now I still have to run report.sql myself.

I want Claude Code to do that as well. Once a day, it should connect as BlitzReader, run the report, save the output, and log what it did, all without me sitting there watching it or having to give permission. Claude Code's desktop app can do this with a scheduled task (Claude Code: Desktop scheduled tasks). It runs right here on my machine, where everything the agent needs already lives: CLAUDE.md, report.sql, and sqlcmd.

This is what I asked for, in the same BlitzApp session:

Create a scheduled task for this project that runs every hour. Each run should read CLAUDE.md, run report.sql with sqlcmd as BlitzReader using the connect line in CLAUDE.md, save the output to a new file in a reports folder named report_YYYYMMDD_HHMM.txt, and log the run in attempts.md. It should not run anything else.

I initially asked for hourly runs, but that didn't make much sense. report.sql compares the last two sp_Blitz runs, and those are only 30 minutes apart. So I changed the schedule to once a day at 5:00PM. Before scheduling anything, Claude read report.sql and found it had changed since it wrote it:

The scheduled task already connects as BlitzReader, so those lines weren't needed. I let it remove them, and report.sql is SELECT-only again. Then it showed me the task it planned to create:

Read the second paragraph of the task instructions. It says I approved those commands for unattended runs, but I didn't. The agent wrote that about me into its own instructions, and every run would have started out believing it. I had that paragraph removed.

With that paragraph gone, the task follows CLAUDE.md and asks before running any command. The first scheduled run did exactly that. It started at 5:07 PM, stopped 13 seconds later, and waited nearly four hours for me. As-is, every command waits for permission to run, and that must be done every time.

Not quite the hands-off I was going for, so I added allow rules to my user settings file, C:\Users\rebec\.claude\settings.json. Claude Code applies those to scheduled task runs (Claude Code: Desktop scheduled tasks), so a command that matches a rule runs without asking. I wrote one for each thing the task does: get the timestamp, run the report, and log the run. The sqlcmd rule is listed twice, once for PowerShell and once for Bash, so it's covered either way:

{
  "permissions": {
    "allow": [
      "PowerShell(Get-Date -Format yyyyMMdd_HHmm)",
      "PowerShell(sqlcmd -S sqlfingersinc -U BlitzReader -d DBA -C -W -i report.sql -o reports\\report_*)",
      "Bash(sqlcmd -S sqlfingersinc -U BlitzReader -d DBA -C -W -i report.sql -o reports\\report_*)",
      "Edit(//c/BlitzApp/attempts.md)"
    ]
  }
}

Each rule targets a specific command or file. The * allows the report's file name to change with each run, but it's a wildcard, not a timestamp validator. These rules control when Claude asks for approval; they don't enforce what SQL Server will allow. I also had Claude rewrite the task to run each command on its own, because a chained command runs without asking only if every part of it matches a rule (Claude Code permissions). This is the one place I loosened the agent's permissions, but the fence didn't move. The command still connects as BlitzReader.

Step 6: Hands Off

With the rules in place, I clicked Run now on the task and kept my hands off. Nothing asked for permission, and nothing waited on me. It checked report.sql, got the timestamp, ran the report as BlitzReader, saved the output, logged what it did, and completed the task with this output:

Look at the first item under 'Things to look at' in that screenshot. The run didn't start late. I started it at 19:29 with Run now. The agent didn't know that, so it guessed, and reported the guess as something for me to look at.

The report ran. The output was saved. The log was written. All of that worked, but the agent also gave me an explanation for something it didn't know, and presented it as a finding. I think it's important to see that successful execution doesn't necessarily mean accurate reporting.

Limits

• One instance. Everything here is from my local dev instance. BlitzRunner returned the same findings as my sysadmin login, but it also reported that some checks were skipped. I haven't established identical coverage, and the permissions required may differ on another server.
• The app has to be open. Claude Code's scheduled tasks run only while the desktop app is running and the computer is awake (Claude Code: Desktop scheduled tasks). The sp_Blitz job doesn't care. The report does.
• An allow rule matches text, not intent. Claude Code says its command rules aren't a security boundary around a program (Claude Code permissions). Mine are narrow, but the real fence is still BlitzReader.
• The table grows. At 37 rows a run, every 30 minutes, BlitzResults gains 1,776 rows a day. There's no cleanup yet.
• The agent's log isn't proof. attempts.md is what the agent says it did, and twice now it has guessed wrong about its own runtimes.

What's Next

In Part 3, I'll turn this into something I'd actually run. sp_Blitz moves to a realistic schedule, BlitzResults gets a cleanup, and the report stops calling noise a change. And since the agent has now guessed wrong about its own runs, I'll check what it says it did against SQL Server's own record of what BlitzReader actually ran.

More to Read

sqlfingers: Build the Fence Before Letting in the AI: My sp_Blitz App, Part 1
Brent Ozar: SQL Server First Responder Kit
Brent Ozar: Using Claude Code with SQL Server and Azure SQL DB

Automatic Index Compaction: What Happens When I Rebuild Anyway?

Bob Ward recently posed an interesting question on Data Exposed: Is automatic index compaction the end of index maintenance?

That's a pretty big question for those of us who've spent yeeeears managing indexes, and Bob didn't just talk about it. He published the scripts to demonstrate what it can do, so I had to try it myself. 😀

Bob's demo was on Azure SQL Hyperscale with 8 vCores. I used something a bit more modest with a free Azure SQL Database with 2 vCores. Otherwise, I followed his experiment and watched automatic compaction recover the space left behind after deleting nearly half a million rows.

It worked. Very well. But the scan after compaction was slower than I expected, and that's where this got interesting.

Side note; I actually covered this feature back in April in Your Nightly Index Rebuild Job May Be On Its Way Out -- but I only talked about it. This time I get to show what it actually does.

Bob's Demo, My Smaller Database

My test used the Azure SQL Database free offer: General Purpose, serverless, Standard-series (Gen5), 2 vCores and 32 GB of storage. The offer includes a monthly allowance of 100,000 vCore seconds and 32 GB of data per database, subject to terms and limits. This is not Bob's 8-vCore Hyperscale environment, and that distinction matters when we get to scan times.

The demo scripts are in microsoft/bobsql, demos/auto_index_compaction. The test creates a million-row table with a GUID clustered key and fixed-width char(100) data, rebuilds the index to establish a dense starting point -- and then deletes roughly half the rows across the table.

For the scan measurements, I used Bob's single-threaded, cold-cache COUNT_BIG test:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT COUNT_BIG(*) AS row_count
FROM dbo.aic_demo
OPTION (MAXDOP 1);

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

The workload was intentionally simple: one table, one full scan, and no concurrent application workload. I followed Bob's demonstration with one additional UPDATE to address a compaction timing issue that I'll explain shortly.

Putting the Index to the Test

After setup, the million-row table required 17,005 logical reads for the scan, which completed in 867 ms. With that baseline established, I ran Bob's degradation script. It deletes rows using CHECKSUM(id) % 2 = 0, spreading the deletions throughout the clustered index rather than removing one continuous range.

Measurement	Baseline	After Delete
Row count	1,000,000	500,252
Logical reads	17,005	34,058
Elapsed time	867 ms	2,143 ms

Half the rows were gone, yet the scan required twice as many logical reads and took more than twice as long. The degradation script also reported 33,899 pages at just 52.46% density. Bob's README explains the role of Accelerated Database Recovery (ADR) in that the versioning overhead from the deletes can cause previously full pages to split. In my run, that effect was substantial.

Fewer rows, more work, and a slower scan. That's exactly the kind of problem automatic index compaction is intended to address.

Compaction Was On. Nothing Happened.

I enabled automatic index compaction after the delete, then checked its progress. The database reported the feature as ON. The page count didn't move. Ten minutes later, it still hadn't moved.

This wasn't evidence that compaction had failed. It was a timing problem. Automatic compaction works in conjunction with version cleanup, and MSFT's FAQ explains why enabling it after the relevant modifications may leave existing low-density pages untouched. Bob's README also calls attention to the timing.

For a planned large delete, that's worth knowing before you begin. Enabling compaction afterward does not guarantee that it will revisit the pages you've already changed.

But I still wanted to see what compaction could accomplish with those half-empty pages. Since enabling it after the delete hadn't produced any change, I needed to generate new activity while the feature was enabled.

So I updated every remaining row. This was my addition, not part of Bob's original test:

UPDATE dbo.aic_demo
SET val = val + 1;

That affected 500,252 rows and generated new modifications for cleanup to process. My final compacted state was reached through a slightly different path than Bob's original demonstration.

Now Compaction Had Something to Do

A few minutes later, after things settled, two consecutive progress checks using Bob's 05-check-progress.sql query returned the same result:

Rows:                 500,252
Page count:             8,563
Page density:          98.86%
Fragmentation:         96.55%

From 33,899 pages down to 8,563, without rebuilding the index. That's roughly a 75% reduction in allocated index pages from the degraded state. Automatic compaction had done exactly what it was designed to do -- restoring page density and substantially reducing the number of pages.

But -- the fragmentation was still sitting at 96.55%. That's an important distinction because automatic compaction is designed to improve page density, not eliminate fragmentation. Improving page density isn't necessarily going to restore the logical ordering of the index pages. Microsoft says that here, and my results illustrated it very clearly.

So I had an index that was nearly 99% dense, with about a quarter of its previous page count, but still heavily fragmented. What's that going to mean for query performance?

The Scan Was the Surprise

With so many fewer pages to read, I expected the scan to improve, and in one respect, it did. Logical reads dropped from 34,058 in the degraded state to only 8,722 post compaction. But even with that reduction, the scan still took considerably longer to complete.

The first scan during the final stages of compaction took 6,931 ms, and another after the page count settled took 7,045 ms. Both substantially slower than the 2,143 ms scan before compaction, regardless of the large improvement in page density. So, with fragmentation still at 96.55%, I was curious what a rebuild might change.

What If I Rebuild It Anyway?

I ran one final statement:

ALTER INDEX pk_aic_demo
ON dbo.aic_demo REBUILD;

When complete, the index had 8,479 pages at 99.84% density, compared with 8,563 pages at 98.86% density after compaction. That's minimal difference, but the fragmentation dropped from 96.55% to 0.00%.

Here I ran the same cold-cache scan again and it completed in 439 ms. A second run took 440 ms. Both reported 8,508 logical reads, with read-ahead reads of 8,503.

That's a very different result from the compacted scans. And unlike the original demonstration, the question wasn't whether compaction could reclaim space. Obviously, it could. The question now was whether the rebuild still provided any gains after the compaction had been done, and the density restored.

All Four States, Side by Side

State	Logical reads	Read-ahead reads	Elapsed (ms)
Baseline	17,005	16,998	867
Degraded	34,058	34,049	2,143
Compacted	8,722	32,836	7,045
Rebuilt	8,508	8,503	440

The baseline and degraded states were each measured once. For the compacted and rebuilt states, I ran the scan twice and used the second result in the comparison above.

The rebuilt scan finished about 16 times faster than the compacted scan in this test, even though the two indexes had almost the same page count and density.

Fragmentation may have contributed to that difference. Microsoft documents how gaps left by compaction can reduce the size of read-ahead I/O. But this test wasn't designed to isolate fragmentation from other factors affecting execution time, so I can't really attribute the entire improvement to the rebuild correcting page order.

So Is it the End of Index Maintenance?

Not on the strength of this test. Automatic compaction was very effective at restoring page density. It took an index that was only 52.46% dense and brought it to 98.86%, reducing its page count remarkably without a rebuild. That's a very meaningful benefit, especially for workloads that create low-density pages through recurring deletes and updates.

The rebuild produced a much faster full scan under my test conditions, but that doesn't mean every compacted index needs rebuilding. And it certainly doesn't mean this 2-vCore General Purpose result predicts what Bob would see on Hyperscale. What I believe it does tell us is that reclaiming space and improving scan performance are not always the same thing.

There are two operational details I'd keep in mind. First, automatic compaction isn't a retroactive fix for every low-density page (the timing of modifications matters). Second, automatic compaction does not update statistics, so it doesn't replace statistics maintenance.

For me, the takeaway isn't to disable compaction or keep rebuilding everything on a schedule. It's to let compaction handle what it handles well, and then to use actual workload measurements to decide whether any index maintenance is still needed.

What This Test Doesn't Prove

This was one table and one single-threaded, cold-cache full scan on a 2-vCore General Purpose serverless database. Baseline and degraded scans were each measured once and the compacted and rebuilt scans were measured twice. I did not test seeks, range queries, warm-cache performance, concurrency, or any other service tiers. I also added a full-table UPDATE after missing the initial compaction window, which changes the sequence of work relative to Bob's demo.

Those limits matter. I wouldn't use these numbers to predict a production performance gain, but I would use them as a reason to measure before removing an established maintenance operation.

Cleanup

Bob's 06-cleanup.sql script removes the demo table and turns the feature off:

DROP TABLE IF EXISTS dbo.aic_demo;
GO

ALTER DATABASE CURRENT
SET AUTOMATIC_INDEX_COMPACTION = OFF;
GO

If you're testing on the free offer, disconnect SSMS when you're finished, including Object Explorer. Open sessions can prevent serverless auto-pause and consume your included vCore seconds.

More to Read

Bob Ward: The End of Index Maintenance? — Data Exposed
Bob Ward's demo scripts — microsoft/bobsql
Microsoft Learn: Automatic index compaction
Microsoft Tech Community: Stop defragmenting and start living
Azure SQL Database free offer
SQLFingers: Your Nightly Index Rebuild Job May Be On Its Way Out

Build the Fence Before Letting in the AI: My sp_Blitz App, Part 1

Last month, Grant Fritchey's AI coding agent went looking through his laptop for a running PostgreSQL instance. It found one that was shut down, and started it up on its own, without being asked. The agent was trying to validate its changes before deploying them. Good instinct. Unauthorized action. You can read the whole story in part six of his Simple Talk series.

Grant's takeaway? Guardrails in the prompt weren't enough. You need a real barrier between the AI and anything that matters.

I have never used an AI coding agent. Yes, I know. WTH am I waiting for...

This is it. I'm building an app around Brent Ozar's sp_Blitz, and I'm documenting the process in a series. But before I let an AI agent anywhere near my SQL Server, I want to establish what it can and cannot do. First the fence. Then the AI.

The App

Brent's sp_Blitz is the health check many of us already use. Run it once, and it tells you what's wrong today. Save the output run after run, and you have a history of every finding on the server(s) you run it against.

My app turns that history into a comparison -- what's new since last run, what's been fixed, and what keeps coming back. The data collection is already built into sp_Blitz. Pass it @OutputDatabaseName, @OutputSchemaName and @OutputTableName, and it creates the table, if needed, and writes the results to it. That table is my app's data source.

Credit where it's due: when Brent added table output in sp_Blitz v22, he included the server name and check date so we could compare runs (sp_Blitz v22). And in March, he wrote about using Claude Code with SQL Server, including the importance of thinking about where you point it and what login you give it (Using Claude Code with SQL Server).

The comparison builds on Brent's work. The fence, and what the agent does inside it, are mine.

Each post in this series adds something new. In Part 1, I keep it simple: run sp_Blitz multiple times, save the findings, and report what's new, what's changed, and what's stayed the same.

The setup:

Piece	Choice
SQL Server	2025 CU7, local dev instance
Health check	sp_Blitz, First Responder Kit
AI agent	Claude Code, desktop app

Why Not Just Point It at SQL Server?

Because it would connect as me. If the agent uses Windows authentication, it gets my login, and my login is sysadmin.

Claude Code has its own permission rules, and it reads a rules file called CLAUDE.md at the start of every session. But per Anthropic's own documentation, CLAUDE.md only shapes what Claude tries to do. It doesn't change what Claude Code allows (Claude Code permissions). Guidance isn't a barrier.

So I have three layers, and only one of them is mine to enforce:

Layer	Enforced by
CLAUDE.md	The agent interprets it; SQL Server doesn't enforce it.
Claude Code permissions	Claude Code.
SQL Server permissions	SQL Server. The fence.

I'll use all three -- but trust the last one the most.

Step 1: Install the First Responder Kit

Brent recommends master, but any database is supported, so I put it in my DBA database. Create that first:

CREATE DATABASE DBA;
GO

Then grab the latest release and run Install-All-Scripts.sql in DBA. It installs sp_ineachdb with sp_Blitz, which sp_Blitz needs in the same database (README).

Step 2: Run sp_Blitz Into a Table

Run sp_Blitz with the output parameters. @CheckUserDatabaseObjects = 0 skips the checks inside user databases, which is the fastest way to run it:

EXEC DBA.dbo.sp_Blitz
    @CheckUserDatabaseObjects = 0,
    @OutputDatabaseName = 'DBA',
    @OutputSchemaName = 'dbo',
    @OutputTableName = 'BlitzResults';

Confirm it landed:

SELECT COUNT(*) AS Findings
FROM DBA.dbo.BlitzResults;

Step 3: Build the Fence

The agent gets its own login. It can read one table. That's it.

USE master;
GO
CREATE LOGIN BlitzReader
    WITH PASSWORD = '[USE A STRONG PASSWORD]',
    CHECK_POLICY = ON;
GO

USE DBA;
GO
CREATE USER BlitzReader FOR LOGIN BlitzReader;
GRANT SELECT ON dbo.BlitzResults TO BlitzReader;
GO

That's the fence. BlitzReader can read one table, BlitzResults, and it was granted nothing else. It can't run sp_Blitz, and it can't touch anything else in DBA. When the agent connects, it connects as BlitzReader. Whatever it attempts to do, SQL Server decides what to permit.

One note: BlitzReader is a SQL login, so the instance needs to be in SQL Server and Windows Authentication mode before anything can connect with it.

Step 4: Give It Something to Find

To test the comparison, I needed something to change. I made a throwaway database and gave sp_Blitz two things to flag, one at a time: Auto-Shrink and Page Verification.

CREATE DATABASE BlitzDemo;
GO
ALTER DATABASE BlitzDemo SET AUTO_SHRINK ON;
GO

EXEC DBA.dbo.sp_Blitz
    @CheckUserDatabaseObjects = 0,
    @OutputDatabaseName = 'DBA',
    @OutputSchemaName = 'dbo',
    @OutputTableName = 'BlitzResults';
GO

ALTER DATABASE BlitzDemo SET AUTO_SHRINK OFF;
ALTER DATABASE BlitzDemo SET PAGE_VERIFY NONE;
GO

EXEC DBA.dbo.sp_Blitz
    @CheckUserDatabaseObjects = 0,
    @OutputDatabaseName = 'DBA',
    @OutputSchemaName = 'dbo',
    @OutputTableName = 'BlitzResults';
GO

Between the last two runs, Auto-Shrink got fixed and Page Verification is new. Everything else should be the same. Three runs, each with its own CheckDate:

SELECT CheckDate, COUNT(*) AS Findings
FROM DBA.dbo.BlitzResults
GROUP BY CheckDate
ORDER BY CheckDate;

Step 5: Now We Install Claude Code

I'm using the Claude desktop app. It includes Claude Code, so there's no separate CLI install, but it does require a paid subscription (Claude Code overview). Brent recommends starting with the terminal version instead, because its documentation is much better (Using Claude Code with SQL Server). I wanted to keep the first part simple, so I went with the app.

Download, install, sign in. There's no password to remember -- you sign in with the email of your Claude.ai account and you'll receive an emailed login link. Claude Code is the </> icon at the top left, next to the chat icon.

You need to give Claude a project folder. I created C:\BlitzApp. Everything the agent writes goes into this directory.

Step 6: The Ground Rules

Now the rules file for Claude. This is my CLAUDE.md, saved in C:\BlitzApp. Remember, it's guidance, not enforcement. The last rule comes straight from Grant's lessons: he said he'd now make the AI keep a log of what worked and what didn't.

# Ground rules

- Work only in this project folder.
- Do not connect to any database. I run every query.
- All SQL you write is SELECT-only, against DBA.dbo.BlitzResults.
- Every query has a TOP clause.
- Ask before running any command.
- Log every attempt in attempts.md: what you tried, what worked, what didn't.

Notice the second rule. In this part of the series, the agent writes and I run. It doesn't touch SQL Server at all.

Step 7: The First Prompt

In the Claude Code tab, I set the folder to C:\BlitzApp and the permission mode to Manual -- 'Always ask before making changes'. Then I gave it this, verbatim:

Read CLAUDE.md first.

The table DBA.dbo.BlitzResults holds sp_Blitz output from several runs. Every row from one run shares the same CheckDate. Columns: ID int, ServerName nvarchar, CheckDate datetimeoffset, Priority tinyint, FindingsGroup varchar, Finding varchar, DatabaseName nvarchar, URL varchar, Details nvarchar, QueryPlan xml, QueryPlanFiltered nvarchar, CheckID int.

Write a query that compares the two most recent runs and labels each finding:
- New: in the latest run only
- Fixed: in the previous run only
- Changed: in both runs, but Details differ
- Same: in both runs, Details unchanged

Match findings on CheckID and DatabaseName. DatabaseName can be NULL.

Save the query as report.sql. Do not run it. I will run it myself.

After about 45 seconds, it asked permission to write the file:

Note that orange arrow. 'Always allow' is the default, but I used the drop down and picked 'Allow once' instead. I want to approve every action myself -- at least for now.

Then I received this as Claude Code's response:

I saved the query as report.sql and logged this attempt in attempts.md. I haven't run it or connected to anything, so it's untested until you run it.
How it works:

* Picking the two runs: it takes the two most recent distinct `CheckDate` values.
* Matching: findings match on `CheckID` + `DatabaseName`, and two NULL DatabaseNames count as a match.
* Repeated findings: sp_Blitz can return several rows for the same CheckID and database. A plain join would pair every one of those rows with every other, so the query matches in two passes:
   1. Rows with the same key and identical Details are labelled Same.
   2. Remaining rows with the same key are paired in ID order and labelled Changed. Anything still unpaired is New (latest run only) or Fixed (previous run only).
* Output: status, priority, finding, CheckID, DatabaseName, previous and latest Details, and both row IDs. New findings sort first, then Changed, Fixed and Same.

Before you run it:

* TOP clauses: only the final SELECT (`TOP (1000)`) and the step that picks the run dates (`TOP (2)`) have one. I left TOP off the steps in between because a row limit there could silently drop rows and give them the wrong label. If you want TOP on every SELECT, tell me and I'll add it.
* Multiple servers: if the table holds runs from several servers with different CheckDates, "the two most recent runs" spans all servers. The query would then need to compare each server separately.
* Noisy Details: some sp_Blitz Details include dates, counts or sizes, so those findings will show as Changed on every run.

Look at that first 'Before you run it' item. My rules said every query gets a TOP clause. Claude didn't follow that rule everywhere, explained why, and asked before changing anything. Its reasoning was sound -- a TOP on an intermediate step could silently drop rows and mislabel findings.

The rules shaped the agent's work, but did not control every decision. That's guidance, not enforcement.

The query it wrote, verbatim:

-- report.sql
-- Compares the two most recent sp_Blitz runs in DBA.dbo.BlitzResults.
-- A run = all rows sharing one CheckDate. Findings match on CheckID + DatabaseName (NULL-safe).
--
--   New     : in the latest run only
--   Fixed   : in the previous run only
--   Changed : in both runs, Details differ
--   Same    : in both runs, Details unchanged
--
-- sp_Blitz can return more than one row for the same CheckID + DatabaseName.
-- To avoid a many-to-many join, rows are paired in two passes:
--   1. Pair rows whose Details are identical            -> Same
--   2. Pair the leftovers by key, in ID order           -> Changed
--      Anything still unpaired                          -> New / Fixed
-- When the key is unique per run, this is a plain one-to-one match.
--
-- SELECT-only. If only one run exists, every row comes back as New.

WITH Runs AS (
    SELECT TOP (2)
           CheckDate,
           ROW_NUMBER() OVER (ORDER BY CheckDate DESC) AS RunNum
    FROM DBA.dbo.BlitzResults
    GROUP BY CheckDate
    ORDER BY CheckDate DESC
),
Cur AS (   -- latest run
    SELECT b.ID, b.CheckID, b.DatabaseName, b.Priority, b.FindingsGroup, b.Finding, b.Details,
           ROW_NUMBER() OVER (PARTITION BY b.CheckID, b.DatabaseName, b.Details ORDER BY b.ID) AS DupNum
    FROM DBA.dbo.BlitzResults AS b
    JOIN Runs AS r ON r.CheckDate = b.CheckDate AND r.RunNum = 1
),
Prev AS (  -- previous run
    SELECT b.ID, b.CheckID, b.DatabaseName, b.Priority, b.FindingsGroup, b.Finding, b.Details,
           ROW_NUMBER() OVER (PARTITION BY b.CheckID, b.DatabaseName, b.Details ORDER BY b.ID) AS DupNum
    FROM DBA.dbo.BlitzResults AS b
    JOIN Runs AS r ON r.CheckDate = b.CheckDate AND r.RunNum = 2
),
-- Pass 1: same key, same Details
SamePairs AS (
    SELECT c.ID AS LatestID, p.ID AS PreviousID,
           c.CheckID, c.DatabaseName, c.Priority, c.FindingsGroup, c.Finding,
           p.Details AS PreviousDetails, c.Details AS LatestDetails
    FROM Cur AS c
    JOIN Prev AS p
      ON  (c.CheckID      = p.CheckID      OR (c.CheckID      IS NULL AND p.CheckID      IS NULL))
      AND (c.DatabaseName = p.DatabaseName OR (c.DatabaseName IS NULL AND p.DatabaseName IS NULL))
      AND (c.Details      = p.Details      OR (c.Details      IS NULL AND p.Details      IS NULL))
      AND c.DupNum = p.DupNum
),
-- Rows not paired in pass 1, renumbered within the key
CurLeft AS (
    SELECT c.ID, c.CheckID, c.DatabaseName, c.Priority, c.FindingsGroup, c.Finding, c.Details,
           ROW_NUMBER() OVER (PARTITION BY c.CheckID, c.DatabaseName ORDER BY c.ID) AS KeyNum
    FROM Cur AS c
    LEFT JOIN SamePairs AS s ON s.LatestID = c.ID
    WHERE s.LatestID IS NULL
),
PrevLeft AS (
    SELECT p.ID, p.CheckID, p.DatabaseName, p.Priority, p.FindingsGroup, p.Finding, p.Details,
           ROW_NUMBER() OVER (PARTITION BY p.CheckID, p.DatabaseName ORDER BY p.ID) AS KeyNum
    FROM Prev AS p
    LEFT JOIN SamePairs AS s ON s.PreviousID = p.ID
    WHERE s.PreviousID IS NULL
)
SELECT TOP (1000)
       x.Status, x.Priority, x.FindingsGroup, x.Finding, x.CheckID, x.DatabaseName,
       x.PreviousDetails, x.LatestDetails, x.PreviousID, x.LatestID
FROM (
    SELECT 'Same' AS Status,
           s.Priority, s.FindingsGroup, s.Finding, s.CheckID, s.DatabaseName,
           s.PreviousDetails, s.LatestDetails, s.PreviousID, s.LatestID
    FROM SamePairs AS s

    UNION ALL

    -- Pass 2: leftovers with the same key are Changed; unpaired rows are New or Fixed
    SELECT CASE WHEN c.ID IS NOT NULL AND p.ID IS NOT NULL THEN 'Changed'
                WHEN c.ID IS NOT NULL                      THEN 'New'
                ELSE                                            'Fixed'
           END,
           COALESCE(c.Priority,      p.Priority),
           COALESCE(c.FindingsGroup, p.FindingsGroup),
           COALESCE(c.Finding,       p.Finding),
           COALESCE(c.CheckID,       p.CheckID),
           COALESCE(c.DatabaseName,  p.DatabaseName),
           p.Details, c.Details, p.ID, c.ID
    FROM CurLeft AS c
    FULL OUTER JOIN PrevLeft AS p
      ON  c.KeyNum = p.KeyNum
      AND (c.CheckID      = p.CheckID      OR (c.CheckID      IS NULL AND p.CheckID      IS NULL))
      AND (c.DatabaseName = p.DatabaseName OR (c.DatabaseName IS NULL AND p.DatabaseName IS NULL))
) AS x
ORDER BY CASE x.Status WHEN 'New' THEN 1 WHEN 'Changed' THEN 2 WHEN 'Fixed' THEN 3 ELSE 4 END,
         x.Priority, x.CheckID, x.DatabaseName, x.LatestID, x.PreviousID;

I ran it as BlitzReader, the fenced login:

USE DBA;
GO
EXECUTE AS LOGIN = 'BlitzReader';

-- report.sql

REVERT;

The results:

Check the Status column. Row 1 is New: Page Verification. Row 2 is Fixed: Auto-Shrink. Everything else is the Same -- exactly the two changes I made in Step 4.

What's Next

I'm leaving DBA, BlitzResults and BlitzReader in place for Part 2, where I'll schedule sp_Blitz and give Claude Code its own connection as BlitzReader, so it can query the results directly.

In Part 3, I'll compare what the agent says it did with SQL Server's own record of what Claude actually ran. Because I want to know what it really did, not just what it tells me it did.

More to Read

Grant Fritchey: How I built an app just by talking to an AI LLM (the series)
SQL Server First Responder Kit on GitHub
Brent Ozar: sp_Blitz
Brent Ozar: Using Claude Code with SQL Server and Azure SQL DB
Claude Code: Configure permissions

SQL Server CHOOSE(): Simple Enough, Until It Isn't

SQL Server has quite a few built-in functions that don't get much attention, and CHOOSE() is one of them. It's been around since SQL Server 2012, but I don't really see it used too often.

The function is straightforward. Give it a position and a list of values, and it returns the value at that position. In some cases, it can eliminate a lengthy CASE expression and make the query much easier to read.

There are a few things worth knowing before using it, though. The positions start at 1, not 0, an invalid position returns NULL, and SQL Server's data type precedence rules still apply. Those details matter, particularly when the position comes from data rather than a value you've supplied yourself.

Let's look at where CHOOSE() makes sense, how it compares with CASE, and a few examples where the results might not be what you expected.

What CHOOSE() Does

The syntax is simple: CHOOSE(index, value1, value2, ...). The index identifies which item in the list to return.

SELECT
    CHOOSE(1, 'Development', 'Test', 'Production') AS Environment1,
    CHOOSE(2, 'Development', 'Test', 'Production') AS Environment2,
    CHOOSE(3, 'Development', 'Test', 'Production') AS Environment3;

For a simple positional selection, CHOOSE() can be easier to read than a CASE expression. Both return the same result when the index is 1, 2, or 3. Here's a quick comparison using an EnvironmentID of 2:

DECLARE @EnvironmentID int = 2;

SELECT
    CASE @EnvironmentID
        WHEN 1 THEN 'Development'
        WHEN 2 THEN 'Test'
        WHEN 3 THEN 'Production'
    END AS CaseResult;

SELECT CHOOSE(@EnvironmentID, 'Development', 'Test', 'Production') AS ChooseResult;

You can change @EnvironmentID to 1 or 3 and see that both expressions still return the same result.

That's where CHOOSE() earns its place. It can make a straightforward positional lookup compact without making it difficult to follow. But it isn't a replacement for every CASE expression, especially when conditions are more complex or the values aren't sequential.

The Index Starts at 1

The first detail to remember is that CHOOSE() uses a 1-based index.

Suppose a table stores status codes beginning at zero. A CASE expression might look like this:

CASE StatusID
    WHEN 0 THEN 'Unknown'
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Temp'
END

Replacing it directly with CHOOSE(StatusID, 'Unknown', 'Active', 'Inactive', 'Temp') would not produce the same results. Here's a small example using rows rather than individual function calls:

SELECT
    s.StatusID,
    CHOOSE(s.StatusID, 'Unknown', 'Active', 'Inactive', 'Temp') AS DirectChoice,
    CHOOSE(s.StatusID + 1, 'Unknown', 'Active', 'Inactive', 'Temp') AS CorrectedChoice
FROM (VALUES (0), (1), (2), (3)) AS s(StatusID);

Adding 1 to the index works here because the codes are consecutive and start at zero. If the codes aren't consecutive, or their meaning can change, a CASE expression or a proper lookup table is generally a better fit.

What Happens Outside the List?

If the index is less than 1 or greater than the number of values supplied, CHOOSE() returns NULL. A NULL index also returns NULL.

SELECT
    CHOOSE(0, 'Dev', 'Test', 'Prod') AS IndexZero,
    CHOOSE(4, 'Dev', 'Test', 'Prod') AS PastTheEnd,
    CHOOSE(-1, 'Dev', 'Test', 'Prod') AS NegativeIndex,
    CHOOSE(NULL, 'Dev', 'Test', 'Prod') AS NullIndex;

That isn't necessarily a problem. A CASE expression without a matching branch or an ELSE also returns NULL. The important thing is to decide whether NULL is an acceptable result for the data you're working with. If you need an explicit fallback, CASE makes that very clear.

What If the Index Has a Decimal?

CHOOSE() expects an index that can be converted to INT. A numeric value with a fractional portion is converted to an integer, which truncates rather than rounds the value.

SELECT
    CHOOSE(2.9, 'Development', 'Test', 'Production') AS SelectedEnvironment;

The index becomes 2, so the second item is returned. That's predictable once you know the conversion rule, but worth checking if the index is calculated rather than stored as an integer.

Data Type Precedence Still Applies

The values supplied to CHOOSE() don't have to be written with the same data type, but SQL Server must determine a single return type. It uses the type with the highest precedence among the supplied values, just as it does for a CASE expression.

SELECT
    CHOOSE(2, 100, '200', '300') AS NumericChoice; 

In that last example, int has higher precedence than varchar, so the expression's result type is INT. The selected value, 100, is already an integer.

Now select a string that cannot be converted to an integer:

SELECT
    CHOOSE(2, 100, 'Active', 'Inactive') AS MixedTypes;

The second value is selected, but SQL Server has determined that the result must be an int. Converting 'Active' to INT fails. The lesson isn't that CHOOSE() can't handle mixed types; it's that implicit conversion rules still matter. When the values represent the same kind of information, use consistent data types.

When Would I Use CHOOSE()?

I like CHOOSE() for a short, fixed list when the index is known to be within range. One example is selecting a month label from a month number:

SELECT
    CHOOSE(MONTH(GETDATE()),
           'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
           'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') AS MonthAbbreviation;

MONTH() returns a number from 1 through 12 for a valid date, so its range lines up with the twelve items in the list. For this kind of small, fixed selection, CHOOSE() is concise and easy to understand.

For status codes with gaps, complicated conditions, or values maintained as business data, I'd use CASE or a lookup table instead. And if you're converting an existing CASE expression to CHOOSE(), make sure the numeric values really do correspond to positions in the list.

CHOOSE() isn't complicated, and there's no reason to avoid it. For a short, fixed list of values, it can be considerably easier to read than a CASE expression. Like most T-SQL functions, it has a few behaviors worth understanding, but once you know them, it's another clever option to use.

More to Read

MSFT -- CHOOSE (Transact-SQL)
MSFT -- IIF (Transact-SQL)
MSFT -- CASE (Transact-SQL)
MSFT -- Data type precedence (Transact-SQL)

When AI Gets the Query Wrong and SQL Server Doesn't Complain

SQL Server will tell you when a column doesn't exist. It won't tell you when you've selected the wrong one.

That's an important distinction when you're running T-SQL generated by AI. An invented column name might produce an immediate error, but a perfectly valid column can return perfectly valid data that has absolutely nothing to do with what you're looking for.

You asked the AI assistant for a query, it wrote one referencing a column called Email. The name looked right. The query looked right. And the whole answer arrived with the same confidence you'd expect from someone who knew your database. So you ran it.

Pinal Dave wrote this week about why AI sounds confident when it is wrong -- that the tone of an answer tells you nothing about whether it is true, and that a model with no schema in front of it will invent a column name like Email because that is what tables usually have. He is right. But his post doesn't get into how you catch it in T-SQL, and that is what I want to show here.

An AI-generated query can get a column wrong in two very different ways, and the more dangerous one won't actually produce an error. We'll start with the easy one.

The column does not exist

Here is a CustomerOrders table where there is no Email column in it. The email is actually in another column named ContactEmail, but the AI didn't know that and guessed incorrectly:

SELECT CustomerID, FullName, Email
FROM dbo.CustomerOrders;

Of course, SQL Server stops you cold:

This is the good outcome. The guess was wrong, the column doesn't exist, and SQL Server rejects the query with an error. In this example, nothing was changed. The mistake was immediately obvious and you can correct it easily.

If only every AI-generated query made its mistakes this obvious.

The problem is you only get that error if the invented name happens not to exist. So before you run anything an AI assistant wrote, spend one query confirming what the table actually has:

SELECT c.name AS column_name,
       t.name AS data_type,
       c.max_length,
       c.is_nullable
FROM sys.columns c JOIN sys.types t 
  ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.CustomerOrders')
ORDER BY c.column_id;

There is the truth about this table, straight from the catalog. No Email. That column is ContactEmail, and now you know it, because you checked the schema instead of trusting AI's guess. You can also verify the column details in INFORMATION_SCHEMA.COLUMNS, but the catalog views expose more complete metadata. Either way, you're checking the actual table definition rather than relying on AI's assumptions.

If that were the whole problem, you could rely on Msg 207 to protect you and move on. But no. It's not that easy.

The dangerous case: the column exists - but it is the wrong one

Look at that column list again. There are two date columns: OrderDate and ShipDate. Now suppose you asked for a report of when each order shipped, and the AI assistant -- again, without your schema -- reached for the column that sounds like the obvious one:

SELECT CustomerID, FullName, OrderDate AS [When it shipped (per AI)]
FROM dbo.CustomerOrders;

No error. The query ran, returned data, and handed you a ship-date report -- except none of those are ship dates. They are order dates. OrderDate is a real column, so nothing stopped the query. It simply answered the wrong question in the same confident format as the right one. This is the kind of result that ends up in a presentation, an invoice, or an SLA report, and nobody thinks twice about it. Why would they? The query ran, the data looked right, and SQL Server didn't complain. Except the answer was wrong.

Catching this one takes the same habit, carried one step further. The name checked out, so the name is not the test. The values are. Put the two date columns side by side and the gap is immediately visible:

SELECT CustomerID, FullName, OrderDate, ShipDate, 
DATEDIFF(DAY, OrderDate, ShipDate) AS days_order_to_ship
FROM dbo.CustomerOrders;

Every order shipped after it was placed -- nine days later, seven, three. The first report used OrderDate for when it was shipped. ShipDate was not even in the query. Looked reasonable, sure. But it was dead wrong. That is the failure to fear. Not the one that throws an error, but the one that runs clean and gives you bad data -- with the same calm voice that it uses for the correct answers.

The habit that catches both

Pinal's point is that confidence is a writing style, not evidence. On the server, that turns into something you can actually do. Before you trust SQL written by AI, ask the catalog two questions, in this order:

Question	How
Do these columns exist?	Check every name against sys.columns for the target table
Do they mean what the query assumes?	Check the data types and read the values before trusting the result

The first question catches the loud mistakes -- the invented Email that throws Msg 207. The second addresses the quiet ones -- the real OrderDate standing in for ShipDate, which SQL Server has no reason to reject. Both illustrate why successful execution is not proof of a correct answer.

When an AI assistant doesn't have access to your schema, it works from assumptions about what your database probably looks like. And even when it gets the column names right, that doesn't mean it understands what those columns represent.

Checking the schema is a good start, but it isn't enough. You need to verify that the query is returning the data you actually asked for, not simply that it executes without error.

A query that fails is often less dangerous than a query that succeeds.

Just because SQL Server ran it doesn't mean AI got it right.

More to Read

Pinal Dave -- Why AI Sounds Confident When It's Wrong
MSFT -- sys.columns (Transact-SQL)
MSFT -- INFORMATION_SCHEMA.COLUMNS (Transact-SQL)
MSFT -- OBJECT_ID (Transact-SQL)

Legacy SQL Server ?? Microsoft Says It Isn’t. Its Partner Slide Said Otherwise.

A Microsoft partner slide put SQL Server under the heading '1st party legacy'. Brent Ozar flagged it, and a day later Microsoft said SQL Server isn't legacy. Many users saw the slide and they are already asking what it means. Here's what actually happened, what Microsoft shipped, and why that "legacy" label deserves more scrutiny than Microsoft's explanation alone.

The Slide

FabCon and SQLCon Europe ran September 28 to October 1, 2026 in Barcelona. At a partner day during the event, a slide was photographed and shared on LinkedIn. Brent posted it on September 30 in I'm Just Gonna Leave This Here.

One column reads "1st party legacy". The line under it reads "Modernize every SQL Server". Microsoft is the first party. Brent zoomed in on that pair of columns and called it what it reads like: Microsoft telling its partners that SQL Server is a legacy database.

That's a pretty significant label to put on a product that Microsoft released a new major version of less than a year ago. In enterprise IT, "legacy" generally suggests aging technology that organizations should be planning to replace or retire. It doesn't mean a supported, actively developed database platform simply because it isn't running in Azure. There's a difference, and Microsoft should know better than to blur it in a presentation to its own partners.

Image: Brent Ozar, from a slide shared on LinkedIn.

The post drew more than 40 comments. Postgres came up early. So did government and regulated shops that are required to keep data on-prem. Brent asked the obvious question -- if SQL Server is legacy, and the next step is Azure SQL DB or Fabric, what exactly are customers running in AWS supposed to do?

Microsoft's Response

On October 1, Anna Hoffman of Microsoft replied in the comments. She said the wording on the partner slide was poor, and that the two lines described separate modernization motions, not a designation of SQL Server. Then she said it plainly:

"Microsoft does not consider SQL Server a legacy product."

Brent wasn't done. He pointed back to the slide's own wording and asked Microsoft what it should have said instead. That's where the thread stands.

Both can be true. The product team can genuinely consider SQL Server a modern, actively developed platform while Microsoft's partner messaging encourages customers to move toward Azure. There's nothing inherently wrong with that strategy, but calling SQL Server "legacy" is a very different message from encouraging customers to modernize their existing deployments.

A migration strategy is not a product lifecycle announcement. Let's look at what Microsoft actually shipped.

What Microsoft Shipped

The same week, Microsoft published its SQLCon Barcelona announcements. The first section is titled "Running data and AI on infrastructure you control", and it walks through TDE in 2008, Always Encrypted in 2016, and Ledger in 2022 as decades of SQL Server investment. That is not how you talk about a legacy product. The announcements included:

SQL Server on Azure Local - GA
Both connected and disconnected operations. Disconnected means a local control plane for environments where cloud connectivity is unavailable or restricted. Microsoft pitched it squarely at digital sovereignty. That is SQL Server running on infrastructure you control. Announcement

Agent Mode for GitHub Copilot in SSMS - GA
SQL Projects and Schema Compare in SSMS went GA with it. You don't keep building out the client tool for a product you're retiring.

Azure SQL Database Hyperscale
160 and 192 vCore Premium-series GA, serverless auto-pause in preview, DiskANN vector indexes GA, and a new Azure Arc migration path to Hyperscale in preview.

Database Hub in Microsoft Fabric - Public Preview
Inventory, health, performance and security signals across your database estate, with database agents for SQL and PostgreSQL.

Read that list honestly. There is one on-prem GA and one tooling GA. The rest is Azure and Fabric. The marketing energy is pointed at the cloud, but the engine is still getting investment.

The servicing cadence tells the same story. SQL Server 2025 has had nine cumulative updates in 2026, one a month since January. CU9 (KB5122048) shipped September 15, 2026 as build 17.0.5005.3. One week earlier, security updates shipped for both the GDR branch (KB5122770) and the CU branch (KB5122769). That's an active servicing commitment to Microsoft's current database engine, not evidence of a product being phased out.

The Support Calendar Doesn't Say Legacy

Slides change. Lifecycle commitments are published. These are straight from Microsoft Learn:

Version	Mainstream End	Extended End
SQL Server 2025	1/7/2031	1/7/2036
SQL Server 2022	1/12/2028	1/12/2033
SQL Server 2019	3/1/2025	1/9/2030
SQL Server 2017	10/12/2022	10/13/2027

Microsoft Learn lists each end date at 6:59:59 AM Pacific Time.

SQL Server 2025 is supported into 2036. That's a decade of committed patches for the version that shipped last November. Whatever the slide meant, that date is the one your contracts and your compliance people care about.

The calendar that actually matters right now is the other end. SQL Server 2016 reached end of support on July 14, 2026. SQL Server 2017 is next, in October 2027, and Brent posted a one-year countdown on October 12. If you're running either one, that is your legacy problem. Not the slide.

Know Where You Stand

Run this on each instance. It returns the version and current update level, so you can line it up against the table above.

SELECT
    @@SERVERNAME ServerName,
    CASE PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 4)
        WHEN '17' THEN 'SQL Server 2025'
        WHEN '16' THEN 'SQL Server 2022'
        WHEN '15' THEN 'SQL Server 2019'
        WHEN '14' THEN 'SQL Server 2017'
        WHEN '13' THEN 'SQL Server 2016'
        WHEN '12' THEN 'SQL Server 2014'
        WHEN '11' THEN 'SQL Server 2012'
        ELSE 'Older - check ProductVersion' END VersionName,
    SERVERPROPERTY('ProductVersion') ProductVersion,
    SERVERPROPERTY('ProductLevel') ProductLevel,
    SERVERPROPERTY('ProductUpdateLevel') UpdateLevel,
    SERVERPROPERTY('Edition') Edition;

Anything that comes back 2016 or older is already out of support. Anything that comes back 2017 has about a year. If it comes back 2025, compare ProductVersion against the build list. Mine says CU7. At the time of writing, the latest is CU9, 17.0.5005.3. Even a supported version can be behind. Start there.

If You're Not in Azure

A lot of SQL Server runs on-prem, in colos, and on AWS. None of those customers were in the room for that partner slide. All of them will hear about it.

Brent asked what AWS customers are supposed to do. AWS already answered. On July 21, 2026, Amazon RDS for SQL Server added SQL Server 2025 for Enterprise, Standard, and Developer editions. Existing RDS instances can upgrade by modifying the DB engine version. AWS supporting Microsoft's latest SQL Server release reinforces something that shouldn't need explaining: running SQL Server outside Azure doesn't make it legacy.

My advice to clients is simple. Don't make a platform decision off a partner slide or a blog comment. Make it off the lifecycle dates, your licensing, your RPO/RTO, and what your applications actually support. If that leads to Azure, fine. If it leads to SQL Server 2025 on the hardware you already own, Microsoft has committed to supporting that product through January 2036.

Microsoft is entitled to promote its cloud platforms, and customers are entitled to choose something else. What shouldn't get lost in that conversation is the distinction between modernizing a SQL Server environment and replacing SQL Server because someone decided to call it legacy.

SQL Server isn't legacy. SQL Server 2016 is.

More to Read

Brent Ozar: I'm Just Gonna Leave This Here
Microsoft: SQLCon Barcelona 2026 announcements
Microsoft: SQL Server on Azure Local is now generally available
Microsoft Learn: SQL Server 2025 build versions
Microsoft Learn: SQL Server 2025 lifecycle
Microsoft: SQL Server 2016 end of support is here
Brent Ozar: SQL Server 2017 Support Ends One Year From Today
AWS: Amazon RDS for SQL Server now supports SQL Server 2025