Database tuning for Sitecore Media Library

Posted on by

There seems to be tons of arguments whether you should store your Media Library files in the database or as files on disk. I usually prefer storing them in the database, since it’s usually easier to manage the data this way. The performance impact is virtually none anyway, since I always use a CDN. But I agree with many of the cons arguments about this as well, so I’ll leave that decision to you.

If you choose database storage, one thing worth considering, is moving the Blobs table into another file store, so that you can put the binaries on a cheaper disk volume. That is if you for example run your primary database on SSD disks, it’d work just fine having the blobs on conventional hard disks.

Create volume
Open SQL Server Management Studio and click Properties on your database. In the Files-tab, add a second file and locate it on your preferred volume. In Filegroup, select and give it a name. In my example it’s called “Volume”.

Note: If you do this on an existing installation with a lot of data in the Blobs table, make sure you have enough space for the ldf files. You can for example let the log file grow on the volume disk as well during this operation.

Move the Blobs table
There is no simple way to move an existing table into a new volume, and there are quite a few guides on this. SQL Server: Move Tables between Filegroups is a quite nice guide, but I ran into some trouble with this one as well because of collaction conflicts.

Fortunately, I was able to bring the CMS off-line for a while, so I used this simple solution instead:

CREATE TABLE dbo.Blobs2 (
	Id uniqueidentifier NOT NULL DEFAULT (newid()),
	BlobId uniqueidentifier NOT NULL,
	[Index] int NOT NULL,
	Data image NOT NULL,
	Created datetime NOT NULL
) ON Volume TEXTIMAGE_ON Volume
GO

INSERT INTO dbo.Blobs2 (Id, BlobId, [Index], Data, Created) 
SELECT Id, BlobId, [Index], Data, Created FROM dbo.Blobs 
GO

DROP TABLE dbo.Blobs
GO

SP_RENAME Blobs2, Blobs

Remember to do this for both Master and Web database. It’s good to do this in a fresh install as well, since you volume management will be much easier when you have the Blobs table already moved. You can for example have the “Volume” file group on the same disk to start with, and move that one later having the CMS and database online.

4 thoughts on “Database tuning for Sitecore Media Library”

  1. tablespace for improved performance. In addition, it provides automated deletion of audit records from the database tables and operating system files.

  2. I would store documents themselves in the Azure Blob storage (not table storage). Outside of the fact that it is pretty expensive to store documents in a SQL Azure database that charges a penny per meg (or less depending on volume), SQL database is generally not a good place for documents. SQL is a relational database that provides benefits of ability to do queries, joins, etc. There is usually no benefit to storing large documents or images in a SQL database, especially when there is a highly scalable central storage system that is pretty cheap to store/access.

  3. Do you have any recommendation on media library database storage in context of the DMS? We are discussing whether or not to move images into the DB or on disk, but are not sure if any of the images or files are still available for the DMS to analyze them.

    In my knowledge, the images in the Media Library are represented either way by nodes and are therefore representable in the DMS.

    • Moving the binaries from disk to db shouldn’t change anything to DMS. They will still work the same way from a content perspective.

Comments are closed.