Pushing the Limits of AGs

Posted on September 17, 2025 by way0utwest

Many of you reading this likely have an Availability Group (AG) set up on at least one database in your organization. Maybe not most, but many of you as this has proven to be a technology that many people like for HA/DR, upgrades, and probably other uses. As the technology has evolved from it’s SQL Server 2012 debut, it has improved in many ways. This might be one of the few features that has received regular attention from the developers in Redmond across multiple versions.

That’s not to imply this is a foolproof or bug-free feature. Numerous people have had issues with the various types of AGs. From setup to performance to scale, I’ve seen many people post questions and search for answers on how to get their system running smoothly and reduce any late-night calls.

Over the last decade I’ve seen various people test different parts of the AG technology, but not many pieces about how much you can stress the technology at high levels. Microsoft supports up to 8 replicas, but what about groups and databases? The recommendation page says MS has tested 10 AGs and 100 databases, but nothing else.

I ran across a post on LinkedIn from Calin Oprea that covers his AG testing. He hasn’t written about it, but says he can make the scripts available. He tested 50,000 databases, maybe more. He says 50k+ in the post and notes anything beyond 500 databases per instances starts to fall apart and 1000 seems to be a hard limit. Failover doesn’t work, even without a workload.

That’s quite a test of the technology at it’s extreme. I’ve never run more than a few AGs or databases, and I see people posting and talking about dozens. Most of the people I know doing things at scale are using less than 10 AGs and usually no more than 100 databases max.

I wonder how many of you out there use more than 2 AGs on any instance and more than 20 databases. I’m sure there are lots of systems at this scale or larger, but I’d guess the majority are 1 AG and less than 10 databases.

Take a look around your environment today and see what the average and extremes are for Availability Groups. And if you’ve never looked at them, it’s a piece of technology you ought to become familiar with. HA/DR is becoming a base requirement in many situations and it’s available in the cloud with the toggle of a setting. If you work on premises, it’s likely your clients expect your systems to easily failover to another location. Check out Stairway to Always On to get started.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged , | 1 Comment

A Prompt AI Experiment

Posted on September 17, 2025 by way0utwest

Prompt AI released recently and I decided to try a few things with the tool that might help me in database work. I’ve had to do this task, but I had a customer recently ask me about this as well. They were wondering where they were short FKs.

This post showed what I tried with Prompt AI.

This is part of a series of experiments with AI systems. This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.

 

Help Me Query the Database

In a recent post, I looked at the MSSQL agent in VS Code to query my database. I decided to try a few things with Prompt AI, working inline. First, I connected to the database and then I asked a question of the AI: who hit the most home runs.

2025-09_0342

From the time I shot the video, I had updated Prompt, and possibly the AI got smarter, because this was the result: I had a qood query.

2025-09_0343

I wanted more, so I asked for seasons. You can see the prompt at the bottom, but seasons were added.

2025-09_0344

Click Accept and then run it and I get the correct answer.

2025-09_0345

How nice would this be for some of the BI analysts to use this to get work done?

Getting Help Fixing the Database

In this db, there aren’t any FKs. I decided to (poorly) ask about this.

2025-09_0347

Note the misspelling. One of the amazing things is the AI recognizes little errors, just as a human would, and corrects them. I get an OK result, but not great.

2025-09_0349

I rewrite my prompt to ask which ones “should” have FKs. I get this:

2025-09_0350

Helpful, but I don’t want to work through this list.

2025-09_0351

I get this. No code, and I feel like I’m talking to a junior dev who is either trying to avoid work or doesn’t have the understanding that I want work done. I’ll try again.

2025-09_0354

I get something I can use. I wouldn’t likely create all these at once, I’d test things, I might start to add these as migration scripts I can deploy over time, after I’ve verified this isn’t going to break an app or cause issues. Or there aren’t already data issues.

2025-09_0355

Still, this is one  of those nice time savings for me writing a script to try and create these or working through time one by one. Now I can just set up tests.

Or ask Prompt AI to help Winking smile

Summary

The new features in Prompt AI let you keep coding inline, but add some AI help to what you do. I’ve seen some neat things, and some frustrating ones, but overall, it’s helpful to produce code.

If you haven’t tried SQL Prompt, download the eval and give it a try. I think you’ll find this is one of the best tools to increase your productivity writing SQL.

Video Walkthrough

This isn’t exactly what is above, because LLMs aren’t deterministic.

 

Posted in Blog | Tagged , , , , | Comments Off on A Prompt AI Experiment

Join Other Database Pros Coming to the Netherlands

Posted on September 16, 2025 by way0utwest

In a few weeks I’ll be heading to Amsterdam for a few events. I’ll be working for a day before a short train ride down to Utrecht, a place I’ve never been and am looking forward to visiting.

It’s a packed week with PASS On Tour, the Redgate Summit, and Data Saturday Holland. If you live in the EU, this is an easy way to learn a lot about different aspects of databases, see what others are working on or concerned about, and network with fellow professionals.

Plus it’s in the Netherlands. I’ve never been to Utrecht, but my wife and I love Amsterdam. In fact, she’s meeting me there and we’ll likely spend a few days before taking a holiday.

The PASS On Tour and Redgate Summits in New York were great. Lots of interesting talks, good conversations and questions, and some fantastic networking. Looking forward to the same in the Netherlands.

Register today and join me for a short week of database discussions in October.

Posted in Blog | Tagged , , , | 2 Comments

AI Steals Joy

Posted on September 15, 2025 by way0utwest

Take a moment and think about how much work you do each day. If you write code, how much time do you actually spend writing code around meetings, admin work, and other tasks. If you manage systems and develop scripts, how often do you get to work on a script, change it, upgrade it, or add a new feature? I bet it’s a minority of time for most of you. Studies show that many developers spend only an hour or two writing code each day. I would guess it’s lower for sysadmins or DBAs who incorporate coding into their jobs.

Now think about how much you enjoy each thing you do. I assume meetings aren’t the most exciting thing you work on, nor is updating a document for others to read. When I wrote code as the main part of my job, solving the problem, trying different algorithms, experimenting with enhancements, those were the fun times.

Now imagine having a competent AI assistant that works with you. What if the AI does the coding and leaves you with the meetings and writing of specifications? It could happen. While it might be fun to direct a team of agents, you still need to understand software architecture and development, but you might not want to get away from writing code.

As more developers start to use AI agents to get work done, there is rising dissatisfaction among developers because the AI can do quite a bit of the coding, which is the thing that most developers love. The AI doesn’t help with any of the other busy work, though AI agents might do a better job of ensuring docs match committed code (or released code).

What I’d really like is an AI that would take my place at meetings. Maybe do a report for me or take a summary back, get some action items for me. An AI agent could handle things that wouldn’t necessarily be fun for me. Not all tedious things, but it could free up more time for coding.

However, that’s not what the GenAI agents are being positioned to do. They’re stealing the joyous part of development. At least, if managers have their way, that’s the place they’ll use GenAI most.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | Comments Off on AI Steals Joy