Adding a second master database

Have you ever wanted to add a second database so you can keep millions of items in it and not having to publish it?

Well, to be honest me neither until a few days ago. The scenario is that you have lots and lots of data in other systems (PIM, DAM, Pdf-library or whatever). You want this data to be accessible in Sitecore but you do not want to publish it since that will take forever and cause all sorts of problems.

So in this case you could keep all that data in the second master database (in a bucket of course, since the content tree will be quite hard to handle otherwise).

The second master database idea

After some debating and testing with different technologies and ideas the idea to just add a second master database, add all the items we need in there with some kind of synchronizer on top of it to populate it with items came to us. It seemed easy, and it actually turned out not be that complicated.

Creating the database and adding it to Sitecore

Creating the database is really easy, just make a copy of the master database and add that in your SQL-server as well.

To make the database accessible in Sitecore you need to do two things. First add the new database configuration to the web.config via an include file. I called it “asset”.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <databases>
      <!-- asset -->
      <database id="asset" singleInstance="true" type="Sitecore.Data.Database, Sitecore.Kernel">
        <param desc="name">$(id)</param>
        <icon>Images/control_image16x16.png</icon>
        <dataProviders hint="list:AddDataProvider">
          <dataProvider ref="dataProviders/main" param1="$(id)">
            <prefetch hint="raw:AddPrefetch">
              <sc.include file="/App_Config/Prefetch/Common.config" />
              <sc.include file="/App_Config/Prefetch/Master.config" />
            </prefetch>
          </dataProvider>
        </dataProviders>
        <securityEnabled>true</securityEnabled>
        <proxiesEnabled>false</proxiesEnabled>
        <publishVirtualItems>true</publishVirtualItems>
        <proxyDataProvider ref="proxyDataProviders/main" param1="$(id)" />
        <workflowProvider hint="defer" type="Sitecore.Workflows.Simple.WorkflowProvider, Sitecore.Kernel">
          <param desc="database">$(id)</param>
          <param desc="history store" ref="workflowHistoryStores/main" param1="$(id)" />
        </workflowProvider>
        <archives hint="raw:AddArchive">
          <archive name="archive" />
          <archive name="recyclebin" />
        </archives>
        <Engines.HistoryEngine.Storage>
          <obj type="Sitecore.Data.$(database).$(database)HistoryStorage, Sitecore.Kernel">
            <param connectionStringName="$(id)" />
            <EntryLifeTime>30.00:00:00</EntryLifeTime>
          </obj>
        </Engines.HistoryEngine.Storage>
        <Engines.HistoryEngine.SaveDotNetCallStack>false</Engines.HistoryEngine.SaveDotNetCallStack>
        <NotificationProvider type="Sitecore.Data.DataProviders.$(database).$(database)NotificationProvider, Sitecore.Kernel">
          <param connectionStringName="$(id)">
          </param>
          <param desc="databaseName">$(id)</param>
        </NotificationProvider>
        <cacheSizes hint="setting">
          <data>100MB</data>
          <items>50MB</items>
          <paths>2500KB</paths>
          <itempaths>50MB</itempaths>
          <standardValues>2500KB</standardValues>
        </cacheSizes>
      </database>
    </databases>
  </sitecore>
</configuration>

Nothing strange here, just a copy of the master database definition with a new icon and another name.

Next add a new connection string in connectionstrings.config.

<add name="asset" connectionString="user id=user;password=password;Data Source=(Server);Database=Sitecore.Asset" />

Again, nothing strange. If it was done correctly a new database should be present in the database selector.

DatabaseSelector

Making the asset content show up in the master-context

In order to make the asset content show up in the content tree when viewing the master database you also need to do two things. At a glance one would think that it would do the trick to create an ItemProvider that adds the items you want to use. It does however turn out that Sitecore does not always go through the item provider to provide items (go figure). So you also need to create a GetItemCommand.

First things first, lets create the ItemProvider.

