Moving from a SqlBlobProvider to something else…

I’m currently having a lot of fun out of updating and moving some demo sites to Azure Websites. When you are moving to Azure you obviously need another blob storage than the default file-based blob storage and 2 popular options comes to mind: Azure Blob storage or – just to simplify everything and use the SQL Blob storage.

In one project I decided on going with the SQL Blob storage from https://www.coderesort.com/p/epicode/browser/EPiCode.SqlBlobProvider. It’s so easy. 2 Nuget packages installed, and 1 scheduled job later, and all my blobs are working straight from the database…One less dependency to worry about!

“Why were you worried about depedencies?” I hear you cry. Well – - I ‘m just the kinda guy who thinks simpler is better. Also – for this specific use case, a demo site, I’d like to be able to easily ‘reset’ the site every night and just focusing on the resetting/restoring the database sounded nice to a lazy guy like me.

Well. Functionality wise, the SQLBlobProvider works like a charm. Mostly. At first we didn’t think much about it, but it did seem like the site was quite slow..Especially in edit mode. It’s not that I had that many blobs – maybe just around 100 images on the entire site, no videos, no sound, no big documents. But each image had 5 different defined resolutions and some might even have had multiple versions – and then we are looking at 100*5*versions blobs in SQL.

Now, the way the SQLBlobProvider is implemented in SQL is using the wonderful Dynamic Data Storage. Which is pretty awesome. And in general scales well. But I’m not sure it was meant for that many byte[] collections to be passed back and forth between the webserver and the database all the time. I don’t know exactly where the bottleneck was..But the site was a pain to work with. (SQLBlobProvier does have the ability to cache locally on disk but I never managed to get that to work on my setup – so whether that would have helped I can’t say).

After trying many other great speed tricks with no major luck, I decided that it must be the SQLBlobProvider and I decided to switch to the Azure Blob Storage. Installing the Azure Blob Provider and setting up the storage is super easy and takes no time at all – but then I was faced with the next challenge – how to move all my blobs from SQL to Azure Storage. Again, on EPiCode (https://nuget.episerver.com/en/OtherPages/Package/?packageId=EPiCode.blobconverter) there is a nice BlobConverter tool but it assumes all blobs are on disk (file Blobs). Somehow I didn’t feel like downloading every image 1 by 1 to my local machine, re-uploading them and re-inserting them wherever they should be. EPiServer Import & Export didn’t really seem like a smooth option for this either. So…I ended up writing this little code that worked like  charm. If anyone else find themselves in this situation, feel free to use it (but before you do make sure you have configured a new blob provider and it’s set to be the current /default).

using System;
using EPiServer.Core;
using EPiServer.PlugIn;
using EPiServer.BaseLibrary.Scheduling;
using EPiServer.Data.Dynamic;
using EPiCode.SqlBlobProvider;
using System.Linq;
using EPiServer.Framework.Blobs;
using System.IO;

namespace EPiServer.Demo.QJet.Business.Jobs
{
    [ScheduledPlugIn(DisplayName = "Convert Sql Blobs Job")]
    public class ConvertSqlBlobsJob : JobBase
    {
        private bool _stopSignaled;

        public ConvertSqlBlobsJob()
        {
            IsStoppable = true;
        }

        /// <summary>
        /// Called when a user clicks on Stop for a manually started job, or when ASP.NET shuts down.
        /// </summary>
        public override void Stop()
        {
            _stopSignaled = true;
        }

        /// <summary>
        /// Called when a scheduled job executes
        /// </summary>
        /// <returns>A status message to be stored in the database log and visible from admin mode</returns>
        public override string Execute()
        {
            //Call OnStatusChanged to periodically notify progress of job for manually started jobs
            OnStatusChanged(String.Format("Starting execution of {0}", this.GetType()));
            int cnt = 0;
            //Add implementation
            var store=DynamicDataStoreFactory.Instance.GetStore(typeof(SqlBlobModel));
            foreach (var b in store.Items<SqlBlobModel>())
            {
                if (b.Blob != null) { 
                    var ms=new MemoryStream(b.Blob);
                    var bb = BlobFactory.Instance.GetBlob(b.BlobId);
                    bb.Write(ms);
                    cnt++;
                    if ((cnt % 50) == 0) OnStatusChanged("Converted " + cnt.ToString() + " blobs");
                }
            }

            //For long running jobs periodically check if stop is signaled and if so stop execution
            if (_stopSignaled)
            {
                return "Stop of job was called";
            }

            return "Change to message that describes outcome of execution";
        }
    }
}