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.

  • Unless I've misunderstood, this sounds like a general virtualisation question (providing a given drive letter with local storage in migratable VMs), not directly related to SQL Server. You'll probably have better luck asking somewhere that has virtualisation tags available.
    – T.H.
    Feb 3 '17 at 15:05
  • @T.H. Good point. It's a tricky one as it's also a question of whether TempDB can work this way, and whether the performance will still be acceptable.
    Feb 3 '17 at 15:20
  • Are you using SQL Server clustering features, or only VM clustering features?
    – T.H.
    Feb 6 '17 at 10:31
  • @T.H. Well there are multiple DB VMs in a AlwaysOn Availability Group for the SQL Server. So it's no major concern if one of the VMs go down, as the secondaries will take over. HyperV hosts are in a cluster, heartbeating to allow the VM itself to move around the cluster.
    Feb 6 '17 at 12:45

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.