Sitecore – Resharding your XDB databases

Sitecore is great in collecting data; an insane amount of data. The XDB data that Sitecore collects is stored within the XDB Shard databases. These databases contain everything about contacts, their interactions and their facets.

When you have Sitecore running for a while, you’ll see these databases grow. Out of the box there’s nothing that removes data from these databases, so they will grow uncontrolled by default. If you don’t apply a proper retention strategy, these databases will grow so large that they may introduce performance problems.

If these problems arise, you have to either clean them up, scale up if that’s still an option, or add more shards. When adding more shards you “split” the data that is stored over multiple databases, so you have multiple databases with less data instead of fewer databases with more data.

When resharding your databases, there can’t be any activity on these databases. This means that you have to disable XConnect while the resharding process takes place. During the downtime of XConnect, functionality like EXM and Marketing Automation won’t work. Interaction data from the CD is temporarily saved in a local submit queue and will be sent to XConnect as soon as it’s available again.

Tools to reshard your database

If you want to reshard your databases, there are a few ways to do this. From Sitecore 9.3 and upwards you can use the default Sitecore tool to reshard your databases, but for older versions this is not possible.

Luckily for us there’s a tool made by the community called the XDB Resharding Tool that can help us with that. If you’re using this tool, make sure you check out my pull request which introduces multithreading.

Steps to reshard your database

The following steps need to be taken to guarantee a successful reshard of your databases:

  • Create new shard databases using the SQL Sharding deployment tool.
  • Stop your XConnect services so that nothing is using your XDB databases.
    This means everything related to XConnect including the IndexWorker.
  • Run the Resharding tool of your liking to reshard your databases.
  • Rebuild your XDB indexes*
    *see chapter “Rebuilding the XDB index”
  • Start your XConnect services.

Steps to decrease the duration required by the reshard operation.

  • Scale your shard databases (old and new) to BC_Gen5_8 instances to get the most IOPS for a decent price.
    Do not try this when having the databases in an Elastic Pool, or while using the DTU model. The LOG IO rate will slow down the process.
  • When using the community resharding tool, make sure to use the multithreaded one.
  • When using the community resharding tool, run it on an VM in the same region and make sure it has enhanced networking to improve throughput.
  • When using the community resharding tool, make sure that the VM has enough memory depending on your batch size, as it is temporarily stored in memory.
    I advise to use at least a VM with 16GB memory.
  • Clean up any data that you do not longer need in the XDB indexes, as anything that you don’t have to copy will save you time.

Rebuilding the XDB index

In a previous post I’ve explained how the XConnect Search Indexer works. Long story short, the indexer uses the XDB Sync Token to determine which changes are new, so it knows that it has to index those changes. The XDB Sync Token contains a record for each shard database, where it stores the connectionstring to the shard, and the change version of that shard. As we’re adding more shards, the XDB Sync Token is invalid and cannot be used anymore, leading to a required re-index.

However, if you did not change any data in your shard databases that is contained in the index, during or before the resharding process, the data in the index is still up to date and only the XDB sync token is invalid. Luckily we can easily create a new XDB Sync token without re-indexing, which will save a lot of time.

I’ve created a console application which generates a new XDB Sync Token based on your shard databases. Just run the program, paste the connectionstring to your (new) Shard Map Manager database (the same connectionstring which you would use in Sitecore). After that, press enter, and your new XDB Sync token will be shown. If you’d like to update the token directly in Solr, follow the steps shown in the application.

My Experiences

I’ve had a run with a total of 130 GB contained in two shard databases, which finished in 49 minutes. By not having to rebuild the XDB index, this was also the total downtime for XConnect and services depending on XConnect like EXM and Marketing Automation. Please note that resharding is not an excuse to leave Sitecore collecting an indefinite amount of data, a proper data retention policy is still required!.

Leave a Reply

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