Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am working on an integration project, where my “app/web service” will sit in the middle serving documents.

Basically, a request is sent with the document id as part of the query string, I check if it exists and if it does, write the document (pdf) back to the response. Now, this is very simple to do and we have done this many times.

This is where it gets very tricky, there will be about 5200 new files added each week, that’s 3GB per week (so about 150GB of data per year). We are expected to keep 10 years of data.

What is the best way of storing these documents and searching has to be very quick.

Some options are:

If I choose to save all these files on a network share, initially it will be quick but over time it is going to be very slow.

If I choose SQL to store these files, I can’t really store all documents in a single table or utilize table partitioning, DB partitioning etc. If I go with DB partitioning that’s still 150GB DB file each year to backup and restore.

I was thinking implementing RBS for MS SQL but not sure how quick this is going to be.

Any suggestions or other options I might have....?

share|improve this question
    
2  
Which ways do you need to use to get access to the documents? Is it an option to only store meta-information in a database with a reference to a place on a disk where the actual document is? –  Pieter B Jan 13 at 8:30
    
For the actual files, a plain file system should work fine. Just don't place them all in the same directory, create subdirectories with a few hundred files. For example you could create a subdirectory for each day 2013/01/13/filename.ext. For the search use whatever database you like, mapping the query to a filename. –  CodesInChaos Jan 13 at 9:54

3 Answers 3

This is actually a very modest number of files for a doc management system. 5200 files x 52 weeks x 10 years is less than 3 million. Even at your own calculation, its only 1.5 TB of data over 10 years. That will easily fit on a hard drive.

For this volume of files, I would recommend keeping the files in the file system, not the database. It will give you some more flexibility with regard to how you store and backup the content. Plus it will take the load of transferring the files off the SQL server. You'd then just keep the metadata and a pointer to the file in the database.

If you don't want to store the files on site, you might also consider Amazon S3 or similar service. There'd be a cost, obviously, but you then dont have to worry about the hardware.

share|improve this answer
    
Thanks GrandmasterB, the documents are very sensitive so will have to store them on the network. But I do agree, it is a fairly manageable on file system Axel Kemper and Eugene have some good ideas. –  MicrosoftDevX Jan 24 at 7:12

You could compose the document ID like

yywwdcxxxx

yy   last two digits of the year
ww   the calendar week 1..53
d    the day 1..7
c    check sum digit from ID+filename (cf. http://en.wikipedia.org/wiki/Check_digit)
xxxx sequence number to distinguish documents from the same day

Directly map this ID structure to a directory structure in a filesystem. Create the new directories on the fly as needed.

Assuming that you want to keep the original document name, you could store the document under

..\yy\ww\d\c\xxxx_original_name

xxxx is the consecutive sequence number for documents for a given day. You can use a dynamic length for the sequence number to increase flexibility.

If you are not free to create your own document ID, you could map the existing scheme to an ID structure as suggested above.

The date oriented structure allows you to easily archive outdated documents year by year and to do a proper backup day by day.

The filesystem should probably be fault-tolerant. Consider using a RAID or store the files in parallel shadow directories to have a chance for recovery.


You also could store your files in a database or in an LDAP server. But 5200 files per week is not asking too much of a filesystem. And you still would need some scheme for unique document IDs.

Be aware that certain filesystems (under Unix) have a maximum number of files.

share|improve this answer
    
Thanks Axel, your solution definately seems more logical and simple to implement –  MicrosoftDevX Jan 24 at 7:14

If you need to store data along with documents - take a look at Sql server's FileStream feature.

If you don't - store them on a file system, but put each week/year in its own directory to keep the number of file in a single folder to a few thousands. That will keep it fairly fast.

share|improve this answer
    
thanks Eugene, did consider your solution but this requires a configuration change on the DB server which is shared by other systems. –  MicrosoftDevX Jan 24 at 7:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.