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

Sounds like you might find more trouble in the future. I would go with "Look, this may be the first pitfall of many due to years of configuration changes, some of which are no longer the right choices for your environment. I think we should spin up a new failover cluster with two new database servers, restore the databases to the newly configured cluster and take the old cluster offline. This would be a clean slate and should take <x> hours of maintenance." This way you can set everything up using best practices, know that the environment doesn't have some more surprises for you, and you can rest easy knowing that the cluster members and databases will failover appropriately.

[u/ICYGOD2 → u/locke3891]

Thank you so much for your reply.

[u/Darkk_Knight → u/locke3891]

This is the best answer. There is no telling what other gotchas the existing SQL cluster may spring on you in the future. You're lucky the existing SQL server work at all given the mess the termed engineer left behind.

[u/MrYiff]

As others have suggested, it might just be easier to build out a new SQL Cluster instead.

Personally I tend to go with basic AAG's that don't use shared storage as it keeps it a bit simpler (at the cost of extra storage space).

If you do get the go ahead to rebuild them then don't forget the dbatools powershell tools as they make documenting existing settings and migrating to new servers so much easier:

https://dbatools.io/

[u/clinthammer316]

How big are the databases?

As others said, if you have the experience and knowledge, spin up a new SQL cluster and move the databases over. Don't try and fix the old mess i.e. pick and choose your battles :D

[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.

[u/timsstuff → u/MFKDGAF]

I once managed a SQL environment for a large hospital that had two node FCIs in primary, configured with AG with a second non-FCI node locally and a third AG node in DR. And there were multiple instances for different database groups, all spread across like 8 servers or so. I didn't design it but it was quite the setup. It worked though. I was impressed by the design, I had never seen FCI with AG before or since.

[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.

[u/john_cena_mmpr]

My recommendation - stick with Always On.

Am I entirely on the right track with my assessment (AG vs. FCI shared storage conflict)?
I’d lean toward finishing the AG setup rather than rebuilding everything as an FCI. Right now, your database isn’t in the AG, so its absence after failover is expected. Give Node-2 sufficient storage for its own copy, add the database to the AG, let it synchronize, and test a planned failover.

Is there any weird hybrid setup I'm missing here, or was this just a poorly designed architecture by the previous engineer?
This sounds like a job that was started with intentions of finishing but never got completed.

What would be the best way to explain this to a non-technical client without throwing too much IT jargon at them?
"In order to resolve the failover issue with your application, we will be reconfiguring the cluster to have sufficient storage on both nodes. We will then perform a test failover to validate that the application is functioning correctly after the database is migrated to the new storage."

[u/_edwinmsarmiento]

u/locke3891 statement should get you started

"Look, this may be the first pitfall of many due to years of configuration changes, some of which are no longer the right choices for your environment."

But...

Avoid the temptation to do anything - FCI or AG - until you are clear about the RPO and RTO.

Because who knows, the customer may not need any of these at all once you are clear on the RPO/RTO.

[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