Storing Sitecore Media Library Assets in File System VS Storing in Database

Introduction

All the assets that we keep inside Media Library folder of the content tree are stored inside the database by Sitecore (default behavior). We can change it and store assets in the file system by making some easy configuration changes.

We will see how this can be achieved. We will compare both the approaches (storing in file system and database) by checking their advantages and disadvantages and try to figure out which approach suits which scenario.

Background

Let us first see where Sitecore stores media assets in the database. Login to the database server where Sitecore DBs are present. Open Master DB, there is a Blobs table. This table will store all media library assets.

This table has 6 columns out of which 2 are important to note, namely BlobId & Data. BlobId column contains a GUID to uniquely identify a blob. Data column contains the binary data for the blob.

So, when we upload an asset in the media library, Sitecore creates a media item with a field named media. Sitecore stores the actual asset in blob table and takes BlobId field value from this table and puts in the media field of the media item. This way, Sitecore is separating blob storage and content storage. Sitecore leverages this concept to store media in the file system instead of storing it in the DB, if required by user.

How to Store Media Assets in the File System?

Sitecore stores all media library assets in the database. This is because of the following configurable setting present in the web.config file of the Sitecore website.

<!--  UPLOAD AS FILES
            Controls whether Sitecore stores media as files or as database records by default.
            This setting is ignored if the Media.DisableFileMedia setting is true.
            Default: false
-->
<setting name="Media.UploadAsFiles" value="false"/>

To make Sitecore store media assets in the file system instead of in the DB, we need to update the value of the above setting to true. See below.

<setting name="Media.UploadAsFiles" value="true"/>

Now, Sitecore will start storing assets in the file system. By default, it stores them at path, "/App_Data/MediaFiles". Sitecore has a configurable setting for changing this as well. See below.

<!--  MEDIA - FILE FOLDER
            The folder under which media files are stored by the system.
            Default value: /App_Data/MediaFiles
            It should be different from MediaFolder setting
 -->
 <setting name="Media.FileFolder" value="/App_Data/MediaFiles"/>

We can change the value of the above setting to store blobs anywhere in the file system.

Storing Media Assets in Database VS Storing in the File System

So, when should we use file system storage of media assets instead of default database storage? Let us compare both of them to figure it out.

Database storage	File system storage
Pros It is convenient since all Sitecore items in the tree are already stored in the database. This follows suit with the standard of how Sitecore is built. No extra reliance (besides the database itself) on actual files when moving or syncing environments of a site (e.g. content management, content delivery, QA, dev).	Pros Databases are leaner because they don’t contain any binary media data. Allows for files to be pushed out to a CDN (like Akamai, Limelight) with custom coding. You can directly change assets on the file system if you have access to it (like a developer).
Cons Storing large binaries (especially larger than 1 MB) in the DB causes slow retrieval and hence is a performance overhead. There is a very old (2006) Microsoft research paper which states that “objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem”. You can read it here. There is another article based on lab experiment which compares Filestream data type (SQL server will store in file system) with Varbinary data type (default, storing in DB) in SQL Server. It can cause bloating of the databases, making them oversized and cause performance issues because of too much indexing. For example, let’s say we have to store images of 10000 products in Sitecore DB and each product has 4 images and the average size of each image is 200 KB (considering the fact that these days we are dealing with high resolution images). This calculation will come out to be 10000 * 4 * 200 KB = 8 GB. If we store videos also, size will increase even more. Every time we take backup of these DBs and restore them, it will take enormous amount of time.	Cons When Sitecore creates physical media files, it adds the media item’s GUID to the name, so changing an asset in the media library will result in an additional file on the file system. It’s much more difficult to sync up environments since the actual folders on the file system need to get synced (e.g. 2 content delivery servers) – either a sync tool or the staging module can help with this.

Learning

Taking into account all the pros & cons mentioned above of both the approaches, it can be said that the tradeoff of losing CMS provided features (security, workflow of items, publishing) by storing in DB can be neglected due to the improved performance by storing assets on the file system.

Therefore, if the overall number of images is less and the number of large images (larger than 1 MB) is less, then storing assets in database should be considered for convenience, otherwise we should consider the file system route.

History

• 15^th February, 2016: First/draft version of the tip