Tuesday, October 15, 2019

Sitecore xDB Resharding Tool - Unlock xDB Storage and Performance Limitations by Increasing Collection Shards Without Data Loss



There is currently no way for you to increase the number of xDB collection database shards for an existing deployment, without starting from scratch and losing all your data.

The release of my colleague Vitaly Taleyko's Sitecore xDB Resharding Tool solves this problem for all of us, as it provides us with a migration path from an old shard collection to a new one.


The inability to increase shards after deployment is a major problem for enterprise customers using the platform, who may not be aware of how quickly the collection databases will grow over time.

If you are a Sitecore veteran, you have experienced this rapid collection growth in MongoDB. As soon as the platform is "turned on", it starts collecting interactions and events for both anonymous and identified contacts.

Putting a Sitecore environment into Production, means opening up the flood gates for a massive amount of data that you don't have much control over.

xDB Search Index

On the xDB index side of the house, Sitecore has filtered the interactions and contact data in the xDB index to identified contacts only (by default). This is good for simple customers who aren't doing much with the platform.

However, if you have millions of contacts, you will have the same index problems as you may have faced when dealing with anonymous contact data in previous versions mentioned in this blog post from a while ago. There is a solve for this, that I may touch on in a later post.

The 2 Shard SQL Problem

Out of the box, if you install the Sitecore platform using default scripts and SIF or if Sitecore Managed Cloud has set up your environment in Azure, you will have 2 SQL shard collection databases.

The value proposition of Sitecore xDB is the ability to store any events tied to an interaction from any channel in xDB. They have provided a robust set of APIs to allow this.

The problem is that storing hundreds of gigabytes or even terabytes of data requires a very carefully planned strategy, or else, you will fail. It is just a matter of when.

I always have the following scene from Evan Almighty in my head when I talk about this problem:

The bottom line - if you have an enterprise deployment, and are using xDB, the out of the box 2 shard collection database configuration is not enough!

New Deployments

If you are new to the Sitecore platform, you can fix this by using the Shard Map Manager Tool to increase the number of shards. This great post by Kelly Rusk explains how: http://thebitsthatbyte.com/what-is-and-how-to-use-the-sitecore-9-shard-map-manager-tool

Existing / Live Deployments

Bad news if you have an existing deployment.

You will hit bottlenecks as you store more contact, interaction and event data. With limited CPU, storage capacity and memory, database performance will start to suffer and query performance and routine maintenance will slow down.

When it comes to adding resources to support database operations, vertical scaling (aka scaling up which is very easy to do on Azure) has its own set of limits and eventually reaches a point of diminishing returns.

The Negative Ripple Effect on xDB

I have seen cases where due to the massive amount of data stored in the xDB collection shards over time, xConnect Search will fail to keep the xDB index in sync, and xConnect search will stop working.

After this happens, the only option is to rebuild your xDB index, but because of the poor xDB collection database performance, it will take days, if not weeks if you are lucky.

Or, it will simply keep failing.

How Increasing Shards Helps

Adding additional collection shards to xDB means additional SQL compute capacity to serve incoming queries in the distributed configuration, and thus faster query response times and index builds.

Additional shards will increase total cluster storage capacity, speed up processing, and offer higher availability at a much lower cost than vertical scaling.

How the xDB Resharding Tool Helps

As I started working with Vitaly to architect this tool, our first idea was to use the Data Exchange Framework that is used to power the xDB Migration Tool. We had used a customized version of this tool when we migrated from our Sitecore 8.2 deployment to our current 9.1.

We decided to pivot, because we wanted a lightweight tool that could run on any Windows-based machine, and would run directly against SQL and as a result, be much more efficient!

The Beauty of the Tool Part 1: Migrating Your Data

This tool allows you to reshard your Sitecore xDB Collection Databases without any data loss.

What does this mean exactly?!

This tool allows you to migrate your current SQL xDB analytics collection database shards to a new set of xDB analytics collection database shards without losing any of your data.

So, for example if you have 2 shard databases, and want to move up to 4 shard databases, this tool will allow you to migrate over.

For this example, you would set up 4 new shards using SIF (as shown in Vitaly's GitHub readme doc), or use the Shard Map Manager Tool, and then point the tool at your old shards and the new shards and voila! Your data will get migrated over!

The Beauty of the Tool Part 2: Resume Mode

Another fantastic feature that Vitaly added to this tool is "resume mode". If there is a glitch in the migration process, or you need to stop it manually for some reason and resume later, it will remember where it left off and pick the migration right back up!

Battle-Tested and Ready For Download!

This tool has been tested, and I can say that it works, and works well!

You can check the full source code out on Vitaly's GitHub: https://github.com/pblrok/Sitecore.XDB.ReshardingTool

You can download your copy today using this link: https://github.com/pblrok/Sitecore.XDB.ReshardingTool/raw/master/ToolReleases/win-x64.zip


Post a Comment