title: Best way to store/retrieve millions of files when their meta-data is in a SQL Database
body: <p>I have a process that's going to initially generate 3-4 million PDF files, and continue at the rate of 80K/day. They'll be pretty small (50K) each, but what I'm worried about is how to manage the total mass of files I'm generating for easy lookup. Some details:</p>
  
  <ol>
  <li>I'll have some other steps to run once a file have been generated, and there will be a few servers participating, so I'll need to watch for files as they're generated.</li>
  <li>Once generated, the files will be available though a lookup process I've written. Essentially, I'll need to pull them based on an order number, which is unique per file.</li>
  <li>At any time, an existing order number may be resubmitted, and the generated file will need to overwrite the original copy.</li>
  </ol>
  
  <p>Originally, I had planned to write these files all to a single directory on a NAS, but I realize this might not be a good idea, since there are millions of them and Windows might not handle a million-file-lookup very gracefully. I'm looking for some advice:</p>
  
  <ol>
  <li>Is a single folder okay? The files will never be listed - they'll only be retrieved using a System.IO.File with a filename I've already determined.</li>
  <li>If I do a folder, can I watch for new files with a System.IO.DirectoryWatcher, even with that many files, or will it start to become sluggish with that many files?</li>
  <li>Should they be stored as BLOBs in a SQL Server database instead? Since I'll need to retrieve them by a reference value, maybe this makes more sense.</li>
  </ol>
  
  <p>Thank you for your thoughts!</p>