2016-09-22 11:18 AM
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?
2016-09-22 08:11 PM
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:
Hope that helps.
2016-09-25 06:27 AM - edited 2016-09-25 06:33 AM
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.