Press "Enter" to skip to content

Category: Availability Groups

Reseeding a Single Database in an Availability Group

Published 2026-11-09 by Kevin Feasel

John Morehouse solves a problem:

Recently, I found myself dealing with an Availability Group (AG) where I needed to remove a single database from one secondary replica and then add it back. Nothing particularly unusual about that, right? Except I wanted to use automatic seeding to rebuild that database without affecting the other databases already synchronized on the replica. That led me down an interesting rabbit hole involving how SQL Server handles automatic seeding, what happens when you remove a secondary database from an AG, and how you can rebuild just one database without disrupting the others.

Let’s take a look!

Read on to see how you can do this without disrupting the existing databases that are in good shape.

Leave a Comment

Truncating a Terabyte-Sized Table in a SQL Server Availability Group

Published 2026-11-04 by Kevin Feasel

Josh Darnell performs a test:

Years ago, I read a Q&A about the consequences (in terms of latency, log backups, etc.) of truncating a table with billions of rows in an Availability Group. I even wrote my own A to the Q with some measurements, although there was no AG involved in my test. Anyway, I was recently reminded of all that, and realized that AI might make it a lot easier to just test and see what happens.

Read on to see what Josh figured out.

Leave a Comment

Disabling FileStream and FileTable on a SQL Server Instance

Published 2026-10-19 by Kevin Feasel

Stephane Haby turns off a feature:

A customer recently asked me to disable FileTable/FileStream on their instance because the databases no longer use it, and to ensure that this is indeed the case.
The aim is to migrate to a new architecture without FileTable/FileStream in a second time.

The tricky bit is that the relevant database is also inside of an Availability Group. Click through for the process.

Comments closed

Taking T-SQL Snapshot Backups on AG Secondaries

Published 2026-09-15 by Kevin Feasel

Andrew Pruski takes a backup:

One of my favourite features of SQL Server 2022 was the T-SQL Snapshot Backups. The ability to leverage the snapshot capabilities of modern storage arrays to take application consistent snapshots of our databases is a game changer for anyone dealing with very large databases and struggling to hit their RPO within their RTO.

However, application consistent snapshots require the write IO of the database to be quiesced/frozen/stunned…and (rightly so) this can make DBAs nervous.

SQL Server 2025 introduced the ability to take full and differential backups of secondary replica databases in availability groups…but what about the T-SQL Snapshot Backups? Wouldn’t it be great if we could take those on the secondary and not have to stun IO on the primary database?

But Andrew didn’t stop there, so click through to see how you can do it.

Comments closed

T-SQL Snapshot Backups and Secondary Replicas

Published 2026-08-31 by Kevin Feasel

Sean Gallardy looks at the intersection of two SQL Server capabilities:

I was recently asked about T-SQL Snapshot backups on secondary replicas, which I hadn’t looked at for many years as it’s kind of a niche item. I was definitely surprised to find little caveats here and there, thus you get this wonderful post.

If you’re unfamiliar with T-SQL Snapshot backups, it’s a way to flush and hold changes made by IO to a database (your Freeze and Thaw entries in the errorlog) so that a stable disk image can be created and then backed up at the storage or filesystem layer. This can be an extremely fast way to create snapshots of databases that are quite large (assuming you’ve set everything up correctly) even across storage arrays (assuming it supports other options, such as ODX).

Read on to see what you have to do to get both to play nice together.

Comments closed

Removing and Re-Adding Databases into Distributed AGs

Published 2026-07-20 by Kevin Feasel

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

Comments closed

Error 1408 with Availability Group Automatic Seeding

Published 2026-07-02 by Kevin Feasel

Sean 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 closed

VS Code Extension for Availability Group Read-Only Routing

Published 2026-06-24 by Kevin Feasel

Denny 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 closed

Things to Watch with Contained Availability Groups

Published 2026-06-16 by Kevin Feasel

John 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 closed