Sitecore Commerce to Order Cloud Data migration – Article 2 – Users migration

I hope you must have read the first article, Sitecore Commerce to Order Cloud Data migration – Article 1, in which I described about the start of my journey for Sitecore Commerce to Order Cloud data migration. If you have not read yet, I would recommend you should read that article before moving ahead in this article.

According to the client requirements, we have to export the users based on their last login date. There were total more than 3 million users in the system and we had to export nearly 1.5 million users from past 12 to 13 months.

To filters the users based on their last login date, I use the dbo.aspnet_Membership table along withdbo.aspnet_Users table from the Core database. Sitecore 9.3 uses the ASP.NET Membership authentication to save the user details and activities.

There is an out of the box store procedure, dbo.aspnet_Membership_GetAllUsers, available in the Core database to get paginated results of the users data based on the given application name.

The client’s Sitecore Commerce has two websites with allowing users to register and login individually for each site. So, I’ve to filter the users first based on the user domain for each website. The given out of the box stored procedure is not fulfilling my requirements and modifying the existing store procedure will disrupt the website functional part. So, I created a new stored procedure by referring the existing one to filter the users based on the domain (you can say user name prefix), start date and end date to limit the users dataset based last login date, and of course the page index and page size to make sure we do not overload the server by fetching all the users at a time.

The following is the stored procedure which I used for users data export work.

I also created another store procedure to get total count of the users based on the filters which are used for actual data set to be queries. This store procedure was used first to get total number of users and counting the total pages to export.

The stored procedure which queries the user dataset also includes the user profile data from the dbo.aspnet_Profile table.

Sitecore and Sitecore Commerce use the dbo.aspnet_Profile table to store the user’s profile data. If you create a custom user profile in the Sitecore user management, that custom profile data is stored in the binary serialized format in the dbo.aspnet_Profile table.

The dbo.aspnet_Profile table has the following three key columns which store most required values related to users.

1. PropertyNames
2. PropertyValuesString
3. PropertyValuesBinary

The following image represents the data stored in the dbo.aspnet_Profile table.

While developing the application to read the profile data, the challenge for me was to de-serialize the binary data store by Sitecore.

For that purpose, I had to refactor the Sitecore dll files and at the end of some findings, I was able to extract, de-serialize and read the binary column data.

The following code sample show the method to de-serialize the ProeprtyValuesBinary column data.

The following sample JSON shows the de-serialized binary values from the user profile table for one of the customers. The JSON is the custom one which I used to store the de-serialized data.

According to Microsoft, BinaryFormatter type is dangerous and is not recommended for data processing. For more information read the article https://learn.microsoft.com/en-us/dotnet/standard/serialization/binaryformatter-security-guide.

However, Sitecore 9.3 is already using the BinaryFormatter to serialize and store the user profile data you must use the same BinaryFormatter to de-serialize the data. You cannot use any other formatter to de-serialize the data which are serialized using the BinaryFormatter.

Microsoft Visual Studio has already disabled the use of BinaryFormatter and it shows the compilation error if you use the BinaryFormatter.

However, if you want to use the BinaryFormatter, as I did, for the purpose of the reading the serialized data, you can direct the Visual Studio not to show compiler error by adding the following line in your .csproj file.

<EnableUnsafeBinaryFormatterSerialization>true</EnableUnsafeBinaryFormatterSerialization>

If you see the de-serialized data JSON, you could realize that the Sitecore Customer ID is available in the ExternalId attribute. This value is very helpful to query the Customer entity from the Sitecore Commerce database.

You can also see some additional attributes related to loyalty program, delivery instructions and pick instructions which are used by Sitecore Commerce to save user specific information.

In the next article, we will see how I fetched the Sitecore Commerce entity based on the data extracted from ASP.NET Membership table.

I hope this article will help you understand how the authentication in Sitecore works.

Jatin Prajapati's Blog

Some little contribution to Sitecore community