Who is Using CAGs?

Posted on May 4, 2026 by way0utwest

While talking to a customer a few weeks ago, they mentioned that they used Contained Availability Groups (CAG) everywhere. They also said they were amazing and wondered why everyone wasn’t using them in other environments. Of course, I questioned the “everywhere”, which turned out to be more of a default for new systems than a standard across all systems. That’s likely true of most things since it’s rare we get to update/patch/set something across an environment of any size and ensure every system is the same.

Still, setting a CAG as a default makes some sense for enterprises. This ensures that in an HA situation I have my logins, jobs, etc. already on a secondary node. That’s been one of the challenges of using lightly linked systems that only sync up database level information. Log shipping, Replication, Availability Groups can all work to keep a secondary ready to take over, but they all miss information that is stored in master or msdb.

That’s the stuff we have to sync manually. It can be done, but it’s work. We’ve had numerous articles at SQL Server Central on syncing logins and other objects outside of your database.

Today I wonder how many of you are using CAGs in your environment? As the default for new systems? Moving all ones to this setup?

Or do you even know about them? They are relatively new, since SQL Server 2022, and I have to admit I’ve heard relatively little about them in the community or from customers. Many people use Availability Groups, but not many seem to use Contained Availability Groups.

Maybe another question is would you want to use them? There are a few things you have to consider and they can be slightly tricky, but they do some reduce some of the work when you have failovers. Of course, like any other technology, you need to test that your failovers work and you understand  the ins and outs of how they work, just in case that switch isn’t as smooth as you expect.

It should be, but sometimes things break. If they do, you want to ensure you, or someone on your staff, knows how to fix them.

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 , , | Leave a comment

A Redgate Data Modeler Gotcha with Relationships

Posted on May 4, 2026 by way0utwest

This happened to me recently after being busy with non-data modeling tasks for a few weeks. I went to add a relationship and was confused about the behavior. Read on to see what happened and what I did.

This is part of a series on Redgate Data Modeler.

Adding a Relationship

I opened one of my models and saw something like this. Note that the Article and ArticleTag entities aren’t related.

2026-04_0220

I decided to add a relationship. This is a Many to Many, so I clicked on that icon in the toolbar.

2026-04_0221

I then clicked on the Article entity and saw this. It’s a self-referencing relationship.

2026-04_0222

What??? I assumed I’d click one entity, then the other. I tried grabbing various elements of the relationship to move them, but nothing worked. I could move them around within the relationship, but not to another entity.

2026-04_0223

I deleted and added this a few times before I decided to check the docs. On this page for relationships, I found my mistake. There is this quote: “If you’ve already selected the tool, simply drag from one entity to another.”

Aha.

If I click the toolbar relationship icon, and then drag from one to the other, it works.

2026-04_0224

I suspect this is an artifact of the web controls, but it was weird for me to not be able to alter the relationship targets. Even in the right properties pane, I can’t change this.

2026-04_0225

Summary

A simple thing, but not quite as intuitive as I’d like. However, one needs to learn to use tools, which means reading docs or using Claude/ChatGPT andfingers crossed

This is a reminder to drag relationships in your Redgate Data Modeler models and don’t get away from modeling for weeks, like I did.

Give Redgate Data Modeler a try and see if it helps you and your team get a handle on your database.

Video Walkthrough

Here’s a short video of my working with Redgate Data Modeler and changing cardinality.

Posted in Blog | Tagged , , | Leave a comment

A Tool is Better than a Script

Posted on May 1, 2026 by way0utwest

While working with a customer recently, I heard this sentence: a tool is better than a script. The reference was that this customer preferred a known, tested, approved tool for most of their staff rather than a script built, lightly tested, and perhaps changeable by anyone in their organization.

I was surprised, because in many ways, I’ve depended way more on scripts, more often, than “tools” in my career. Often I struggled to find tools that actually worked in the way I wanted them to and built them myself with Unix shell utilities, VB Script, PowerShell, or some combination of those or other technologies.

