In this blog post, we will dig into one of the best features of the Sitecore Experience Commerce. Before starting the actual configuration first let’s first talk about what Sharding exactly is?
In Sitecore Experience Commerce we have Shared_Enviornment database which contains two schemas knows as CommerceEntities and CommerceLists respectively. These schema used to store the data from Carts, Order, Catalog and Customer, etc. In the Sitecore Experience Commerce, we have few of the tables where all related entities will store. But sometimes the data becomes too huge that the performance of the query starts degrading. We can tune the database setup the maintenance plan for optimizing the SQL Server performance but still there is another simple way in the current release of the Sitecore commerce.
Sharding is a technique to logically separate out the entities and lists into the different table. All the sharding related logic is maintained by the PlugIn.SQL.Sharding.PolicySet-1.0.0.json. Each of the policy contains the following properties:
Based on the RegularExperssion value CommerceEngine decide where should be the specific entities read form and stored to. If you don’t have any policy for some entities by default they will be part of the CommerceEntites and CommerceLists table.
When we can use Sharding?
Sharding is used whenever we have the new entity in the commerce engine to maintain the logical separation from the other entities. But in some cases when data grow significantly for the existing entities we can use the sharding to fine tune the performance a bit. By default when you create the new entities table for the existing entities you will lose the old data. But there are few ways around this as well but it’s always a good idea to give sharding a look in the development phase only so that risk for losing the data will not be there in the future. Now, Let’s jump into the implementation part.
Objective: To Separate the Customer Entities from the Default CommerceEntities Table.
Steps to Perform for Configuration:
- Create a table based on the Commerce Entities Table schema.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[CustomerEntities](
[Id] [nvarchar](150) NOT NULL,
[EnvironmentId] [uniqueidentifier] NOT NULL,
[Version] [int] NULL,
[Entity] [nvarchar](max) NOT NULL,
[EntityVersion] [int] NOT NULL,
[Published] [bit] NOT NULL,
CONSTRAINT [PK_CustomerEntities] PRIMARY KEY NONCLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- Create Policy in the PlugIn.SQL.Sharding.PolicySet-1.0.0.json for the Custom Entities with the following Policy:
“$type”: “Sitecore.Commerce.Plugin.SQL.EntityShardingPolicy, Sitecore.Commerce.Plugin. SQL”,
- Bootstrap the New Policy from Postman.
- Now when you register the New User it will be inserted into the new table.
- All the Previous customer logins are not working correctly and throwing exceptions.
- All the saved address with the customer name is not present in the system.
- All the previously created cart by user are also got deleted.
- Bizfx Customer Dashboard not showing any customer.
We have lost all the Old data associated with the Customer Entity.
How to Resolve:
When we map the CustomerEntities to the New Table it the SQL.Sharding policy. Now on each rewuest from CommerceEngine, it will check for the Old entities in the New Table only. To resolve this we have to move all the Entities related to the customers. So, now let’s dig into some simple SQL Query. By Default all the Customer Entities have Id started with “Entity-Customer-“. To move all the data having the Id started with the Entity-Customer use the following command:
INSERT INTO [dbo].[CustomerEntities]
SELECT * FROM [dbo].[CommerceEntities] where Id like ‘Entity-Customer-%’
Now all the logins were working fine and you will be able to see the addresses associated with the Customer as well. But still, the old carts was not there. After some, investigation I’ve found that all the Cart Entities Id associated with the Customer was starting with DefaultEntity-Customer-[AccountNumber][ShopName]. To move this data along with the Customer Entity I’ve tweaked the above query little bit like the below:
INSERT INTO [dbo].[CustomerEntities]
SELECT * FROM [dbo].[CommerceEntities] where Id like ‘%Entity-Customer-%’
After all these changes and transferring the Old data to a new table, we have deleted the old data from the Customer Entities table. Now you will be able to see all the data related to cart and customer in the storefront as well as in the newly created table.