There is no one-size-fits-all answer to this issue. As with most database issues, ‘It depends…’
For smaller objects ( currently up to about 250 KB) there are in fact performance advantages to holding these within SQL Server 2005 and above. This advantages tails off at about the 1 MB mark, and if database performance is the only issue then it is better to hold larger objects in the file system (Search “To Blob or not to Blob” from Microsoft Research.) The size of an object that has better performance in a database has increased over time, and is likely to further increase. Also, different database systems can efficiently handle larger (or smaller) objects than SQL Server, so do not blindly apply the 1 MB rule to Oracle, DB2, etc without further investigation.
Even with larger objects the database may be the best host. If you have 5 million files with a reasonable rate of CRUD activity that need to be replicated across sites for DR purposes, your Windows admin people will have problems making this work regardless of bandwidth. If the same 5 million documents are held in SQL Server, cross-site replication is not an issue. Management of those objects in SQL Server may be an issue, but the business expects IT to provide the best business solution, not just the fastest database solution.
It is important to know what options are available with SQL Server, how they perform, and the implications of using them.
<li> Storing binary blob data in the database causes the database to grow more than it needs to, as well as causing performance issues as the database gets busier.</li><li>It is possible to store the data within the file system and then store pointers (file names) to the data within the database.</li><li>SQL Server 2008 has introduced the Filestream option on the database which allows you to tell SQL to accept binary data, but it will actually store the data on the file system as a normal file outside of the database’s main database files.</li><li>These options perform well in SQL Server</li>
The implications of using them will depend on your application. Although storing objects larger than 1 MB in the file system (using Filestrream where available) gives the fastest database performance and the easiest database management, it may not always be the best solution for the business.
Regardless of how you store the large objects, there are standard things you should do to improve performance.
Always aim to put the large objects on different disks to the main database data, to avoid IO contention. If you store within the database, use a separate file group for the LOB data to control data placement. If you store within the file system use a separate mount point or driver letter to control data placement. Talk to your SAN administrator to get the LUNs sourced from different disks to the main database files, or use a separate RAID array if the disks are local to the server.
If you do store the data in the filesystem, consider turning off 8.3 filename construction to improve NTFS performance, but only do this if none of the software on your server needs 8.3 names (SQL Server 2000 will not install or upgrade unless 8.3 names are available, SQL Server 2005 and above noes not need 8.3 names.)