Multiple nodes within a cluster. Using HyperV as the virtualisation layer. VMs use Fibre Channel SAN for storage but trying to explore the possibility of using Local SSDs for the TempDB. Issue is when the VM migrates.
I know TempDB doesn't need to persist after migration as the DB will just recreate it, but, when it migrates, the VHD will no longer exist. I would imagine the DB would fail if it can't find the directory to create the TempDB?
Is it possible to create a VHDX per node, which sits on the exact same directory (For example, it is always the 'T' Drive), so that every VM will simultaneously write to it regardless of which node they are on? I don't want a VM on Node 2 trying to write to a local drive on Node 1. I want to keep things local at all times.
Best Answer
As far as SQL Server config: Since you're not using SQL Server clustering with shared storage, I believe you don't need to do anything but configure all your instances with Tempdb on T drive (or whatever standard path you choose). See Example A at https://msdn.microsoft.com/en-gb/library/ms345408(v=sql.110).aspx.
As for how to ensure that T drive is available as local storage in all your VMs for when SQL Server starts up, that someone else will have to help with (perhaps stackoverflow with virtualisation tags).
And be sure to test your whole failover process, don't take anyone's word for it.