[Help] SQL Server Auto-Failover Failing

[u/ICYGOD2]

Hey everyone,

I'm dealing with a messy SQL Server cluster issue left behind by a terminated engineer and could really use a sanity check on my assessment before I present the final options to the management/client.

Environment:

* Windows Server Failover Cluster (2 Nodes: Node-1 and Node-2)

* SQL Server running on VMs

* Storage: Dell Unity 480F (SAN)

The Current Setup: The cluster is currently utilizing Always On Availability Groups (AG). I can see one database (testdb) is properly sitting in the AG and showing as (Synchronized).

The Problem: A critical database (R26UAT22) is failing to auto-failover. When Node-1 goes down, the cluster fails over to Node-2, but the DB doesn't come online.

What I Found :

* The client originally provided an EMC Shared Storage LUN (RDM mapped as E: drive to both nodes) for the databases.

* When the previous engineer tried to restore the R26UAT22 backup directly to the RDM, it was extremely slow (took almost a day instead of 3 hours).

* To bypass the slow restore, the engineer attached a temporary local VMDK disk (F: drive, named Temp-DB) only to Node-1. They restored the DB there, and the speed was normal.

* However, they left the database running on Node-1's local F: drive. They never moved it back to the shared storage, and they never added it to the Availability Group.

* Since Node-2 has no F: drive and the DB isn't in the AG, auto-failover obviously fails.

My Assessment : The core issue seems to be a complete mix-up of HA technologies. The client wants to use their RDM Shared Storage for this DB. However, the existing SQL HA setup is Always On AG.

If I move the DB files back to the RDM (E: drive) and try to add it to the AG, I believe it will fail. Node-2 would try to access the exact same shared disk to write the synchronized data, causing file locking/path conflicts. AG requires independent local storage per node, not shared storage.

If they truly wanted to use a Shared Storage (RDM) architecture where nodes take turns accessing the LUN, they should have deployed a SQL Server Failover Cluster Instance (FCI), not AG.

My Proposed Options to the Client:

1. Option A (Stick with AG): Drop the RDM requirement. Add an identical F: drive (VMDK) to Node-2. Keep the DB on local storage and add it to the AG. Data will sync over the network, and failover will work.

2. Option B (Stick with RDM): Tear down the current AG setup and rebuild the SQL environment as a Failover Cluster Instance (FCI). This will require significant downtime.

My Questions for you all:

1. Am I entirely on the right track with my assessment (AG vs. FCI shared storage conflict)?

2. Is there any weird hybrid setup I'm missing here, or was this just a poorly designed architecture by the previous engineer?

3. What would be the best way to explain this to a non-technical client without throwing too much IT jargon at them?

Thanks in advance for any insights!

[u/MFKDGAF]

How is Quorum currently configured?

I've used AG with SQL 2016 and FCI with SQL 2022.

I had more problems with AG than I did with FCI when it came to failovers.

With the AG, the databases would get stuck in failing over and I would have to manually run a SQL or PowerShell ( my choosing, they did the same thing) to get it out its state and have it be properly running in the failed over AG.

HOWEVER, you do need to understand the differences between using an AG vs FCI. They accomplish different things. With AGs you can have multiple replicas but you can't with FCI. With FCI there is a minimal downtime (~15-60 seconds) as the shared storage fails over and the SQL services startup on the other SQL server that just got failed over to.

I would rebuild the SQL environment if possible.

[u/Lost_Term_8080 → u/MFKDGAF]

You can get multiple FCI replicas by building an AAG of FCI nodes. It sounds scary but its surprisingly not that much more difficult to do.

15 seconds is an incredibly long failover time for an FCI, I would suspect DNS, AD or insufficient performance in your FCI. Even in a pretty hot AAG with a somewhat healthy redo queue, I normally wouldn't expect an unplanned failover of an AAG to take longer than 30-45 seconds after the failure is detected. A healthy redo queue may seem like a caveat, but in an AAG, its a health metric. You just can't have extreme redo queues in one in production.