Voice of the DBA

Tag: high availability

  • 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.

  • Do You Really Need HA?

    I ran across a thought-provoking post from Chrissy LeMaire asking if we should reconsider SQL Server HA. The post actually asks if you’ve considered not using it. The default from Chrissy, for most installations, is to use standalone SQL Servers. This isn’t to say she’s against HA solutions (FCIs or AGs), but that they often cause problems and might not be needed.

    It’s an interesting position to consider. For a long time, I avoided SQL Server clusters as they were hard to setup with a lot of complexity, hardware requirements, etc., and didn’t really provide enough benefits over using log shipping with a second server for me.  These days I have clients with mostly AGs, and they seem to run fine. That being said, Chrissy notes that after she left a job, a network outages caused a bunch of downtime. I could see there being downtime, as the old database mirroring (and the it-will-never-die replication) needed a working network. If you have network issues, you better know how to manage your HA technology’s issues.

    I also Brent reposted this on LinkedIn, with some fun comments. There was one great one, which said, “Can confirm. FCI is literally my friend’s largest downtime cause.” That one got me to stop and think a bit. If I were having stability issues with any of these technologies, I’d certainly look to replace them. I value my time off and my sleep.

    I know people who default to using AGs with most servers, mostly to avoid someone calling when a system is down, but to be fair, I also think these are some of the more talented data people I know, so perhaps they can handle minor issues and prevent them from turning into major ones. However, Chrissy brings up a great point. SQL Server HA (or Oracle, PostgreSQL, Linux, etc.) isn’t simple. If you have staff turning over, are they qualified to keep things running?

    Or do things just happen to run without these people until something breaks?

    There is also the RTO issue. If you have a high RTO, like a day or two, is HA worth any amount of effort? Isn’t it better to rebuild things and restore? Especially in the cloud, where I might be able to redeploy a new VM/db/etc. and put data in it? Note, I’d want to be sure that I can get to my backups. The SLA on getting older files might be slow, and if it is, I’d want separate backups.

    I do think that small to medium companies ought to rely more on backups and tools, like dbatools, to provide the ability to recreate a system. Adding in the complexity of HA certainly shouldn’t be the default, especially if you aren’t sure the staff will be around for the long term. The caveat with that might be if you use a company like ProcureSQL, StraightPath Solutions, Dallas DBAs or someone else, maybe you don’t worry about staff turnover.

    Steve Jones

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

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

  • All the Costs of Downtime

    I studied economics in university, which isn’t that close to database work, though I did have to work through linear regression problems by hand. I always enjoyed mathematics, so this wasn’t a hardship. Until I purchased a PC that was capable of letting me do graphs and calculations in PASCAL and BASIC. Then I realized that my enjoyment wasn’t that efficient or useful, and a computer could help me get things done way more efficiently.

    Many of us work on systems that process tremendous amounts of data, something our organizations couldn’t complete without computer hardware, efficiently or not. We just wouldn’t be able to get the work done by hand. That’s the main reason why downtime is such a problem in the modern world; we can’t fall back to manual systems in many cases.

    I ran across an article that discusses some of the large-scale failures in recent history (Heathrow, Delta, NYSE, Royal Bank of Scotland) due to computer system failure. Certainly, there are large financial costs and lost revenue for organizations that suffer these outages. However, there are other costs that are borne by the staffers, which don’t often make the news.

    When it’s “all hands on deck” to solve a problem, other work isn’t being progressed. There is certainly the interruption of Operations people, but often developers get asked questions or pulled into meetings to provide input. That can take them away from their existing work. Apart from the “23 minutes to get their head back in the game,” as noted in the article, can they even focus anymore? Will they be thinking through all the possible causes, and did they actually provide the right information or all the details needed?

    During a crisis, or even after, it is very hard for humans to focus on anything else. Apart from the technical details, IT staffers can have a range of emotions and thoughts. They might have sympathy for customers affected. They might worry they’re at fault and might be blamed (or terminated). They might be thinking about how they should have coded or configured something differently? Should they have tested more or accounted for issues? They might have simple anger at others who didn’t do their job, or frustration at the failure of a piece of hardware.

    Perhaps even more concerning is the load management can place on employees to get things fixed. If people work long hours, how do we ease them back into the flow of all the other daily work? I know I’ve struggled to get people to rotate work with rest as a manager. As an employee, I struggle to even sleep if I am sent home while others are still working. I’ve had to work 100+hour weeks and very quickly we get into survival mode, not productive mode.

    There are lots of costs to downtime apart from the financial impact. If you can’t maintain a stable environment that limits the time employees spend firefighting, you likely aren’t going to survive as an organization. Startups sometimes can do this, but often it’s from a few extremely dedicated employees who make a difference at a smaller scale. And these employees often pay the price in their personal lives with health, relationship, or other issues.

    The article goes on to look at predictive analytics that might help us reduce some of the issues from hardware issues. I think this is likely true, as we’ve seen digital twins that simulate loads on equipment help proactively catch issues.

    What do we do with software? If we don’t write well architected software that handles the load, how do we write an analytical system that can predict failures? This seems like a level of static and dynamic code analysis that we aren’t mature enough to build.

    Heck, even if we could, how hard is for many of you to get queries tuned in a running system? I find too often there isn’t enough effort or enthusiasm from developers, management and others to follow solid tuning advice and change your SQL. Maybe that’s too limited a view.

    Perhaps the AI analysts of the future will become the consultants of the past, whose recommendations often mimic the words of the current staff, but somehow carry more weight. Maybe they’ll get more things done and changed to help us build more robust systems.

    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.