Month: May 2018

Reading Time: 3 minutes

In this article, we will look at list and entity sharding that has been introduced in Sitecore Experience Commerce 9 (XC9), when we would create a custom shard, and how we can implement our own custom shard.

Sharding – What is it?

Let’s take a quick step back and review the Shared Environment database from Sitecore Commerce 8.2.1. All commerce entities and lists were found under tables named CommerceEntities and CommerceLists respectively. These tables consisted of data pertaining to carts, orders, pricing and promotions.

With the introduction of XC9, additional entities and lists have been added to cater for the catalog and customer data, as part of the final phase out process of the Commerce Server.

Now it’s a known fact that with any database that continues to accumulate data that inevitably there will be increasing performance degradation. This isn’t the end of the world as databases can be tuned for performance, but there is a simple and logical way of managing data for optimal performance. This is where sharding comes into play.

Sharding, in XC9, is the logical partitioning of entities and lists into separate tables, to isolate and manage data growth for improved performance and data maintenance.

Sharding is managed via the ListShardingPolicy and EntityShardingPolicy policies in the Plugin.SQL.Sharding.PolicySet-x.y.z.json.
Each policy specifies the properties, RegularExpression and (database) TableName, which the Commerce Engine utilises to identify where the entity/list should be read from and stored to. When a list/entity is not matched to one of these policies regular expressions, it defaults to the CommerceEntities/CommerceLists tables.

When is Sharding  Required?

Just because a new entity is introduced in your project, this doesn’t automatically mean that a new shard has to be implemented. Instead consider how this new entity will be utilised over time. If there is an expectation that over time this data will grow considerably where it should be housed separately, then sharding would be a good idea. However, if the data stored for this new entity is expected to be kept to a low volume, leaving the data in the default tables would be fine.

What is the Purpose of Sharding in the Commerce Global Database?

Simple answer. There is no purpose at this time. The script to create the databases was reusable and created the same structure. Only the CommerceEntities and CommerceLists tables are utilised for the Global database and I suspect this may be cleaned up in a future release.

How to Implement Custom Sharding

Creating Policies in the Commerce Engine

The following code snippet is a sample of a shard I am creating for a side-project for fulfillments.

{
    "$type": "Sitecore.Commerce.Plugin.SQL.ListShardingPolicy, Sitecore.Commerce.Plugin.SQL",
    "RegularExpression": "^List-Fulfillment.*?$",
    "TableName": "FulfillmentsLists"
},
{
    "$type": "Sitecore.Commerce.Plugin.SQL.ListShardingPolicy, Sitecore.Commerce.Plugin.SQL",
    "RegularExpression": "^List-DeletedFulfillmentsIndex-.*?$",
    "TableName": "FulfillmentsLists"
},
{
    "$type": "Sitecore.Commerce.Plugin.SQL.EntityShardingPolicy, Sitecore.Commerce.Plugin.SQL",
    "RegularExpression": "Entity-Fulfillment.*?$",
    "TableName": "FulfillmentsEntities"
},
{
    "$type": "Sitecore.Commerce.Plugin.SQL.EntityShardingPolicy, Sitecore.Commerce.Plugin.SQL",
    "RegularExpression": "Fulfillment-.*?$",
    "TableName": "FulfillmentsEntities"
}

Important note: Where sharding will be altered for a project, it is best to identify and implement it prior to development to avoid having to migrate data from its existing database table.

Creating a Database Table Shard

Database tables are based on either the entity table, or the list table definition. To create a new table shard, follow the process below for both table types.

In SQL Server Management Studio:-

1. Right-clicking an existing table
2. Selecting Script Table as > CREATE To > New Query Editor Window
3. Updating the table name as appropriate, e.g.  [dbo].[<MyEntityType>Entities]
4. For entity tables, update the constraint name as well, e.g. [PK_<MyEntityType>Entities]
5. Execute the script