Store files into DB (Newbie)

Question

I start to develop a web app (asp.net) that will store every kind of document in a DB. It's a SQL 2008 DB, and I'm analizing what is the best way to store "metadata" in a DB solution? A few type: - file system pointer in protected folder, for me is too complex and insicure - filestream - varbinary(max) column - others? any suggestion? Someone with a similar dilemma?

Thanks all.

check out the msdn whitpaper on filestream http://msdn.microsoft.com/en-us/library/cc949109.aspx — DForck42, Mar 21 '11 at 19:16

score 0 · Answer 1 · answered Mar 21 '11 at 17:45

0

Why is it too complex or insecure? It's best to store a filename on the database and access the files themselves from the filesystem. You can use NTFS permissions to secure the locations of the files so that only the account under which your ASP.NET application (pool) is running can get to them. This is especially true for large files.

answered Mar 21 '11 at 17:45

kprobst

16,165
5
32
53

Time ago I found problems in backup strategies and sometimes data integrity...with large files you're right it's better because metadata don't cause the overgrowing of the DB. – Mar 21 '11 at 23:07

score 0 · Accepted Answer · edited May 23 '17 at 09:58

0

I would check out SQL Server 2008's FILESTREAM attribute on a varbinary column. I haven't used it personally, but it's the first thing I would check out.

I asked a similar question. I'd also check out the great answers people gave there.

edited May 23 '17 at 09:58

Community

1
1

answered Mar 21 '11 at 17:51

Aaron Daniels

9,563
6
45
58

I didn't see your question sorry. The filestream technology looks to be the best way, I have to look at how cost the implementation in my solution. – Mar 21 '11 at 23:02

score 0 · Answer 3 · answered Mar 21 '11 at 17:52

I tend to stay away from keeping the files in the file system and only storing the metadata in the database. I prefer to keep the files in the database as it greatly simplifies any backup/recovery strategy. The only exception I have to this is if you know you're going to be storing files across multiple devices, which tends to happen if you know you are going to have more images than you can reasonably store on a single logical drive.

Another suggestion I'll make, if you are storing the files in the database, is to store the contents of the file in its own table (i.e. only have the contents and a PK). If you have a file in the table with the entity (say an avatar image in the user table), then if someone were to run a select * against the user table, then the files would be returned as well, which can be very costly if you are returning a large set of users.

I don't have much personal experience with filestream in SQL Server, so I can't speak to it very well, but I would think either that or varbianary(max) would probably be good choices.

Store files into DB (Newbie)

3 Answers3