Press "Enter" to skip to content

Category: Availability Groups

Filestream and Availability Groups

Published 2024-07-22 by Kevin Feasel

Chad Callihan kicks a hornets’ nest:

Migrating databases to new servers and configuring them for availability groups can be a complex process. It can be more complex depending on what type of sneaky features are in use by the databases being migrated. I encountered an interesting issue migrating a database that had been setup to use FILESTREAM and wanted to show the steps I took to identify and resolve it.

Read on for the issue and what happened. It was a small thing overall, but the kind of small thing that can eat up the better part of a day if you’re not aware of it.

Leave a Comment

Dealing with AG Secondaries Falling Behind

Published 2024-07-18 by Kevin Feasel

David Fowler troubleshoots a wait type:

Are you struggling with a laggy redo and a build up in the redo queue on your readonly secondaries? Are you suffering with high PARALLEL_REDO_TRAN_TURN waits? Then this magic remedy could cure your ailments.

Read on to learn more about the parallel redo process and one thing you can do if it’s falling too far behind. I wouldn’t permanently switch back to serial redo, but maybe make that switch if there’s some consistent pattern to when the process falls behind.

Leave a Comment

Limiting Jobs to the Primary Replica of an AG

Published 2024-07-08 by Kevin Feasel

Chad Callihan doesn’t want jobs running willy-nilly:

Transitioning from a failover cluster configuration to an Availability Group configuration brings with it all kinds of “fun” challenges. One such challenge that you may not have considered is the handling of jobs on whatever server is Primary, along with secondary servers. Let’s briefly discuss a potential challenge and an option to address it.

Click through for the example and a solution. Eitan Blumin has another solution in the comments, so check that one as well.

Leave a Comment

Low-Downtime Migration Techniques from SQL Server 2017 to 2022

Published 2024-06-17 by Kevin Feasel

Yohei Kawajiri describes three techniques for performing a SQL Server migration:

It is possible to configure a SQL Server Always On availability group with a primary replica running on SQL Server 2017 and a secondary replica running on SQL Server 2022, but there are important considerations and limitations to keep in mind: 

  1.  Backward Compatibility: SQL Server supports having replicas on different versions, but the primary replica must be on an older version than or equal to the secondary replicas. Therefore, having SQL Server 2017 as the primary and SQL Server 2022 as the secondary is valid. 
  2.  Database Upgrade Path: When you decide to upgrade the primary replica to a newer version, you need to follow a specific upgrade path to ensure minimal downtime and data integrity. 

Building an availability group? Yeah, makes a lot of sense. Performing log shipping? Sure, I could see that working. Database mirroring? I did not expect to read that one, mostly because it’s been deprecated for a decade.

Comments closed

Contained Availability Groups in SQL Server 2022

Published 2024-05-24 by Kevin Feasel

Rich Benner talks about a new feature:

SQL Server 2022 introduced of a number of new features, and one of the more interesting of these is Contained Availability Groups. These are very close to normal Availability Groups, but they have some very important differences. In this post, I’ll compare the new features included and some of the pitfalls that we’ve come across with their implementation.

Read on to learn more about the functionality and several harsh limitations.

Comments closed

The Risk of Query Failure on Readable Secondaries in SQL Server

Published 2024-02-22 by Kevin Feasel

Kendra Little explains a problem:

If you use readable secondaries in Availability Groups or Read-Scale out instances in Azure SQL Managed Instance, you may have queries fail repeatedly if there is a glitch and statistics are not successfully “refreshed” on the secondary replica. Those queries may keep failing until you manually intervene.

This has been the case for a few years, and it’s unclear if Microsoft will fix this. There is a well established support deflection article which documents the issue and provides ‘workarounds’.

Read on for Kendra’s thoughts. I haven’t run into this before, myself, but I also don’t tend to make very heavy use of readable secondaries.

Comments closed

Working around a Contained Availability Group Error

Published 2023-12-05 by Kevin Feasel

Sean Gallardy talks about an error:

Contained Availability Groups are the most recent update to the Availability Groups feature, and a great update at that! They are completely new in SQL Server 2022, and like any new feature (or even mature ones) there will be some bugs. Enter in some Access Violations (AVs) that may occur when creating a new contained availability group.

Read on for an example of the error and what you can do until Microsoft fixes it.

Comments closed

Failed to Update Replica Status Due to Exception 35222

Published 2023-11-24 by Kevin Feasel

Sean Gallardy troubleshoots a problem:

Read Scale Availability Groups can be pretty useful in the right places and for the right things and were a latest feature update for AGs until Contained AGs came along in 2022. Read Scale AGs don’t integrate with clustering of any type and they behave somewhat as mirroring used to where there is no real coordination of resources and it is up to the administrators to make the proper judgement calls or automate whatever possible scenarios they deem important.

Read on to see what this error means and why it’s less of a problem than it first appears.

Comments closed