Thursday, February 01, 2007

Give Me One Good Reason Why You Would Store Documents In The Database?

Why would you ever store PDF, Doc or Excel files in the database? What is the point? I don't see any advantages only disadvantages
Let's say you have 8 webservers and 1 big SQL monster. If you store these documents in the DB and you get hit by these 8 webservers for documents all the time your DB is going to slow down. A much better way is to have the files on the webservers itself, sending 9MB pdf files over the network is just wrong.
Also if you store all these files in the DB your backups will take much longer.
Updating BLOBs is another pain in the neck; UPDATETEXT and WRITETEXT are not my favorite SQL commands.

So here is the question:
Would you store images/documents/spreadsheets in the Database and why?

3 comments:

onpnt said...

The only advantage I've ever seen is added layers of security. This still doesn't register when it comes to real-world situations as a advantage to me though. There are plenty of mechanisms in place for security to not do this and hinder performance

dmarkle said...

1) Transactional processing. The filesystem doesn't natively provide transactional integrity, but the database does. Some people *may* need this.

2) Guaranteed integrity. It can be really comforting to know that when you RESTORE your database, all of the pertinent files will be where they need to be, with no holes. Storing files on the FS gives you no guarantees, and requires another, separate backup mechanism.

That being said, most of the time, I store docs on the filesystem and put pointers in the database to it, because the files usually aren't *that* important. And life has gotten better for SS 2005 users, as IMAGE and TEXT are now deprecated (we're supposed to use VARCHAR(MAX) and VARBINARY(MAX).

SQL said...

VARCHAR(MAX) and VARBINARY(MAX)make life soooooo much easier on SQL Server 2005