4

I'm looking at trying to store a large amount of files in my SQL Server 2005 instead of saving them to a file system.

I recently read a white paper suggesting that anything over 1M should be stored on a file server as it can affect SQL Server performance.

Does anyone have any experience storing large binary data in SQL 05?

Thoughts on why I should or should not do so?

Improve this question
2

2 Answers 2

Reset to default
4

Using SQL Server for small file storage is usually a bad idea for different reasons.

  1. SQL Server costs money. A lot of money. Even if everything works well, you're paying an extra for what a simple file server (if done right) would do for "free".

  2. SQL Server is transactional, and ACID compliancy has it's overhead. It's much slower than writing to disk without a transaction. NTFS has partial write protection, but the overhead is much smaller than with SQL Server.

  3. Writing to a filesystem is straightforward, but to access SQL Server you need to go through a driver, network/shared memory etc. Everything you do to access the files now needs the database in the middle.

  4. It's not meant to be a file store. Eventually, what you get is a huge .mdf file on the disk which you can't do much with. Want to free up space on disk after deletion? You need to shrink the database which can take ages.

There are multiple other reasons. Perhaps another way to put it - SQL Server is not meant for this kind of thing, and if you start using it for files it's going to grow very fast. Now, managing a small database is one thing, and a huge one is another (number of rows not relevant for this discussion, only data size) - and you don't want to go into that area if you don't have to.

Improve this answer
2

SQL Server can be used as a file store...think Microsoft SharePoint.

The content database stores everything associated to its SharePoint site. That includes any file you upload to a document library, that is not restricted by SharePoint (.exe, .bat, etc). It will store that file in a table as image data type up to a size of 2GB. Now you can store a file larger than 2GB in a SharePoint site, if the quota limit allows it but I am not sure at that point how it is stored in the table.

With the new feature in SQL Server 2008, FILESTREAM can change this a bit by putting a link to the file on the file system instead of actually storing it inside the database. Although with SQL 2005 this is not an option.

With any application it requires careful planning on the SQL Server side to ensure the resources given to SQL Server can handle the workload. If you have planned accordingly though SQL Server is more than capable of getting the job done.

Improve this answer
2
  • Just because Microsoft did it with SharePoint doesn't make it a good idea. SharePoint is widely recognized as being about the worst possible database design for an application out there. Anyone using it as a best practice guide is going very much the wrong direction. The SharePoint database was written by application developers not by database experts. As far as I know the SQL Server team or a DBA wasn't brought into the project until after the first couple versions were released.
    – mrdenny
    Commented Jul 18, 2011 at 20:41
  • I'll agree with you, it is not a good example for designing an application.
    – user507
    Commented Jul 19, 2011 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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