SQL Server Storage-Design Best Practices?

[DerOest]

Hello everyone,

I'm looking for advice on how to design the Storage layout of SQL Server VMs (2014 with AlwaysOn Availability Groups) regarding best performance/operability with Veeam.

Basically, we followed Derek Seaman's blog/walktrough: http://www.derekseaman.com/2014/09/sql- ... yment.html
This guide results in 2 VM cluster, each with a lot of harddisks
C:(OS Drive)
D:(SQL Binaries)
E:(Data files 1)
F:(Data files 2)
J:(TempDB)
L:(DB log files)
T:(TempDB log files)
S:(Backup)

I understand that Derek uses multiple HDDs attached to different SCSI controllers for better performance, but in our case, performance is not an issue (and extremely unlikely to ever be) - powerfull hardware, low IOPS/TPS.

But now that we have Veeam, i see that it takes quite some time for the backup to map all those drives to the backup proxy for processing, and i'm not sure if this setup even makes a lot of sense.
Plus we've had some problems with this setup (Veeam causing VSS errors -> causing cluster failovers, servers unreachable because of snapshot delete stuns).


Wouldn't it then be "better" to have a less complex setup like this?
C:\ - OS & SQL program files (sized big enough to hold the temporary transaction logs before they are copied off)
D:\ SQL Database Files
E:\ for Transaction logs


I'd really appreciate some input on this!

Greetings from Germany,
Timo

[PTide]

Hi,

Wouldn't it then be "better" to have a less complex setup like this?

Such a complex setup would make sense in case all hard-drives were separate physical devices. Also please note, that in order to explore and recover the necessary database, you should have a transactionally-consistent backup of your Microsoft SQL Server created successfully. If you backup only the drive that contains your database then you'll be able only to restore the database files.

Thank you.

[DerOest]

Hi PTide,

yes - of course those are all VMDKs sitting on the same datastore, and a transactionally consistent backup is what we want
The result must be a full, consistent server backup plus hourly transaction log processing.

So i understand that it really is ok to go with the 3-Disk-Setup.

It's just that with the "complex" setup, we've encountered some issues:
Hourly transaction log processing would sometimes cause VSS errors, which in turn caused the Availability Group to failover to the second node and resulting in an "unrecoverable error" on the SQLServer VSS Writer.

Also removing the VMware Snapshot sometimes caused the VM to be unresponsive/lose network connections (which i think is snapshot stun) - i'd hope that this would be less of a problem with the 3-disk-setup

[EugeneK]

Hi,

Since you're using proxy to process all VMDK's, the processing time should really be in parallel: up to 15 disks mounted to each controller. If you see the slow processing times, it's very unlikely due to the number of disks you process, but rather the limitations in place:
a) Concurrent tasks limit per repository
b) per Proxy
c) per Datastore on ESXi host

The Snapshot removal and connection loss issue is not really relevant to the number of VMDK's in place: it's solely the time required to stun the VM for Snapshot creation/removal time, how busy the storage is and how much data needs to be processed. Likely you notice the disconnect because it takes longer periods of time to commit this data from redo logs, which normally is the result of either Vmware Tools being at blame or/and storage overworked.

All in all, regardless of the number of VMDK's used, if the VM stays the same active to cause the interruptions during backups, as well as the same amount of data is changed/to-be-processed, the symptoms will not go away on their own by just changing the number of disks in use, these are typically different type of issues.

I'd recommend to check why Proxy takes long time to process VMDK's - is it waiting for infrastructure resources to be available? If so, adjusting the limits/resources for the Veeam components may improve it.