Storing files in SQL Server

Question

Storing files in SQL Server

hoodg

Ten Centuries

Points: 1342
More actions
August 20, 2003 at 2:00 am

#82826

I just read an article in asp.netpro magazine suggesting to store files in SQL Server for access via web. I'm starting a project where around 200-300 files should be available on web (size roughly 2-3MB each though some as big as 40MB). Max of 2 or 3 new files will be uploaded a week.
Any opinions on whether this is a reasonable solution, or is the file system a better option?
Would I be able to use full text search on the column containing the file contents?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

guarddata SSCertifiable Points: 6583 More actions · Answer 1

This depends a lot on what you want to do with the files. We generally avoid storing files in the database because we access them from so many different applications, it keeps the transaction log size down, performance is better (if the files are > 50 KB), and backup and restore issues are easier for our network team.

Pretty much depends on your specific implementation, though.

My $0.02

Guarddata-

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

I like storing them in the db, they are secured and get backed up. Not a clear answer on this, but there are times when it is a good solution.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

mccork SSC-Insane Points: 22288 More actions · Answer 3

quote:
Would I be able to use full text search on the column containing the file contents?

Yes you will. This is one of the benefits of storing the files in text/image columns in SQL. If you do so, suggest you look into the BOL suggested configuration for full text indexes. For starters, provide a separate disk array for the FT catalogs.

You might also decide to put text/image data on a separate array too. Suggest you prepare for such by storing them in a separate filegroup from the start.

Apart from the full text indexing capabilities, I'm generally in favour of leaving files in the file system (sorry Andy)and storing pointers/paths in SQL Server. As you've seen, you'll get many differing opinions, none of them 100% right.

Cheers,

- Mark

Cheers,
- Mark

hoodg Ten Centuries Points: 1342 More actions · Answer 4

I can see certain advantages - full text indexing, no problem with permissions (the data access layer only would need access). But I imagine there will be a certain overhead.

On the indexing side, if the files are binary like word or pdf docs, and stored in an image column, are they still OK for full text index?

BTW The article said the next version of the file system will be based on sql server.

yolousa Old Hand Points: 367 More actions · Answer 5

Let me add some more advantages should you decide to go with 2005. NVARCHAR(MAX), VARCHAR(MAX) and VARBINARY(MAX) can store up to 2 GB of data and behave pretty much like the regular VARCHAR, NVARCHAR and VARBINARY, so no more LOB pointers... One more thing, there is server side compression solution for SQL Server 2005 (http://www.sqlcompress.net).