I need to be able to demonstrate SQL Server recovery procedures and since our DBs are all virtualized and using a SAN for backend storage, I need to know what the best way is to simulate a disk failure and recovery using a tail-log backup.
I've tried removing the data volume from the VM in vSphere but SQL doesn't seem to be aware that the MDF file is no longer accessible and doesn't trigger a fault, and none of the other options I think of can be done "live". I'm just not enough of a vSphere guy to know my options here.
Best Answer
I can really only see a couple of options here.
They both assume that the only thing on this datastore is the VMDK that's housing the SQL Server's MDFs. No VMX files, no log files, no datastore heartbeating.
The first would be to unmount the volume on the host in question. You can do it via the vSphere clients,
esxclior it's PowerCLI equivalent. I don't actually know if vSphere will let you do this while it's got a running VM attached, but it might.The second is to just un-present the volume from the SAN or mask it on the host in question. It's a really drastic thing to do and might cause more pain than it's worth since it's likely to cause the host to throw a PDL or APD error, but it will give you what you want if the first option isn't possible cause VMware blocks it (which is actually a good thing).
If you're attempting the second option, you should read up on this article about PDL/APD and the articles listed in the 'See Also' section.
Oh, and don't do it to a production box.
And have backups before trying any of this.