Feasability of storing PDFs and WORD.DOC files in MSSQL?

[Don Vaillancourt]

I'm building a system when one can upload a document to the website.

I will be storing the document on the hard-drive for quick/easy access,
but I was also thinking of storing it in an existing database since most
of the sites information is all stored there.

As well there would be only one place to worry about backing up. And if
the file on the hard-drive was ever missing or became corrupted, I could
restore it form tha database. Is this feasable? Has anyone ever done this?

--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@webimpact.com>
web: http://www.web-impact.com

/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

[Joe Weinstein]

Don Vaillancourt wrote:

I'm building a system when one can upload a document to the website.

I will be storing the document on the hard-drive for quick/easy access,
but I was also thinking of storing it in an existing database since most
of the sites information is all stored there.

As well there would be only one place to worry about backing up. And if
the file on the hard-drive was ever missing or became corrupted, I could
restore it form tha database. Is this feasable? Has anyone ever done
this?

Sure it's possible to save any size binary object in the DBMS, but it is
not usually a performant use of a DBMS as a simple file store. I would
first consider a hardware solution for backups of read-only files. Either
just store copies on separate disks, or get a fancier RAID setup where
any one disk failure won't lose any data.
Joe Weinstein at BEA

[Erland Sommarskog]

Don Vaillancourt (do**@webimpact.com) writes:

I'm building a system when one can upload a document to the website.

I will be storing the document on the hard-drive for quick/easy access,
but I was also thinking of storing it in an existing database since most
of the sites information is all stored there.

As well there would be only one place to worry about backing up. And if
the file on the hard-drive was ever missing or became corrupted, I could
restore it form tha database. Is this feasable? Has anyone ever done
this?

I have not done this myself, but certainly it is a common scenario.

Storing the documents on disk makes for a simple implementation.
Storing them in the database requires a battle with the text/image
data types, which are somewhat difficult to use.

But as you note, storing in the database is safer. Backup is simpler,
and a file on directory can easily "disappear". And if all you store
is the file path, another problem is that you don't have two-phase
commit with the file system.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Don Vaillancourt]

I know that technically I can store this info in the database and
retrieving it is not an issue.

My issue really is how manageable will the database be with tons of
PDFs, Word, etc. documents in it. Or how willing people would be
willing to manage such a beast.

Right now our databases are about 300MB each. I may decide to create a
second database, not sure, but the total number of documents may be at
the very least in the 1000's which would most probably create a database
about 5GB + overhead.

To some this may be an intimidating size to manage. It would certainly
fit on a tape backup.

I'm just wondering about other issues that may come up.

Erland Sommarskog wrote:

Don Vaillancourt (do**@webimpact.com) writes:

I'm building a system when one can upload a document to the website.

I will be storing the document on the hard-drive for quick/easy access,
but I was also thinking of storing it in an existing database since most
of the sites information is all stored there.

As well there would be only one place to worry about backing up. And if
the file on the hard-drive was ever missing or became corrupted, I could
restore it form tha database. Is this feasable? Has anyone ever done
this?

I have not done this myself, but certainly it is a common scenario.

Storing the documents on disk makes for a simple implementation.
Storing them in the database requires a battle with the text/image
data types, which are somewhat difficult to use.

But as you note, storing in the database is safer. Backup is simpler,
and a file on directory can easily "disappear". And if all you store
is the file path, another problem is that you don't have two-phase
commit with the file system.

--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@webimpact.com>
web: http://www.web-impact.com

/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

[Erland Sommarskog]

Don Vaillancourt (do**@webimpact.com) writes:

I know that technically I can store this info in the database and
retrieving it is not an issue.

My issue really is how manageable will the database be with tons of
PDFs, Word, etc. documents in it. Or how willing people would be
willing to manage such a beast.

Right now our databases are about 300MB each. I may decide to create a
second database, not sure, but the total number of documents may be at
the very least in the 1000's which would most probably create a database
about 5GB + overhead.

To some this may be an intimidating size to manage. It would certainly
fit on a tape backup.

Few of our customers that run our system have databases that small. My test
database alone is 600 MB.

And 5 GB in one database is easier to manage, than 300 MB in a database + a
lot of loose files.

However, if you are using MSDE the size could be a concern, since MSDE
has a size limit of 2 GB.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Don Vaillancourt]

No MSDE for us. We tell how clients which version of whatever database
they must purchase in order to use our software.

Otherwise, from your experiences, my issue is really a non-issue.

Thanks for your input.

Erland Sommarskog wrote:

Don Vaillancourt (do**@webimpact.com) writes:

I know that technically I can store this info in the database and
retrieving it is not an issue.

My issue really is how manageable will the database be with tons of
PDFs, Word, etc. documents in it. Or how willing people would be
willing to manage such a beast.

Right now our databases are about 300MB each. I may decide to create a
second database, not sure, but the total number of documents may be at
the very least in the 1000's which would most probably create a database
about 5GB + overhead.

To some this may be an intimidating size to manage. It would certainly
fit on a tape backup.

Few of our customers that run our system have databases that small. My test
database alone is 600 MB.

And 5 GB in one database is easier to manage, than 300 MB in a database + a
lot of loose files.

However, if you are using MSDE the size could be a concern, since MSDE
has a size limit of 2 GB.

--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@webimpact.com>
web: http://www.web-impact.com

/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/