Sql Server Data / Log file separation -- important with Netapp storage?

[indianrock]

Best practices have always recommended that sql server data and log files be kept on separate storage due to the random read/write with data files and sequential read/write with transaction log files.  Does this matter on a Netapp storage device?  The other idea was that having numerous data files on separate paths/mountpoints/LUNs was valuable for IO.  Does this matter on a netapp device?

[asulliva]

Hello @indianrock,

Unless you have an edge case with very high IOPS and/or throughput requirements, I wouldn't worry too much about the random vs sequential nature.  It's handled transparently by ONTAP and you don't really need to think about it like with disks attached to a server.

My opinion (I am not a DBA, nor a database expert) is that there are two important reasons for using more than one disk/LUN:

1. Backup and recovery.  Using SnapManager or SnapCenter it's easy to coordinate snapshots with database IO operations to protect the data.  If logs and data are on the same disk/LUN, it's impossible to restore the data back to one point-in-time, then use the logs to bring the database up to a particular point-in-time.
2. Specifically for LUNs, having more than one LUN means more than one queue, which is helpful in high IO situations.

Hope that helps.

Andrew

[indianrock]

That does help Andrew.  What did you mean by having "more than one queue?"

When Netapp support helped us set things up initially, we did segregate data and log files for full recovery databases ( those where transaction log backups can actually be taken ).   For simple recovery ( full backups only ) databases, data and log files were located on the same LUN. Tempdb had it's own LUNs as did system databases.  Most of these are mirrored to DR.

For the database in question ( aspstate which stores session state info ), there is a very high level of log IO.   There is a fair amount of blocking in this database and during busy times, the customers start calling.     Actually other solutions that don't keep this information in the database are being explored, but in the interim I thought it would be a virtually free, quick exercise to move this simple recovery database's log file to another LUN.

Local IT management doesn't think it will help since they view the Netapp as one big, albeit smart, bag of disks.  What goes on internally in a Netappp storage device is mostly a black box to me.