Sitecore Commerce 8.2.1 Firewalls & Azure SQL Notes

I know Sitecore Commerce 9 is just around the corner, but I know many projects are under way with the current Sitecore Commerce 8.2.1 and I wanted to share some notes we’ve collected on how to work with the technology.

This is from lessons our team at Rackspace has collected, including some exceptionally talented “sales weasels” who have really dug into the Commerce platform with us to determine perf benchmarcks etc — score 1 for the talented sales weasels ξ(^◒^)ξ !

These two links are the key pillars of online documentation for getting started with the product:

http://commercesdn.sitecore.net/SitecoreCommerce/DeploymentGuide/en-us/index.html

http://commercesdn.sitecore.net/SitecoreCommerce/DevOpsGuide/en-us/index.html

As a complement to the above, I’d like to share the following tips we’ve learned on a few Sitecore Commerce 8.2.1 projects . . .

Firewall Settings

We’ve run into issues with both the Sitecore Commerce Staging software and the Profile system; the exceptions are logged to the Windows Event log or the Sitecore logs and can report an ugly COM error such as the following . . .

Don’t panic

. . . it turns out that the current documentation doesn’t mention Firewall rules for these two services to operate.  After some investigation we realized we needed to open up ports.  Prior versions of Sitecore Commerce have this article, http://commercesdn.sitecore.net/SCpbCS82/SitecoreCommerceDeploymentGuide/en-us/FirewallPorts.html, and it turns out port ranges 5000-5030 and 507 can be important to proper communications in a distributed Sitecore Comemrce build.

Azure SQL

Everyone is price conscious and Azure SQL can save customers a lot of money over conventional SQL Server.  Documentation online for 8.2 states Azure SQL is supported, but the installation documentation doesn’t treat it as a first-class citizen.  There is this great link, http://commercesdn.sitecore.net/SCpbCS81/SitecoreCommerceDeploymentGuide/en-us/c_UsingAzureSQLwithCS.html, with notes on Azure SQL specific provisioning for Sitecore Commerce — but it’s organized under a PaaS installation topic which hides it from our IaaS Sitecore Commerce eyes.

Generally speaking, the Initialize-CSSite script modifies the SQL database schemas and does not work if you use Azure SQL.  The authentication assumptions are invalid (using an App Pool user isn’t going to fly with Azure SQL).

This can be worked around (mostly), but we learned the hard way that the Sitecore Commerce Reference Storefront application is not compatible with Azure SQL in the reliance on Cross-Database queries for certain key operations; in our case, trying to use a central Commerce Inventory Azure SQL database was ultimately untenable.  Azure SQL does offer Cross-Database queries, but the syntax is different and the Reference Storefront code has non-trivial dependencies on the IaaS SQL Server understanding of querying between databases.

There are a lot of nooks and crannies with Sitecore Commerce 8.2.1.  I know everyone is excited about a fresh start with Sitecore Commerce 9 that doesn’t include the legacy COM and thick-client dependencies; this write-up summarizes a few of the Commerce highlights on the horizon.  For now, however, there are Commerce 8.2.1 builds in the wild that require our attention and I hope the above notes help others to be successful.

How a 13 year old archived list serv helped me out with Sitecore Commerce

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:

1. 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
2. Click on the Partitions node:
   • 
3. In the right pane, there’s a SQLSource element you right-click and choose “Properties”
4. Select the Partitions Tab, then “Edit” the connection string
5. 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!