I inherited a two-node Windows Server Failover Cluster running SQL Server on virtual machines with Dell Unity SAN storage. The environment uses Always On Availability Groups, and one database, testdb, is synchronized correctly.
The problem is that the critical database R26UAT22 does not come online after Node-1 fails over to Node-2. I found that the database files were restored to a temporary VMDK attached only to Node-1 as drive F:. The database was never moved back to the shared storage on drive E: and was never added to the Availability Group. Node-2 therefore has no access to the database files.
My understanding is that Always On Availability Groups are designed for independent storage on each replica, with changes synchronized over the network. They are not intended for both nodes to access the same database files on a shared LUN. A shared-storage design would generally use a SQL Server Failover Cluster Instance instead.
I see two possible paths: keep the Availability Group and provide suitable local storage to Node-2, then seed and add the database properly; or redesign the SQL deployment as a Failover Cluster Instance if the shared SAN LUN is a firm requirement. Before presenting this to the client, I would appreciate a sanity check. Is this assessment correct, is there a supported hybrid configuration I am overlooking, and how would you explain the difference to a nontechnical client?
3 Answers
If the requirement is to keep using the shared SAN LUN as the database storage, a Failover Cluster Instance is the architecture that matches that requirement. Only one SQL instance owns the shared disks at a time, and the SQL role moves between nodes during failover. Converting the current setup may involve substantial planning and downtime, so it should be treated as a migration rather than a quick configuration fix.
If shared storage is not mandatory, keeping the AG is usually simpler operationally. Give each node suitable local or dedicated VM storage, seed R26UAT22 to the secondary, and test both planned and unplanned failover.
Your main conclusion is correct: the database on Node-1's private F: drive cannot fail over to Node-2, and it is not participating in the Availability Group. Adding the database to an AG would require a properly configured replica on Node-2, with storage that Node-2 can access independently. Both replicas should have their own database files; they should not simultaneously use the same shared database files on the LUN.
Before changing anything, verify the SQL edition, AG configuration, endpoint and service permissions, database recovery model, synchronization state, and all data and log file paths. If the existing environment has been modified repeatedly, rebuilding may be safer than repairing it piecemeal.
Given the history, I would seriously consider building a clean replacement pair and migrating the databases instead of trying to uncover every old workaround. Document the current instance settings, logins, jobs, linked servers, permissions, backup configuration, and database file paths first. Then restore or seed the databases on the new design and perform a controlled cutover.
For the client, I would describe it this way: the current database is stored on a disk that only one server can see, while the failover process sends users to a server that cannot see that disk. We can either give both servers their own synchronized copy, or use a design where the shared disk moves together with the active SQL server. The present configuration mixes those two approaches.
A clean rebuild also makes the final failover test much more meaningful. Test database availability, application connectivity, jobs, backups, and failback rather than checking only whether the cluster role changes owners.
I would also confirm that the cluster is actually providing the availability mechanism you intend. A Windows cluster by itself does not make a standalone SQL database highly available; the SQL database must either be part of an AG or belong to an FCI role.