A Fine Slice Of SQL Server
Aleksey Vitsko creates an availability group:
SQL Server Availability Groups are great for DR and HA purposes. In this article, we look at how to setup a SQL Server 2025 Availability Group using the features in SQL Server Management Studio.
Read on for the demo.
Leave a CommentJordan Boich administers a database in a distributed availability group:
I’ve seen a lot of great blog posts and content out there talking about Distributed Availability Groups (DAGs). However, I haven’t come across much of anything covering the actual administration of them. Especially for those choosing to use a DAG as their Disaster Recovery (DR) strategy. If you’re choosing to use a DAG for your DR, then you need to know how to perform basic administration. The goal of this post is to cover the gaps in knowledge out there, and cover the nuances of DAG administration as the methods and steps reveal nuances that normal Availability Groups do not necessarily present.
This post is going to cover how to remove and add a database from a Distributed Availability Group. Removing a database from an AG and having to add it back is a common maintenance or troubleshooting step that many DBAs face. But when it comes to DAGs there are some extra nuances and steps you have to keep in mind depending on if you’re working on the global primary AG or the forwarder.
Click through for the steps, as well as some of the pain points you might run into along the way.
Leave a CommentSean Gallardy troubleshoots an error:
This used to be a very hot topic around SQL Server 2016 when automatic seeding first came out, then everyone learned how automatic seeding was just manual seeding but done for you and the topic went away. A decade later, it’s somehow back to being a hot topic. I guess all that is old is new again.
Error 1408 states that the database doesn’t have enough log to find a starting point with the primary/principal – which, let’s just use AG terminology from this point on because mirroring has been dead for 14 years and counting.
Mirroring has been dead and yet it lives on in undead form. Regardless of that, Sean provides some good information around what automatic seeding actually means and what you can do if you get stuck with Error 1408.
Comments closedDenny Cherry announces an extension:
Microsoft introduced Read-Only Routing to Always On Availability Groups many versions of SQL Server ago. However, Microsoft never added any sort of UI to SQL Server Management Studio. Years ago, DCAC released a Windows application to manage your Read-Only Routing configuration. We’ve converted our Windows application into a VS Code extension called SQL Always On Read-Only Routing Configuration, which you can download from the website or via VS Code.
Click through to learn a bit more about the extension. You can also view it in the VS Code Marketplace.
Comments closedJohn Morehouse keeps one eye on things:
Contained availability groups solve some real operational problems, especially around logins, jobs, permissions, and supporting metadata. They can reduce drift between replicas and make failover cleaner.
That does not mean they are magic.
Like most SQL Server features, contained availability groups come with details that matter. The feature can absolutely help, but it needs to be designed, tested, and operated with the right expectations.
Click through for several things you should consider before jumping into deploying contained AGs.
Comments closedJohn Morehouse moves some assets between availability group replicas:
In the first post, I introduced contained availability groups and how they bring contained versions of
masterandmsdbalong with the Availability Group. That matters because many applications depend on more than just user databases.Two of the biggest wins are SQL Agent jobs and security objects.
Click through to see how they work.
Comments closedJohn Morehouse takes us through contained availability groups:
Availability Groups have been one of the best high availability and disaster recovery options in SQL Server for a long time. They let us move a group of user databases together, keep replicas synchronized, offload some read-only workloads, and give applications a listener instead of a single server name.
That works great until the application needs more than just the user databases.
What about the logins? SQL Agent jobs? Permissions? Objects that live in
masterormsdb? In a traditional Availability Group, those objects are still tied to each SQL Server instance. That means DBAs have to keep them synchronized manually across replicas. Miss one login, job, credential, or permission and the failover might technically succeed while the application still has a very bad day.
Read on to see how contained availability groups can help resolve these problems.
Comments closedAnthony Nocentino performs additional testing:
In my planned failover walkthrough, I showed what happens when you deliberately move the primary role to another replica. That’s the easy case. Now I want to show what happens when the primary pod just disappears unexpectedly, like during a node failure or a container crash. No graceful shutdown, no demotion, just gone.
I ran two test scenarios, each cycling the primary role across all three pods by force-deleting the current primary three times in a row. First, a 5GB TPC-C database idle. Then, that same 5GB database under sustained HammerDB TPC-C load. Six force-deletes total, six successful automatic failovers. I’ll walk through the error log from the promoted replica, the operator’s detection and recovery behavior, and the full timing data.
Read on to see how Anthony’s SQL Server Kubernetes operator handles when things go bump in the night.
Comments closedAnthony Nocentino runs a test:
When building the sql-on-k8s-operator, I wanted to make sure it could handle both planned and unplanned failovers. The easy case is a planned failover, where you deliberately move the primary role to another replica. The harder case is an unplanned failover, where the primary pod just disappears. The operator needs to handle both.
I recently ran a full planned failover rotation on a three-replica SQL Server Availability Group managed by sql-on-k8s-operator, and I want to show you exactly what happens inside SQL Server and the operator during each hop. If you’ve been following my Introducing the SQL Server on Kubernetes Operator post, this is the logical next step: what does the error log actually look like during a planned failover, what does the operator do in response, and how long does the whole thing take?
I ran the same three-hop rotation twice: once with an idle 5GB database to establish a baseline, and once under a sustained TPC-C workload with HammerDB. In this post, I’ll walk through the SQL Server error log entries, the operator’s reconcile behavior, and the timing data for both runs. In the next blog post, I’ll show what happens during an unplanned failover. Let’s go.
Click through to see how it all works.
Comments closedJohn Deardurff lays out some changes in SQL Server 2025:
A client requested a presentation discussing key improvements to Always On Availability Group fast failover in SQL Server 2025. I decided that a summary would be appropriate for a blog post. So, here I discuss Enhanced Telemetry, Persistent Health, and Intelligent Fast Failover
John has a very positive take on fast failover. I haven’t tried any of this functionality, but some of this does sound promising.
Comments closed