SQL Server Performance Forum – Threads Archive

files storaged in SQL server tables

Hi.
I have an application in wich general bynary data are stored in a table formed by a numeric PK and column with "image" type.
This table is used as a file/docuemnt repository in which users store Word documents, Excel sheets, different image files, PDfs ,etc …. This way DB size is actually increasing its size in 600Mb a week -actually size is around 13Gb-. This rate is spected to be increased as more users will be using the system. My questions are:
Is there any limitation for SQL server 2000 to store and manage this huge amount of data?
Can I expect any kind of performance decreasing regarding this size increasing fact?
Is it better to consider any other storage system -i.e directly using hard disk-? Where can I find information regarding best practices in this sense: use of data base as a docuement storage system? Thank you very much in advance.

First things first. Welcome on board! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Read this:<a target="_blank" href=http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx>http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx</a> It’s a really long article, but it’s probably the best discussion on the pros and cons of storing BLOBs in a db or not that there is available for SQL Server. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Hi, I am not a DBA but i can share some of my experience. We are using similar methods to store manuals and prints in database.
Current database size of my binary storage is about 146GB [B)], my vendor divide binary tables from normal text based information table (he create 2 database – total size approx 300GB). For now, i don’t see big issue of speed. It is always a good practice to pull only those fields (in SELECT query) which you need and not to pull all fields with * and remember never select binary field when you wanted to extract information to display as list it will slow your performance dramatically. Regards, Waqar.

Thanks for that feedback! It’s really interesting to have someone with a that large db which stores BLOBs. I guess you know that this topic is subject to a kind of almost fanatically lead discussions. One side emphasizes on the use of the filesystem and only stores that link back in the db, while the others (me included) use the db for storing. However, I only run my private archive which is only at about 4 GB. But I’m also very satisfied with performance. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Thanks a lot to all. I’ll have a look at the document you told me. Seems fine. Anyway, seems to be a good idea to have all binary-non-structured data in a separated database. Doesn’t it? I think this deal with the "repository" convept in a better way, making backup and mantainance much easier.

]]>