Sitecore Commerce is an interesting landscape — it’s never a dull moment. After recently swapping the backing store from Azure SQL to SQL Server (due to an interesting Inventory gotcha with the Reference Storefront that I’ll maybe share at some other time), I’m finding nooks and crannies of configuration I never knew existed with the Commerce product until now.
After I migrated to Azure IaaS SQL Server VMs from Azure SQL, I thought I had everything tidied up.
- Commerce Server Manager references? ✔
- Sitecore application connection strings? ✔
- Bootstrap configuration (I posted this gist on manipulating those files to make this easier)? ✔
I updated the Azure SQL database credentials to prove that I had no lingering connections to Azure SQL. I encountered an exception at Sitecore start-up related to initialization of the profile service, however, and had to start digging. CommerceProfileSystemException was the exception type and the stacktrace started as follows:
Exception type: CommerceProfileSystemException Exception message: Failed to initialize profile service handle. at CommerceServer.Core.Runtime.Profiles.ProfileContext..ctor(String profileServiceConnectionString, String providerConnectionString, String bdaoConnectionString, DebugContext debugContext) at CommerceServer.Core.Runtime.CommerceContextFactory.CreateProfileContext() at CommerceServer.Core.Runtime.CommerceContextFactory.get_ProfileContextSingleton() at CommerceServer.Core.Runtime.Profiles.CommerceProfileModule.get_ModuleProfileContext() at CommerceServer.Core.Runtime.Profiles.CommerceProfileModule.get_ProfileContext()
The Commerce Server Manager encapsulates the connection strings, and I thought I had them all updated to the SQL Server VM equivalents, even going so far as to inspect MSCS_Admin in SQL Server with a query like this:
SELECT [i_ResourceID] ,[s_PropertyName] ,[s_Value] FROM [MSCS_Admin].[dbo].[ResourceProps] where f_IsConnStr=1 ORDER BY 1
While interesting to find where this information is stored (may or may not be permanent, though — tough to tell with Commerce!), this output didn’t shed light on what might be going on, though:
Eventually I stumbled across some 13 year old documentation on Commerce Server discussing updating the ProfileService data source in some detail (http://microsoft.public.commerceserver.general.narkive.com/NPLMLusv/commerce-2002sp3-on-windows-2003-can-t-change-profiles-data-source). It turns out, this 13 year old solution was completely applicable to my 2017 Sitecore Commerce predicament.
Succinctly, within Commerce Server manager you should do the following:
- Expand the Commerce Server “Global Resources” node, then “Profiles” node, then “Profile Catalog” node, then “Data Sources” node, and finally expand the “ProfileService_SQLSource” node
- Click on the Partitions node:
- In the right pane, there’s a SQLSource element you right-click and choose “Properties”
- Select the Partitions Tab, then “Edit” the connection string
- Make your connection string modifications here. This is where my elusive reference to Azure SQL was hiding and causing Sitecore to fail to initialize.
The more work I do with Sitecore Commerce, the more I’m appreciating the value of the older documentation targeting previous editions of the product. The catch is, it’s not 100% relevant to the modern experience with Sitecore Commerce . . . and knowing what is and isn’t applicable to the Sitecore Commerce 8.2.1 world is a challenge. I think we’re getting there, a little bit at a time!
“After recently swapping the backing store from Azure SQL to SQL Server (due to an interesting Inventory gotcha with the Reference Storefront that I’ll maybe share at some other time)”
Please share ..! Would be interested to know what that was.
LikeLiked by 1 person
Yeah thanks for the reminder, let me get my notes together on it and share it on the blog. The crux of the issue was around inventory updates after purchase actions on the CD servers . . . Sitecore Commerce (relying on some old MS Commerce underpinnings) would try a distributed SQL call to update inventory and Azure SQL (at the time of this project — Fall 2017) had no support for the cross database query. Tough roadblock to hit late in a project, but moving to SQL Server IaaS was worth the hassle in this case.
LikeLiked by 1 person