SQL Server 2005- File storage for non-DB files
I've come acros a scenario a number of time in the past few years, and have not been able to get a good answer. I work with production systems that process a lot of sale transactions. My current setup uses MS SQL Server 2005. I have a Dell PE2950 with six drives- It's maxed out. They are all set up as RAID-1 mirrors of two drives each- three volumes total. OS is stored on the first volume... and then it gets unclear what the best configuration is. I have the database data files, and I have the translogs. Those must go on separate physical volumes.... but then there are more types of data that must go on this server. There are input and output files that just can't cut it if moved to a different server- The performance hit brings every process down to a crawl if I try other locations. The output files are all small PDF's- we must generate a few hundred a day. The input files are mostly zip files, which the application must open, process, and archive. The PDF's generally are 100K or less, with a few exceptions per day. The ZIP files are less than 200KB. What is the most optimal way to organize the files? My thought was to leave the MDF's, PDF's and ZIP's on one of the two remaining volumes and move the translogs to a dedicated drive. Should I set custom format options on the drive holding input and output files- a small allocation unit size? Any advice you can provide will be helpful.