Store Files in SQL Server or keep them on the File Server?

Question

Currently we have thousands of Microsoft Word files, Excel files, PDF's, images etc stored in folders/sub folders. These are generated by an application on a regular basis and can be accessed at any time within that application. As we look to upgrade we are now looking into storing all these documents within SQL Server 2005 instead. Reasons for this are based on being able to compress the documents, adding additional fields to store more information on those documents and applying index’s where necessary.

I suppose what I’m after is the pros and cons of using SQL Server as a document repository instead of keeping them on the file server, as well as any experience you might have in doing this.

We would be using C# and Windows Workflow to do this task.

Thanks for your comments.

Edit

---

How big are the files?

between 100k = 200k in size (avg. 70KB)

How many will be?

At the moment it’s around 3.1 Million files (ranging from Word/Excel and PDF's), which can grow by 2,600 a day. (The growth will also increase over time)

How many reads?

This one is difficult to quantify as our old system/application makes it hard to work this out.

---

Also another useful link pointed out on a similar post covers the pros and cons of both methods.

Files Stored on DB vs FileSystem - Pros and Cons

Answer 1

rule of thumb for doc size is:

size < 256 kb: store in db
265 kb < size < 1 MB: test for your load
size > 1 Mb: store on file system

EDIT: this rule of thumb also applies for FILESTREAM storage in SQL Server 2008

Answer 2

If you upgrade all the way, to SQL Server 2008, then you can use the new FILESTREAM feature, that allows the document to appear as a column in a table, yet to reside as a file on a share, where it can be directly accessed by a program (like Word).