Availability Groups – Curated SQL

Migrating from a Contained Availability Group

Published 2026-02-09 by Kevin Feasel

A client was running a Contained Availability Group in SQL Server 2022, but wasn’t using the AG Listener for their application connections. This negated most of the benefits the Contained AG was designed to provide. They also had some security misunderstandings and missteps, as this was built for them without any real knowledge transfer – one of the reasons they reached out to us for help. After review, it became clear that there was no need for a contained AG here, so we helped them migrate to a Basic Availability Group (SQL Server Availability Group in SQL Server Standard Edition) while preserving their database configurations and minimizing downtime.

Read on for a step-by-step process and a few hints on configuration.

Thoughts on Creating Databases in Contained Availability Groups

Published 2026-02-09 by Kevin Feasel

Andreas Wolter digs into some updated functionality:

First of all: It’s always encouraging to see the Product team act on user feedback. SQL Server 2025 CU1 introduces an improvement that allows the creation and restoration of databases within contained availability groups (CAG). This is a step in the right direction, but as you’ll see, there are still some bumps to smooth out. Keep the feedback coming (here: Allow creation and restore of databases in contained availability group) — progress is happening, but we’re not quite there yet.

Read on for a litany of issues, as well as Andreas’s recommended solutions.

SIDs and Distributed Availability Groups

Published 2026-02-02 by Kevin Feasel

Evan Corbett troubleshoots an issue:

After building a contained availability group in SQL Server, a customer was experiencing intermittent issues connecting to their primary database. Our investigation revealed that the SQL Authentication login being used had been created both within the context of the contained AG as well as directly on the primary node but had different SIDs in each location.

This is a pretty common issue when using SQL authentication, and it always seems to bite at the least opportune times.

Checking SQL Server Availability Groups

Published 2026-01-22 by Kevin Feasel

Jeff Iannucci announces a new procedure:

SQL Server Availability Groups can be a great feature to help support your High Availability needs, but what happens when they fail to work as expected?

Do you have an expiring certificate on used by an endpoint? Do you have timeout settings that could contribute to unexpected failovers? Are you suffering from a high number of HADR_SYNC_COMMIT waits?

We’ve seen all those things happen, and like Marvin Gaye we’ve wondered: what’s going on? And we’ve wanted a tool to help us see if other clients were having these problems, and more.

Read on for more information and check it out yourself.

Comments closed

Troubleshooting a Distributed Availability Group Failure

Published 2025-12-09 by Kevin Feasel

Jordan Boich digs in:

To give some background on this topology, they have a DAG comprised of two individual AGs. Their Global Primary AG (we’ll call this AG1) has three replicas, while their Forwarder AG (we’ll call this AG2) has two replicas. The replicas in AG1 are all in the same subnet and all Azure VMs. The replicas in AG2 are all in their own same subnet and all Azure VMs.

By the time we got logged in and connected, the Global Primary Replica was online and was able to receive connections. The secondary replicas in the Global Primary AG however, were unable to communicate with the Global Primary Replica. This is problem 1. The other secondary problem is that several databases on the Forwarder Primary Replica were in crash recovery. This is problem 2. Considering problem 2 was happening in the DR environment, that was put aside for the time being.

Read on for the troubleshooting process and solution.

Comments closed

Log Truncation in Distributed Availability Groups

Published 2025-12-01 by Kevin Feasel

Paul Randal phones a friend:

This is a question that came in through email from a prior student, which I’ll summarize as: with a distributed availability group, what are the semantics of log truncation?

As I’m not an AG expert (I just know enough to be dangerous ha-ha), I asked Jonathan to jump in, as he is most definitely an AG expert! I’ll use AG for availability group and DAG for distributed availability group in the explanation below. Whether the AGs in question have synchronous or asynchronous replicas is irrelevant for this discussion.

Read on for the answer.

Comments closed

An Error with Log Shipping in an Contained Availability Group

Published 2025-11-04 by Kevin Feasel

Rich Benner diagnoses an issue:

We’ve recently come up across a fun little bug with log shipping in contained availability groups. A cursory Google search did not bring up any useful posts for troubleshooting, so here we are. If you’re here for the bug, feel free to scroll to the bottom of this post. For the rest of you, however, let’s add a little context to this scenario before getting to the meat of the situation.

Click through for the issue, as well as how Rich was able to resolve the problem.

Comments closed

Trace Flag 1448 and Replication

Published 2025-10-23 by Kevin Feasel

Garry Bargsley covers the behavior of a trace flag:

In SQL Server environments where transactional replication runs alongside Always On Availability Groups (AGs), DBAs sometimes face a frustrating scenario: replication stalls when a secondary replica or subscriber is offline for maintenance, patching, or unexpected downtime.

By default, SQL Server’s Log Reader Agent is cautious. It only marks transactions as ready for replication once they are hardened on both the primary and all replicas. This ensures consistency across the AG, but it can also cause replication to stall if an asynchronous replica or subscriber is unavailable for an extended period of time.

Click through to see how it works and what the consequences are.

Comments closed

Diagnosing a Partition Job Failure after Migration to an AG

Published 2025-10-13 by Kevin Feasel

Mike Lynn describes a customer issue:

Quick Summary

A client noticed one of their reporting tables wasn’t logging any new information after the first of the new month.

Context

This environment ran on SQL Server 2019 in an Always On Availability Group configuration hosted on AWS EC2 servers. This is roughly 30-45 days after the servers were migrated from a SQL Server Failover Cluster Instance in AWS on EC2 to the new AG setup.

Read on for the problem, the discovery process, and the solution. I like reading this sort of report specifically to focus on the process. One of the best skills you can develop in any technical field is the practice of methodical behavior: review and understand the error message (perhaps with the assistance of a search engine or tool of choice), then work logically through possible issues until you discover the cause. It sounds obvious when I describe it that way, but far too often, people flail about and try a variety of arbitrary things because they don’t really understand the issue and hope that doing this one thing will fix whatever problem is happening.

Comments closed

Extending Index Information across Availability Group Replicas

Published 2025-10-08 by Kevin Feasel

Aaron Bertrand extends a prior article:

In my previous tip, Managing Unused Indexes in SQL Server Availability Groups – Part 1, I showed how I use dynamic SQL to gather index usage statistics for a given table from all replicas in an availability group. Knowing the usage from all workloads is definitely better than focusing on only the primary or a single secondary. But what if I want to make more informed decisions, incorporating row counts, size, and index columns into this output?

Read on to see how you can incorporate this additional information.

Comments closed

Category: Availability Groups

Quick Summary

Context