bit Obscene: SQL Server 2025 Availability Groups
Video Summary
In this video, I delve into the exciting world of SQL Server 2025 availability groups, discussing enhancements and improvements that address common pain points for DBAs and infrastructure teams. We explore key features such as improved monitoring tools, more discoverable settings, and better support for readable secondaries with query store data. Additionally, we touch on regular backup capabilities on secondary replicas, which significantly reduces recovery point objectives (RPO) issues. The discussion also covers the availability of various configuration options scattered across different management interfaces, highlighting areas where Microsoft could improve user experience through a more unified approach. Overall, this episode aims to provide insights into what SQL Server 2025 has in store for availability groups and how it can better serve the needs of the modern database environment.
Full Transcript
Yeah. Welcome back to another exciting episode of the Bit Obscene radio program. Since Sean has to go donate blood to his gator farm in Florida in 15 minutes, we are going to talk about 2025 availability groups so that Sean can showcase his expertise in that area and then go and do whatever he needs to do with his reptiles. So, Sean, I guess we’re picking on you first two in a row here. Since Joe has nothing to contribute, he’s just going to sit there and blink. We are going to, this is going to be the Sean show, you’re going to be elevate your temporary potential guest candidate co-host status to Olympian heights. So go ahead and take it away. And be a Greek Olympian? You can be whatever kind of Olympian you want. Right. Yeah, no, I mean, I think we’ve all had some interactions with AGs, even Joe. Right. Yeah, I mean, I had a bunch of latency problems, which was totally a SQL Server problem until it ended up not being one. Until someone, we won’t name names answered and found that it was the network. So what I want to know, Sean, is the SQL Server 2025 move data over the network faster. Have you personally made any improvements to get better network performance?
Sean O’ Yeah, sort of. I personally, so my main issue with what the crux of that with the latency and everything is I think the underlying messaging layer needs redone. Right. I think with some changes there, things will get better. Having said that, there were a couple things put in. So, you know, you all like fast, high throughput machines, right? Sean O’ Yeah. You like that. That’s good queries. That’s sweet, sweet, fast queries. Sean O’ Firehose and cursors. That’s not about the firehose.
Sean O’ So there’s a couple things in there. Have either of you heard of Group Commit? Sean O’ I’ve seen the wait type for it. I haven’t. I don’t think I’ve ever experienced it doing anything magnificent for me. Sean O’ Isn’t that like what we did when the three of us agreed to talk about this?
Sean O’ Yes. Yeah. Sean O’ But I think there are probably fewer razor blades involved. Sean O’ Okay. Sean O’ They’re missing out on the fun. Sean O’ Yeah.
Sean O’ So yeah. So group commit, I mean, essentially is when you go to commit, can we package up? Can we hold off writing that to disk? I mean, this isn’t an AG thing. It exists outside of it. But can we hold off writing that log block? Because commits and log blocks, they’re one of the things, right? You can hit the maximum size, which is 60k. And then that ends a log block where you get a commit and that ends a log block. Sean O’ So if we can put multiple commits in one log block, then we get more efficiency, less on disk space, blah, blah, blah, blah, blah, blah, right? You get one right to the IO instead of 15, whatever. So if we, if you have a commit that’s going to happen, you know, you do insert, insert into Joe, you know, salary, $5. And then you go ahead and commit that. And you do that. You have all these little tiny commits. Sean O’ I know, right? Zero dollars for Joe. So you have a bunch of those little small, your typical LTP style, you know, you can batch all those commits up and then write them as one, which means if you’re using something like an AG, then you’re only sending one log block across the wire versus 50.
Sean O’ So if you do have a latent, latent pipe like Joe has, sorry, I didn’t mean to throw out your medical issues on online. But if you have a latent pipe like Joe, then that can be very helpful. Sean O’ But then it’s not super great because you sit here and go, well, I don’t, you know, I have a fast, I’m using a hundred gigabit, you know, DAX with all this stuff and you’re clearly not running in the cloud. So you actually have sub millisecond latency. That might not be good. You might not want to hold up for five milliseconds, right? While you send it because it would have been faster for you to just send it.
Sean O’ You would have got it there and back faster than waiting for the group commit. So the group commit wait time was added in. So you can kind of remove that. That’s just a set option. I think that’s really good. One of the things that people I, when I’ve helped people and they complain and we talk about box cars and some other stuff, which you don’t really have to know about, but there’s a limit to the amount of outstanding log, right? Sean O’ Because you don’t want to say, you know, it’s not log shipping where you say, okay, great. Do a log back up and then ship the log over. And that’s whatever the outstanding is. You know, this is how much, how much do you want to send it without being acknowledged? Sean O’ Right. So that’s your outstanding. That’s your, your commit. And so that’s your number of box cars. So that’s a slightly configurable now, just there. And again, a, a, a SP configure option. And most people are going to think bigger number better as per usual, not always bigger number better.
Sean O’ It’s arguably slightly more, but you don’t, it’s like a parallelism and queries, right? If, if one thread’s good, eight threads, clearly better. Clearly won’t run slower. There’s no other issues that would ever it’s linear. Sean O’ Yeah. Why not? Sean O’ Eight times throughput.
Sean O’ Yeah. I mean, let’s just roll with that. Cause you know, Sean O’ Exactly. Sean O’ Every time I’ve been like, you know, forced to parallel plan, I’ve been really happy with the results. So yeah. Sean O’ I mean, I don’t even know why there are serial plans. Everything should just, even if one row is coming back, if you run it maxed up eight, it should just cut that row up into eight individual sections, run them individual faster and come through. Right.
Sean O’ It should, what it should do is run that query, run that across eight threads and whichever one finishes first wins. Sean O’ There’s something called batch mode aggregation, or at least I think that’s what it’s called. And you might end up with a count or a total. Sean O’ Oh, no.
Sean O’ And you might end up with a count. And parallelism does indeed make it faster. Sean O’ Sounds like someone who likes residual predicates, but we’ll move on. Sean O’ Joe likes all sorts of strange things.
Sean O’ This is 2008. You know, for whatever reason, the internet, like it can never advance past 2008. Sean O’ Like R2 SQL Server knowledge. You know what I’m talking about. Like it’s just impossible to advance past those. Sean O’ Yeah, but the other, some of the other things, have you, you guys aren’t, I know Joe doesn’t like AGs all that much, but contained AGs. So you got your logins, your jobs. In the AG, you got replicated master, replicated MSDB. So they come over.
Sean O’ I think those are great. Because, you know, a lot of what I see people struggle with, with AGs is keeping all that stuff synchronized in a meaningful way. Sean O’ So, you know, Sean O’ 90% of the reason they use DBA tools.
Sean O’ Yeah, exactly. Sean O’ Sync the logins and jobs. Sean O’ Exactly. You know, it’s like, and then like, you know, like, you know, of course, adding a step so that the job doesn’t run if the, you know, if it’s not on the writable or not on the primary or whatever. So, you know, like, you know, there’s just a lot of stuff that goes on with those. You’re like, I wish this was, I wish this didn’t suck. Sean O’ Yeah, so one of the big drawbacks for contained AGs was it couldn’t be in a distributed AG. Now it can be so too many kinds of AGs now.
Sean O’ There are. Yeah, we didn’t even talk about Linux. Sean O’ We’re not gonna. Sean O’ That’s the correct answer.
Sean O’ Just like we weren’t going to talk about Express Edition and the standard edition video, but someone, someone had to go say the word. Sean O’ Well, here, here’s one Joe like, yep. Sean O’ To me to express my name.
Sean O’ Well, you’ve been excited. Sean O’ How do you feel about query store on readable secondaries? Sean O’ I mean, I remember when it was announced and it was supposed to be it was in preview for a very long time. And then it was maybe generally available kind of but not supported still. And so, you know, I’m cautiously optimistic that they’ve worked out all the kinks and bugs with that. So that’ll be that’ll be nice. Because you know, the plan cash is terrible.
Sean O’ Never let that optimism fade. Sean O’ Yeah, never let them take that. Sean O’ I’m curious about that. So before we had that, like, like, what were the options for getting your query diagnostic data? Was it only plan cash?
Sean O’ Yeah, pretty much. Yeah, because query store data just shipped from the primary over. Sean O’ 2008 R2, man. That’s, that’s, that’s, that’s all you need. Sean O’ Yeah.
Sean O’ For tuning by plan cash. Sean O’ Yeah, just the plan cash and some logical reads and you’re just good to go. Sean O’ Yeah, and plus the fact that whether I don’t know if you know this, but those, you know, will come over to the primary. So you have that. Sean O’ It’s like merged replication.
Sean O’ I know, right? Sean O’ I still know some people who like that barf. Sean O’ But yeah, I think there’s a lot of great things for 2025. In terms of availability groups. I think it really hits a lot of the previous issues that you would have, especially, you know, between DBA, who’s typically in charge of it, right? Or infrastructure people, some Sean O’ sysadmin who’s like, All right, I got to set this up. I got to do this. No, we can’t do it. Can’t run into a lot of things. I still don’t think items are as discoverable as as they could be. Obviously, management studio doesn’t have support for a lot of things distributed AGs. And there, I shouldn’t say support. There’s no nice GUI wizard.
Sean O’ Yeah, no, nice. Sean O’ I think the biggest thing right now for people and the thing that I probably get the most questions on, even on stack exchanges. You know, how do we monitor this? How do we monitor this? Sean O’ How do I know there’s a problem? And where do I know there’s a problem?
Sean O’ Nothing in that has been changed. Sean O’ No. Well, that’s because Microsoft really does hate the GUI. Or rather, what I should say is Microsoft hates the end user and they express that hatred through various GUIs. The query store GUI being one of them, the AG dashboard being another. There’s a lot of stuff. Sean O’ You’re just like, like, I wouldn’t treat someone who I like and respect in this way. Like I wouldn’t do this to someone who I was a fan of like, if I was just like, hey, that Joe and Sean, they’re, they’re nice people.
Sean O’ Here’s how I’m going to let them look at their data. I like it just wouldn’t happen that would not go down that way. Sean O’ So I want to know what’s been I committed in a former life to be forced to use the SSMS extended events GUI. Sean O’ That’s another one.
Sean O’ It must have been a really bad one. It must have been a really horrible thing that I did. Sean O’ You used my secret. Sean O’ I would like to apologize to the SQL Server community for my karma being gripped upon all of you. Sean O’ The last two things I really want to highlight here for the 2025 improves on AGs when we were saying about, you know, things being given people heartburn, you can take regular backups on secondaries.
Sean O’ So you can take a full backup. Sean O’ Oh, not the regular anymore, right? Sean O’ Not the cop alone.
Sean O’ So it’ll change your, your diff map now will clear diff maps and your ML maps and stuff. Sean O’ That’s nice. Sean O’ You know, my, my, my gripe with the, like the set, but taking backups from the secondary was always like, Sean O’ They’re like, like there was nothing that would sort of warn you if the backup you were taking was far behind the primary.
Sean O’ Right. Sean O’ And so like, you know, like you’re sitting there, you’re taking log backups every minute of the secondary. Sean O’ But meanwhile, the secondary is like five, 10 minutes behind the primary and you’re like, well, like there, there goes my RPO.
Sean O’ Like, like, like, like, I wish there was something a little bit smarter with that. Sean O’ Like, you know, if someone’s like, you know, sets up all their backup jobs, so they go off a secondary because I don’t know, like, like, like, whatever reason people, people get really worked up. Sean O’ They’re like, Oh, the primary can’t handle the backups.
Sean O’ I’m like, what are you doing with your primary? Sean O’ But like, it would be nice if there were something sort of intelligent in there that would be like, wait a minute, like, like, this is pretty far behind that. Sean O’ Maybe we shouldn’t take backups here for a little bit.
Sean O’ Maybe we should focus on the backups here instead, because like, you know, you would you would you would have less chance of losing data in that scenario. Sean O’ But anyway, go on. Sean O’ Yeah, no, those I think those are the big things.
Sean O’ You know, you get some of the stuff with the readable, the query store. Sean O’ Secondary should get the backups. Sean O’ I mean, these are all top, top complained about items from everyone.
Sean O’ And I don’t mean that in a bad way. Sean O’ I mean, that in a good way like that. Sean O’ That’s a thing that I feel it should have been there from the beginning. Sean O’ Yeah, you took customer feedback and you implemented it.
Sean O’ And well, I didn’t. Sean O’ But yes, well, someone did. Sean O’ I mean, we all know you have source code access and you could really be a lot more helpful into these conversations. Sean O’ But, you know, we do it.
Sean O’ We do appreciate the contributions you do make. Sean O’ So thank you for that round of applause for Sean. Sean O’ I don’t have a round of applause sound effect. Sean O’ Oh, you know, we’re just going to insert.
Sean O’ A silent round of applause for Sean and all his. Sean O’ There’s the one in the zoom. Sean O’ Oh, there’s a single hand go. Sean O’ Yeah, no, I just think there’s a lot of really good stuff. Sean O’ I don’t like that.
Sean O’ A lot of these, you know, we’re talking about sequence. Sean O’ I know, I know I don’t like to know a lot of these things are in different places, right? Sean O’ We have not to segue into other stuff, but we have database scoped items.
Sean O’ We have SP configure or we have alter server. Sean O’ We have all kinds of stuff. Sean O’ Like, let’s pick one.
Sean O’ Yeah, right. Sean O’ Do you guys remember when sack was a thing? Sean O’ That’s the 2005. Sean O’ What is it? Sean O’ Well, it was, it was called wasn’t it called sack?
Sean O’ It was like the server administration. Sean O’ Oh, yeah, yeah, yeah, yeah. Sean O’ And you could export the XML or whatever and import it and do the, it was the way all the security things where you had to run a different.
Sean O’ Oh, God, that was mine. Sean O’ Yeah, well, thankfully, we moved on past that. Sean O’ Anyway, it is it is 430. Sean O’ And Sean has to go shave the virgins so that there’s gators don’t get heartburn again. Sean O’ So we’re going to let Sean go get to whatever he has to do and then some small chicken Joe Joe was me two episodes Sean O’ Because he’s been useless on this one.
Sean O’ So me and Joe. Sean O’ I think I show you one episode. Sean O’ How’s that two to one work? Sean O’ Because you were twice as useless on this one as you are usually. Sean O’ Wow.
Sean O’ Yeah, you got you have you have some labor some tax stop. Sean O’ You know, I’m making the same face right now that you made when when I have Googled best consultants and I didn’t include your name. Sean O’ Oh, yeah.
Sean O’ I think I think I think you just intentionally left my name out of that. Sean O’ I think you just made a list of names that you knew would make me make that face. Sean O’ And and just and just read that only I don’t even think you’ve looked it up. Sean O’ I think you just pretended to type.
Sean O’ You came up with a list of people who, you know, are worse consultants. Sean O’ I could share my screen right now and we could do a live demo. Sean O’ No, that’s okay, because you would you would have you would have pre cooked this. Sean O’ You would have this is something that you wouldn’t.
Sean O’ Yeah, this is something that you would have pre cooked just to just to just to make get me to make the Sean O’ That face again. Sean O’ So anyway, Sean’s gonna go do his thing. Sean, thank you for joining us for I think three episodes of the bit obscene radio program.
Sean O’ And we do we do anticipate having you back for future episodes to prove your worthiness as a co host. Sean O’ So. Sean O’ So that would be great that we will see you in the future.
Sean O’ Otherwise, it’s time for Joe’s punishment. Sean O’ So goodbye. Sean O’ We’re justwolf Jesus.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.