The case of the Inventory gotcha with Azure SQL and Sitecore Commerce 8.2.1

I only recently discovered comments to this blog were going into an unmonitored spam folder — my apologies if anybody had their hearts set on a response to their comment!  In scanning some of those comments, I thought a proper response was warranted to one note on a piece I wrote about a hard to find Sitecore Commerce configuration setting.

I started that post by writing:

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.

This post discusses the interesting Inventory gotcha with the Sitecore Commerce storefront implementation. I should point out this is relevant to Sitecore Commerce 8 and I’ve not tested this specifically with Sitecore Commerce 9, but I assume it’s been addressed in version 9 by either not using the same Reference Storefront for Commerce (in version 9 you’d be steered to an SXA UI instead of the older “Reference Storefront”) or by the general platform improvements in Sitecore Commerce 9.  I guess you can stay tuned for Sitecore Commerce 9 news on this front!

The Problem Scenario

This is IaaS VMs running Sitecore 8 (conventional IIS, not PaaS) and using the Sitecore Commerce 8.2.1 release in a scaled Sitecore environment. Azure SQL was chosen as the database technology for a variety of reasons I need not go into. This implementation used separate Commerce catalogs for CM servers and CD servers, to provide the “publish workflow” behavior Sitecore uses for marketing content; this is accomplished for Commerce via the Staging Service.

One tricky area when using multiple Commerce catalogs is Inventory. A late-breaking customer requirement was the need for inventory updates to flow real-time between content management and content delivery servers, so nobody sees stale inventory data. Don’t you love those late-breaking customer requirements?

This can be handled in different ways, but the method appropriate to this project was to use a single Inventory database that both Sitecore CM and CD environments would reference for inventory data.  Commerce Server Manager presents this in the UI as the “Inventory” resource under the Commerce site:

It’s smart to have separate databases for product catalog and inventory, and we set this solution up to have multiple catalogs (one for CM and one for CD) with a shared Inventory database so that data was always realtime.

The crux of the problem was the mechanism Sitecore’s Reference Storefront uses to coordinate Inventory queries between the two Commerce catalog databases — it’s called Cross Database Query and it’s not supported the same way in Azure SQL as it is in conventional SQL Server.  You can do cross database queries with Azure SQL, just not the way the Reference Storefront for Sitecore Commerce depends on it.

The exception we would see was as follows:

SqlException (0x80131904): Reference to database and/or server name in ‘CFSolutionStorefrontSite_productcatalog.dbo.Habitat_Inventory_InventorySkus’ is not supported in this version of SQL Server.

The above was followed by a fair amount of StackTrace such as:

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3189408
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +753
 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +5042
 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +87
 System.Data.SqlClient.SqlDataReader.get_MetaData() +101
 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) +590

You get the idea.

What We Considered

After considering our Sitecore Commerce configuration and confirming it was solid, the exception next had us examining Azure SQL compatibility settings, but that wasn’t the issue.

We determined we could do an import/export of Inventory data.  Maybe the Sitecore publish:end event could push inventory changes to the CD servers, where they store inventory in an XML file and on publish:end:remote we could import that XML data into the Content Delivery servers.

We could remove publishing from the equation and use a scheduled <agent> to move Inventory data around.

What We Actually Did

The import/export options were never going to be truly realtime, at least not sufficiently “real” for our project and their inventory requirements. So we swapped out the database back-end and used SQL Server in IaaS VMs instead. Cross database query worked as expected, and we could use a central Inventory database with multiple Commerce Catalog databases for CM and CD sites. It worked as it should.

This concludes the tale of the interesting Inventory gotcha around Sitecore Commerce 8.2.1 — this should close the loop with the initial post from a few months ago, I hope this helps out somebody else!