Voice of the DBA

Tag: availability groups

  • Who is Using CAGs?

    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.

  • Pushing the Limits of AGs

    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.