Binary file storage - SQL Table vs Windows 1

[iwease]

Hi,

I need to store lots of binary files. I'm considering storing them in a table in SQL server and I'm also considering storing them in a windows file folder (and just storing the path in SQL server).

My main worry is access time. Will accessing the file from SQL server be way slower than trying to do so from a windows directory?

any other considerations?

 

[jmeckley]

I don't think it's a issue of speed so much as an issue of requirements. things to consider.
1. hardware running DB.
2. hardware running application.
3. diskspace for servers.
4. user accessiblity.
5. is file versioning required?

If your db has limited hardware or diskspace, then store the files on a sperate server and save the link in SQL Server.

If you require auditing you can either turn on OS auditing for the directory where you store the files, or have an audit table within your application.

If requirements state that files must be versioned then storing them in a db is a valid option. Note: this can require alot of diskspace depending on the filesize. I would only recommend this if it was required.

If you do opt for storing files in the db. I would use this table structure:

create table file_data (
 file_id int not null,
 file_data binary not null,
 constraint file_data__PK primary key (file_id)
)
create table file_meta_data (
 file_id int not null identity (1,1),
 file_name varchar(255) not null,
 size bigint not null,
 created_by varchar(25) not null,
 created_on datetime not null,
 [more columns if necessary]
 constraint file_data__PK primary key (file_id)
)
alter table file_data
add constraint file_data__file_id__FK foreign key (file_id) references file_meta_data (file_id) on delete cascade

you could also add an audit/access table and link to file_meta_data (file_meta_data:file_audit 1:n). this could track who is viewing the file, and when revisions of the file were created.

In saying all of this I find it's more common to store the link in the db and save the file on the network. With this method remember to validate the file actually exists before opening the file. Since the network files and db are not synced, a file could be deleted/moved/renamed outside the application.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[iwease]

Wow...that was great. Thanks. That is a lot to consider. I do need versioning, but, I think it should not be to hard to just rename the file when storing it in a windows directory (if I opt to take that approach)

 

[jmeckley]

if you go the route of modifing the file name you'll need to lock down file permissions on the directory tree to limit manual edits/deletes/moves.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Lamprey13]

In genreal it is considered bad practice to put files into a DB. I admit I have done it in the past myself and there are pros and cons both ways. But, (again in general) I would let the file system do the job it was designed to do and hold your files.

If you are interested you should do some searches on the topic. This topic has been discused by many DB centric folks and they may be able to help shed some light on your particual situation.

-Ryan

 

[iwease]

I found this article

http://www.zend.com/zend/trick/tric...-sept-2001&kind=tr&id=358&open=1&anc=0&view=1

where the guy says it is slower in a DB. But, it might not really relate as he is using MySQL which I would imagine is not as efficient as SQL server (pretty great for a free DB engine though)