public class ItemProvider : Sitecore.Data.Managers.ItemProvider
  {

    protected override ItemList GetChildren(Item item, ChildListOptions options)
    {
      ItemList items;

      if (item.Paths.FullPath.Equals("/sitecore/media library", StringComparison.CurrentCultureIgnoreCase)
        && !item.Database.Name.Equals("asset", StringComparison.InvariantCultureIgnoreCase))
      {
        Assert.ArgumentNotNull((object)item, "item");
        items = base.GetChildren(item, options);
        items.Add(Sitecore.Data.Database.GetDatabase("asset").GetItem("/sitecore/media library/MyItemsFromAssetDatabase"));

        if ((options & ChildListOptions.SkipSorting) == ChildListOptions.None)
          this.Sort(items, item);

        return items;
      }

      if (item.Paths.FullPath.StartsWith("/sitecore/media library/MyItemsFromAssetDatabase", StringComparison.InvariantCultureIgnoreCase)
        && !item.Database.Name.Equals("asset", StringComparison.InvariantCultureIgnoreCase))
      {
        Assert.ArgumentNotNull((object)item, "item");
        items = this.GetDataEngine(Sitecore.Data.Database.GetDatabase("asset")).GetChildren(item) ?? new ItemList();

        if ((options & ChildListOptions.SkipSorting) == ChildListOptions.None)
          this.Sort(items, item);

        return items;
      }

      return base.GetChildren(item, options);
    }

  }

The only method that need to be overridden is the GetChildren, and in that method we want to add the root item for the content from the asset database. This is a very simple implementation that uses paths to keep it less confusing, it of course needs some work to be production worthy.

But basically what happens here is that if the parent item is the media library then we add the item “MyItemsFromAssetDatabase” from the asset database. This item needs to be present in the asset database, and under it all the assets that you want to have access to in the master database. If the parent item is something below the “MyItemsFromAssetDatabase” we will also return children from the asset database.

In order for the new functionality to kick in the following include file is needed. It will substitute the standard ItemProvider with our new one.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <itemManager>
      <providers>
        <add patch:instead="*[@name='default']" name="default" type="MyNamespace.Managers.ItemProvider, MyNamespace"/>
      </providers>
    </itemManager>
  </sitecore>
</configuration>

So now onto the GetItemCommand. In the DataEngine.

  public class GetItemCommand : Sitecore.Data.Engines.DataCommands.GetItemCommand
  {
    protected override Sitecore.Data.Engines.DataCommands.GetItemCommand CreateInstance()
    {
      return new GetItemCommand();
    }

    protected override Item DoExecute()
    {
      var item = Nexus.DataApi.GetItem(this.ItemId, this.Language, this.Version, this.Database);

      if (item == null)
        item = Nexus.DataApi.GetItem(this.ItemId, this.Language, this.Version, Sitecore.Data.Database.GetDatabase("asset"));

      return item;
    }

  }

In the CerateInstance method we need to return our new GetItemCommand instead of Sitecores version of it and in the GetItemCommand we need to trick Sitecore inte getting the assets we need from the asset database. This is a really simple implementation and it needs some sort of more elegant solution to find out if it should get the item from the asset database or not. Here we just assume that if the item is null (not found in the context database) it should be gotten from the asset database.

Add the new functionality to the web and the master databases via an include file.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <databases>
      <database id="master" singleInstance="true" type="Sitecore.Data.Database, Sitecore.Kernel">
        <Engines.DataEngine.Commands.GetItemPrototype>
          <obj type="MyNamespace.Commands.GetItemCommand, MyNamespace" />
        </Engines.DataEngine.Commands.GetItemPrototype>
      </database>
      <database id="web" singleInstance="true" type="Sitecore.Data.Database, Sitecore.Kernel">
        <Engines.DataEngine.Commands.GetItemPrototype>
          <obj type="MyNamespace.Commands.GetItemCommand, MyNamespace" />
        </Engines.DataEngine.Commands.GetItemPrototype>
      </database>
    </databases>
  </sitecore>
</configuration>

And that is about it. If you browse the content tree in the master database the item from the asset database should now show up along with its children.

Buckets, indexing and preventing publish

In order to be able to have millions and millions of items in a bucket and still be able to use Lucene indexes in a practical manner some tweaking and custom indexes are needed. In order for the items not to be published some more tweaking is needed, but more about that in later blog posts.

Disclaimer

This has just been tested in development environment but it seems promising. It is a work in progress. I will update this post as I go further.

One thought on “Adding a second master database

Leave a Reply

Your email address will not be published. Required fields are marked *