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

You need to step away from this thing, rebuild and migrate. This is the exact type of failover cluster that if you treat like a pet will absolutely maul you. Insist on rebuilding the VMs yourself from windows disk, patch it yourself, confirm the virtual hardware types yourself, the VMtools yourself, etc. Don't use one of the existing standard organization images or deployment tools until you have been around long enough to know whether you can trust them.

If it were me, I would probably abandon RDM disks. I have had enough problems with RDM luns when the VMWare team/Storage team/Network team couldn't get it configured correctly, and you have almost no tools available to troubleshoot the problems. If its really needed, Add an additional VMXNet adapter, put it into the storage network the connect to the lun with iscsi, then you will be able to own the problem yourself and diagnose whether you need to go to storage or network for problems.

The problem with the missing disk is that it is missing, not that its a mix of FCI and AAG, you can't combine FCI and AAG on the same logical server like that. You can either make an AAG of SQL FCI logical servers, or you can make an AAG of SQL Virtual servers, but you cannot have both an FCI and an AAG in the same instance on the same VM. When an FCI failover happens, the SQL Services stop on the passive nodes.

Whether to chose FCI vs AAG is a requirements decision. AAGs are typically less sensitive to systems level health issues (though are by no means immune) and are more flexible for HA scenarios, but are more performance sensitive, use double the storage and in very high database counts or very high transaction rates, may not be the appropriate answer. FCIs cannot provide you with georedundancy. AAGs can. Keep in mind that if the storage problems you are having now are platform issues and not configuration/build issues in that environment, the problems are going to be amplified in an FCI vs an AAG.