patternMinor
SQL Server 2005, Large Binary Storage
Viewed 0 times
sqlstorage2005binarylargeserver
Problem
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?
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?
Solution
Using SQL Server for small file storage is usually a bad idea for different reasons.
-
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".
-
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.
-
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.
-
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.
-
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".
-
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.
-
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.
-
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.
Context
StackExchange Database Administrators Q#3924, answer score: 4
Revisions (0)
No revisions yet.