I asked them if they wouldn’t prefer to customize things and let one of their senior people write their tools. They said that it wasn’t necessarily a problem for a few people, but too many people might edit and change the script, even the senior people, and then others couldn’t depend on them. They weren’t sure if they were reliable if a known script wasn’t being run. Even among their senior people, someone would edit a script then they thought something wasn’t quite right, or there was a new requirement and then the script would return unpredictable results.

I felt they didn’t completely trust their staff, but I also understand. I’ve changed my own “tools” and broken them or gotten back incorrect results. Depending on what the script did, and how busy I was, I might not even notice the problems. That might cause me problems if I expect data or actions to occur one way and they work differently.

Scripts can be changed by anyone.  The results might not be what’s expected on a team. I get that. At some point in my career, I put all our DBA “scripts” or “tools” into a version control system and we had a path to the trunk (main) branch of them that was read-only, so that we knew what was being executed. If we wanted to change the script, we had to open an branch in the repo, make our edits, and then have another DBA approve the change before it would be merged into the main, and subsequently pulled into the folder in our path. This didn’t prevent changes, but it did give us some versions and history of changes.

There’s a balance between flexibility and customization that is challenging to tread amongst team members. Sometimes a known, reliable execution is better than one that changes too quickly. We’ve been asked at Redgate Software to ensure people using Redgate Monitor can audit what actions are taken in the tool. Sometimes a team member changes a setting, such as an alert threshold, and other team members aren’t aware. That can impact their ability to diagnose problems when they assume the tool works one way, but it’s actually working another way.

Tools we decide to use, whether purchased or OSS, won’t always do what we need. There is a requirement to build our own, but we also need some expectation and assumption of how they work. Especially in a team. I don’t want to have to re-read the code every time I run a tool; I should know how it works.

At the same time, I can’t have every tool change each time someone wants something new. I love sp_whoisactive, but I depend on it working a certain way and wouldn’t want Grant to change how it reports data unless I know about the change.

Working in a team requires teamwork, which means that whatever type of tools we buy/download/build, we need to ensure we all are aware of how they work.

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 | Leave a comment

Advice I Like: Celebrate Success

Posted on May 1, 2026 by way0utwest

“On the way to a grand goal, celebrate the smallest victories as if each one were the final goal. That way, no matter where it ends, you are victorious.” – from Excellent Advice for Living

I believe in celebrating small things. I am a big “smell the roses” and “celebrate your success” even if the bigger part of the success feels like a failure. If I deliver a good demo in a talk, or one of my players makes a good hit/pass/etc., let’s celebrate that. If the rest of the talk wasn’t smooth and people are confused, or we kept hitting the ball in the net, that’s a bit of a failure and something to work on.

There’s always a bright side and a dim side. One is likely larger, but both are there.

I think if we look at something in black and white terms, and reduce it to success or failure, then you create a psychology that you win or something wasn’t worth doing. The sports teams, the musicians, the friends I have, my kids, we aren’t defined by whether we win every time. We can’t be the best all the time.

Even the best of the best isn’t the best most of the time. Tom Brady (7 rings) and Michael Jordan (6 rings) might be considered the best at what they did. However, they played more years (Brady 23, Jordan 15). Were they failures those other years?

The Beatles had 19 #1 albums, the most of all time. However, they released more. They had lots of #1 songs, but plenty that didn’t get to #1. Were those not worth doing?

Celebrate your success. You might write a great query, but ultimately there are plenty others that don’t perform well or some might even return the wrong results. Hopefully you’ll fix and improve those, but celebrate the things that go well.

Work on those that don’t and turn them into victories later.

 

I’ve been posting New Words on Fridays from a book I was reading, however, a friend thought they were a little depressing. They should be as they are obscure sorrows. I like them because they make me think.

To counter-balance those, I’m adding in thoughts on advice, mostly from Kevin Kelley’s book. You can read all these posts under the advice tag.

Posted in Blog | Tagged , | Leave a comment