Disk organisation when running virtual SQL Servers

I have been running SQL Server on a virtual platform (vSphere from VMware) for several years. This post describes my approach to disk organisation. I have had to think about this post because I suspect that some of this will be controversial – it is my approach, and it seems to work for me.

SQL Server runs within a guest operating system in vSphere. Filestore is based on a SAN, and organised into LUNs by the SAN administrator. Within this environment, I have seen several approaches to managing the virtual disk dives within the guest operating system.

First, organise the guest filestore in one big disk. The operating system, SQL binaries, data, log files and any backups are all on the same drive. Some DBAs take this approach because the virtualisation layer and the SAN are thought to remove control over the filestore.

I don’t like this approach.  It means that for practical reasons vSphere snapshots cannot be made – the data volumes are too large. If a process spins out of control, there is a danger that the entire disk will fill, probably stopping the server – not a good outcome.

Second, put the operating system on one disk (call it C:) and everything else – SQL binaries, data, logs etc. – on another disk (call it S: for SAN).  This has a small advantage over the first approach because it is now possible to take vSphere snapshots of the C: drive before applying Windows patches. Otherwise, the disadvantages of the first method still apply.

Finally, my preferred approach, split the filestore in the guest operating system as follows.

C:\ – install the windows operating system. about 40Gb seems reasonable.

E:\ – I install the SQL Server binaries here. I also store the system databases (master, msdb, model) on this drive.Within Vsphere, this drive (or Hard Disk) is included within snapshots.

Neither Hard Disk 1 (The C: drive) and Hard Disk 2 (the E: drive) are marked as independent. Independent disks are not affected by snapshots. The remainder of the Hard Disks (logical drives within the guest) are marked as Independent within vSphere.

G:\ – I use a drive for SQL Server backups.  L:\ – This drive holds most SQL Serve log files.

S:\ – this drive holds most SQL Server data files.

T:\ – this drive holds the SQL tempdb.

In addition, depending on the system, I may place data and log files for specific databases on separate drives.

The advantages of this approach are follows.

When I apply a service pack, I can take a snapshot (using vSphere) of the virtual machine. The C and E drives are included in this snapshot. If there any problems with the service pack, fall-back (if appropriate) is simple – apply the snapshot.

Second, I have to some extend protected against filestore filling up and stopping the server.  With this system, for example, if a backup overruns and fills the G: drive, the SQL Server still operates. I still have to resolve the problem and correct the backup chain. However, I have maintained the service. This can be extended – to some extent – by placing some databases on their own logical drives. So even if the ‘main’ S:\ drive fills up, the financial database – running on the F:\ drive – is still available.

Third, there is a possibility that I can tune I/O by moving database files to different LUNS. This is a major challenge due to the operation of the SAN caches and the virtualisation layer. However, this may be an option if you are facing performance problems. To approach this you will need access to the SANs performance tools.

Finally, I need to mention a dis-advantage. This approach appears to require more file store than the other approaches. However, given the low cost of file store (even your expensive SANs) this seems a minor downside.