2

I have dozens of text reports that come in every day and I need to store them. I cannot just zip them and put them on the hard drive because the users aren't sophisticated enough to figure that out. I need to be able to associate some data with the report so they can look up report ABC for any given date. These files range in size from 11K to 1GB. Mostly they are less than 3 MB and many are less than 1MB.

What is the best way to store these? The options I am looking at are using Filestream or storing it in a varchar(max) field or zipping it and storing it in a varchar(binary) then unzipping it to display. I am open to any other options as well. Space efficiency is a high priority.

Thank you for reading this. I would truly appreciate any help or advice.

** The other post is very interesting and I learned a lot from it - thank you for referencing it. This is different from the other post in that my files are plain text files -- they are not binary files.

  • 1
    I recommend a Filestream. – paparazzo Oct 14 '17 at 18:06
  • 1
    As a side note, as your DB will hit a pretty chubby size with this kind of use, be careful about your index for every single one of your query : only the file field should be read on the hard drive. – Blag Oct 14 '17 at 19:41
  • 1
    I suggest you look at FileTables. – SqlWorldWide Oct 14 '17 at 21:25
  • 2
    This might help: stackoverflow.com/questions/13420305/… Also, are you open to storing references to the files in the database, and storing the files themselves directly in the file system? – mathewb Oct 16 '17 at 21:26
  • @mathewb - not really open to that. I have a team of people who do not want this data stored at all and my boss who wants to keep it. I'm figuring that stashing it in the database is a good strategy for the next three years. – Missy Oct 17 '17 at 23:22
2

Okay, I think that I'm beginning to understand the nuance of your question.

Given that space efficiency is a high priority, I think that you can strike the varchar(max) option from your list. Since your minimum file size is 11K, nothing is going to fit on a single page. All the files will be stored as BLOBs. SQL Server won't compress that (aside from compressing the backup), so you're not going to save any space with that approach.

Next, I'm going to make the assumption that processing time can be traded for space efficiency. In that case, I would recommend that you compress the files yourself before storing them and decompress before displaying them. This goes for any solution you end up going with. You should end up with better compression than letting the file system handle it (which applies if you use a Filestream).

So I think that your question boils down to Filestream (and possibly FileTable) or VARBINARY(MAX). The standard answer provided by Microsoft is:

When to Use FILESTREAM

In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. If the following conditions are true, you should consider using FILESTREAM:

  • Objects that are being stored are, on average, larger than 1 MB.
  • Fast read access is important.
  • You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

I think that you will need to reassess your figures using compressed file sizes, and then make a decision based off of that.

  • 1
    @Missy You're welcome. If this database is going to get large, you may want to consider partitioning. Since you're using Standard edition, you'd go with partitioned views. Here's a nice article to introduce you to it. This approach will allow you to split the underlying tables among filegroups, which can then be backed up independently from each other. It can save you from backing up the same (old) reports over and over again when only the newest report data is changing. – mathewb Oct 19 '17 at 14:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.