Databases on file share

Introduction

You might have lot of virtual machines (VM) for Dev and Test environments. And a storage with huge amount of space to fit all databases. What are the options? Probably the best for performance would be to connect to storage’s LUNs directly from the guest OS (windows iSCSI Initiator). But if you want something much much simpler and still efficient, read on…

Mounting the remote drive

The idea is to have OS (C drive) of VM on the VM Host’s local drives (eg SSDs), because it is local, no network round-trips, so VM are snappier, super-fast for launching apps and for temp db. And put user databases on the storage, so they do not spend space on the host. If you put data and log files on file share directly, the performance is really bad, I’m not sure what is the reason. But there is a equally simple way that performs much better: a VHD/VHDX file on a normal file-share of the storage!

The problem is: after every VM restart, this VHD is dismounted from the guest OS, and your databases cannot start, being forever in “RECOVERY” status. Because the disk was not ready when SQL Server service was started.

The solution is simple:

1. Create a Scheduled Task which triggers “At Startup” and starts a program “powershell.exe” with arguments:

-command “Mount-DiskImage -ImagePath \\nas1\VMDisks\SQLDEV1_Disks\SqlData.vhdx; net start mssqlserver”

Of course, adjust the path to your VHDX. This will assign next available drive letter to the mounted drive, so the order of mounting matters because you cannot choose the letter. And notice it starts SQL Server service – right after mounting the drive. That is very important!

You can add additional triggers, eg. on workstation unlock, periodically etc, additional executions won’t throw error. Because if computer wakes up from sleep, you may find the disk disconnected – seems that “startup” is not fired when computer wakes from sleep.

2. Change SQL Service startup to “Manual”

That will ensure drives are mounted on VM startup, and available BEFORE SQL Server starts, so databases can be started normally, together with VM.

Summary

For dev and test we can save space by leveraging file-share storage to put VHDX there and mount them into VM guest with a simple process, described here. I hope it is useful for you.