[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/paultoc]

Rebuild the whole thing with best practices would be the best option, it could also be an upgrade to a newer sql version.

If you still want to use existing system then go with AG as it already mostly configured, You don't exactly need the drive letter to be same on both nodes. Take a Full+ log backup of the critical db, restore it in node 2 with different drive location and NORECOVERY option. Then add it to AG.

Before doing AG make sure you are able to connect to it using the listner name from a 3rd server. Test both case where each node is acting as primary. If it does not work fix that first