Storing general files in a SQL Server database

Tags:
SQL Server
Is it wise to store files such as .doc's or .pdf's as binary objects in a SQL Server database? If there can be issues down the line, is there a way to design the SQL database to make it easier to work with?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.
<ul>
<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>
</ul>
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.)

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,065 pointsBadges:
    report
  • Kccrosser
    Storing BLOBs in the database is a better general solution than storing them in a separate file system. The primary advantage to storing them in the database is that the store can occur as part of a database transaction. If the file is stored in a separate filesystem, it is too easy for the file system and database records "pointing" to the file system to become disjoint, particularly if there is any kind of system error. While database transactions can usually be recovered from the transaction logs, the file system transactions are not so easy to recover. This can lead to database records that point to non-existent files. The same consideration applies to file system backups. If the BLOBs are stored in the database, the backup of the data records and the BLOB data will be more consistent and less vulnerable to problems should a recovery from a backup be necessary. Physical file capacity requirements are virtually the same whether the file is stored in the database or in a separate file system. And, unless the system architecture is very complex (and uses multiple servers), the physical file I/O will be essentially the same whether the file is written to the database file store or to a separate file system store. That said, I would advise that BLOB data be stored in separate tables designed to be BLOB holders, rather than including BLOB data logically embedded in a field in a data record along with other non-BLOB data fields. That will ensure that the BLOB data is only retrieved when the query explicitly joins to include the data, rather than accidentally when a query performs a "select * ..." on the field data. Although most databases physically separate BLOB data and rows with non-BLOB data (by putting a pointer in the data row), a "select" that accidentally includes the BLOB column(s) will still cause the data to be retrieved and sent to the requestor
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following