SQL Server Case of the Week: When Availability Group Replica Communication Issues Cause Log Drives to Fill

Summary – Log File Filling up on the Availability Group

Networking firewall changes made over the weekend resulted in the client’s three disaster recovery (DR) replicas to not be able to communicate with the three High Availability (HA) replicas in their SQL Server Always on Availability Group (AG). This resulted in log files growing out of control for busy databases in their Availability Group and drive space on their log drive to plummet, putting them at extreme risk for losing database availability and impacting recoverability.

Context

The client in question is running a six replica AG, all on SQL Server 2019 Enterprise Edition. Three of their replicas are in synchronous commit mode in their primary data center and act as their HA, while the other three replicas are in asynchronous commit mode and reside in their secondary data center and act as their DR. This AG and the replicas in it are business critical as it serves as their main backend for production workloads.

The Problem

The problem was noticed when reviewing the client’s Daily Health Check on a Monday morning. Beginning on Sunday’s Daily Health Check, the tool that is used to perform these daily checks was unable to connect to the three DR AG replicas and analyze the SQL Server Logs. This was also accompanied by reports of the client’s three HA replicas reporting low space on their log drives as well. The SQL Server AGs were in trouble over the weekend, and this was the Monday priority now. I saw this and immediately jumped in to investigate further.

The Investigation

After connecting to the client’s environment and opening SQL Server Management Studio, I connected to their primary replica in their Availability Group. I ran the following query to look at the log_reuse_wait_desc column from the sys.databases DMV to see what the databases were waiting on for log reuse and what was causing the log drive to fill:

SELECT d.name, d.log_reuse_wait_desc FROM sys.databases d

After running this, I saw that many of the databases were waiting on AVAILABILITY_REPLICA for the reuse description. This immediately indicated to me that something was going on with their AG. I then expanded the Always On High Availability folder in the SSMS Object Explorer and navigated to the Availability Replicas folder. After expanding this, I saw that the three DR replicas were showing as disconnected. This indicated to me as this being the reason that we weren’t getting log reuse and points out an interesting fact about synchronous commit and asynchronous commit modes for an AG. Just because one or more replicas is in asynchronous commit mode, doesn’t mean SQL will ignore waiting for a log record to be hardened to the log file before marking that portion as reusable. All asynchronous commit does is tell the application that it doesn’t have to wait for confirmation from that specific replica that the transaction has been hardened to move on. SQL still needs confirmation from all replicas asynchronous and synchronous that a transaction has been hardened before it can mark that section in the log file for reuse. In this case, the three DR replicas were in a disconnected state, and SQL could never get confirmation from those replicas, so it held on waiting for confirmation, hence the log reuse wait description being that of AVAILABILITY_REPLICA.

After seeing this, I then simultaneously started opening RDP sessions to the primary replica and each of the DR replicas, as well as opening the SQL Server Log on the primary replica. The first thing I checked was to make sure that the cluster was online and the nodes and resources were up and healthy. They all were so this ruled out any cluster related issues for me. Next, I checked that the SQL Server Service was up and running on all of the DR replicas. It was. After, I opened the SQL Server Log on the primary replica in SSMS. I found the following message in the SQL Log:

“A connection timeout has occurred while attempting to establish a connection to availability replica ‘Replica Name Here’ with id [SomeIdHere]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.”

This made me immediately want to test ports to confirm a hunch I had from this message. I then went to my RDP session into the primary replica and opened PowerShell as Administrator. I then ran the following test connections from the primary replica to each of the DR replicas:

Test-NetConnection -ComputerName <ComputerNameHere> -Port 1433

Test-NetConnection -ComputerName <ComputerNameHere> -Port 5022

The first test checks if the host computer you’re running the command from can ping the target computer, and also checks to see if it can communicate through the provided port. Port 1433 is the default port for SQL Server, and port 5022 is the default port for Always On Endpoint communication. Both failed.

This told me that the networking team had made some sort of firewall changes over the weekend to the DR replicas, and this impacted the primary replica from being able to communicate with them through those ports. There are three ports that are critical and need to be opened when dealing with SQL Server Availability Groups. They are:

• Port 1433: SQL Server’s default communication port. (or the port selected at setup)
• Port 5022: The Always On Endpoint communication port that AG’s use to talk and send data from one replica to another. (or the port selected at setup)
• Port 3343: This is the port that the cluster uses to communicate. Since the Failover Cluster Manager showed all resources showing healthy and online, I was able to rule this check out.

Because of the DR replicas not being able to communicate with the primary AG replica, and the log reuse waiting on this to be resolved, one of the log files had grown to almost a Terabyte in size! There were only eight gigabytes free on the log drive and the client was moments away from having bigger problems on their hands.

The Fix

Fortunately, the fix for this was a straightforward one. I opened Windows Firewall with Advanced Security on each of the DR AG replicas and opened ports 1433 and 5022. As soon as I did this, I was able to refresh the Availability Replicas folder and see that each of the DR replicas was able to communicate with the primary again. From that point it was monitoring and ensuring that the databases were able to catch up and shrink the log files down to normal size after log backups were taken. There were a couple databases that needed to be re-seeded as they got too far behind, and their data synchronization was suspended.

Key Takeaways

The biggest lesson from this is to show how it doesn’t matter how “highly available” your architecture or solution is. If the communication between teams is not where it should be, the result could lead to outages that could have been avoided.

Apart from that, knowing the critical ports that need to be opened and stay opened for your Availability Group deployment to be able to communicate properly is essential to not only setting up your AG but troubleshooting these types of communication issues when they come up.

The Straight Path Team and Skills

Jordan used his knowledge of Always on Availability Groups, Clustering, and general SQL knowledge to help troubleshoot and resolve this issue. SP_CheckAG can also help inventory your setup, troubleshoot things like this and help you learn more about your SQL Server High Availability environment. It’s a free community tool from us to you – no e-mail required, just download and use.

This post is part of our Case of the Week series—real SQL Server issues and lessons from the field.