Sitecore Commerce 8.2.1 MSCS_Admin Database & the Tyranny of SQLOLEDB.1

Disclaimer: this is a journey of discovery and not a manual on database best practices for Sitecore Commerce 8.2.1.

You don’t see a lot of talk about disaster recovery and Sitecore Commerce — getting the regular implementation to work well is enough of a challenge that DR and Sitecore Commerce feels like a mythical Phase 5 of a project with only 4 Phases.

I want to write-up some notes and exploratory digging I did recently on the topic of disaster recover and Sitecore Commerce 8.2.1. You could even consider it a poor man’s option for database high availability that doesn’t incur the licensing costs of SQL Server Always On . . . but it’s probably best if you forget I ever mentioned that. Just because something can be done, doesn’t mean it should be done. I get in trouble sometimes presenting cans when I should just stick to the shoulds, but sometimes there’s real opportunity in those cans so I just can’t resist. Reader: beware.

Enough preamble. To simplify the scope of this post, I’m going to focus on database disaster recovery since it’s distinct for Sitecore Commerce 8 from “regular” Sitecore without Commerce. It’s because Commerce has a decade (or two!?) of COM code and legacy architectures that are way down deep in the Sitecore Commerce 8.2.1 system.

The crux of the challenge I was tasked with addressing was the inability to identify a SQL Mirror as part of a Sitecore Commerce 8.2.1 project. Many customers have used SQL Server database “Mirroring” as the high availability option for Sitecore databases for a long time because it was the only one officially supported by Sitecore. As this documentation explains, only since Sitecore 8.2 has “Always On” been an option for an officially supported Sitecore implementation. I know — many projects are successful with newer SQL Server approaches or RDS on AWS etc, but in my role at Rackspace, we have to walk the line of what Sitecore officially supports from top to bottom to ensure clean lines of escalation in the event of any issue; this is appealing to risk-averse customers, those with aggressive SLAs, etc.

To use SQL Server Mirroring one must identify a failover partner in each of your SQL Server database connections defined in ConnectionStrings.config like:

FailoverPartner

In Sitecore Commerce 8.2.1, however, this is the interface you have to manage a database connection for the MSCS_Admin database . . . there is a single endpoint (server name) and no provision for a failover partner. It comes down to a limit of the SQLOLEDB.1 provider, I believe. It’s fine for a SQL Server Availability Group listener where you get a service to route requests between the Always On SQL Server nodes, but this Commerce UI is incompatible with SQL Server Database Mirroring:

DRCommerce

I set to digging and found some old documentation on a Windows Registry key for Commerce Server 2007 edition and the MSCS_Admin database. I’ll assume you understand the primacy of the MSCS_Admin database for Sitecore Commerce 8.2.1 — if that’s not the case, you can review this material for background, but trust when I explain that MSCS_Admin is the administrative heart of Sitecore Commerce 8.2.1. This SQL query shows how the ResourceProps table in MSCS_Admin stores all the dependent database connections for Inventory, Catalog, Profiles, etc:

Now, the old documentation I found mentions an encrypted registry key named ADMINDBPS that is where Commerce Server Manager, the desktop tool, reads and writes the actual database connection string for the MSCS_Admin database. Since I can’t insert a DB Mirroring Failover Partner into the desktop tool, I figured I could engineer a work around using this registry key as leverage.

The problem, however, is this documentation I was reading was from 2006 and no longer reflected reality. It also mentioned how this approach wasn’t supported by Microsoft and came with every cautionary disclaimer. Sounds like fun, right? The Windows Registry Key schema had changed, but not the overall approach and after doing some digging I found HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\CommerceServer was where this newer version of Sitecore Commerce had reorganized the Registry state and that’s where ADMINDBPS was hiding!

admindbps

The connection string to MSCS_Admin was still encrypted . . . sure . . . but nothing the .Net System.Security.Cryptography namespace couldn’t resolve.

The connection string to MSCS_Admin was still buried in the Windows Registry . . . sure . . . but nothing the .Net Microsoft.Win32 namespace couldn’t resolve.

Here’s the code I used to read this value:

RegWork

Since we’re getting this value, we might as well just add the set logic too, right?

RegWork2

Yes, let the Registry edits flow through you!

I told you this was a reader: beware type of post.

Taking a step back, remember my original challenge was finding a way to weave support for SQL Server DB Mirroring into Sitecore Commerce 8.2.1 and I found the MSCS_Admin database connection to be my focus? We can wrap the above c# code into a command line tool that can be run in a database failover situation to update the MSCS_Admin database connection automatically — changing to the intended failover partner defined as part of the conventional SQL Server Database Mirroring. We could schedule this c# logic to run once every 30 seconds in Windows Task Scheduler, for example, conditionally changing the MSCS_Admin database connection in the event of a failover scenario. The MSCS_Admin database connection string could update automatically. I’m not going to present this as a viable alternative to SQL Server Always On for many many reasons, but you achieve some semblance of it with robust enough PowerShell.

If you’re scratching your head here because I work with customers who are risk averse or have critical Sitecore SLAs that would still rely on the above Registry hacks, you’re not quite getting my point. I’m sharing this because it’s possible, it’s interesting to know how the guts of Sitecore Commerce 8.2.1 are working, and others may also learn from this. While it’s theoretically an option to introduce some automated failover logic through this method, it’s an uncomfortable hack. Based on my testing, though, it works.

Honestly, this technique is most appropriate in a disaster recovery scenario where a set of database servers are unavailable (and then this can apply whether using SQL Server  Always On or DB Mirroring — doesn’t really matter). Instead of considering this a high availability solution, it’s a DR solution.

I spoke with some on the Sitecore Commerce technical team about this and they agreed this was a bit crazy, but it works (and also I shouldn’t quote them directly). They also pointed out how you don’t have to store MSCS_Admin connections in the Windows Registry and that as part of the PaaS support evolving through Sitecore Commerce there was a “Registry Free” deployment option for Sitecore Commerce 8.2.1 that I didn’t know about for this version. With this technique, you can use a ConnectionString defined like the others for Sitecore (see some details here)

<connectionStrings>
 <add name="ADMINDBPS" connectionString="<your MSCS_Admin connection string" />
</connectionStrings>

I haven’t experimented with the Registry Free deployment for Commerce 8.2.1 but I’d like to see if it avoids the tyranny of the SQLOLEDB.1 provider and would let us add the Failover Partner logic to a connection string. I think this blog post may have a Part 2 . . . but I’m not sure how much further down this rabbit hole it’s worth tunneling.

Advertisements

Sitecore Commerce 8.2.1 and ListManager with EXM

I’ve been engaged on a few more Sitecore Commerce builds (Commerce 8.2.1 still as these have carried over from 2017) and found an interesting wrinkle the other day. At first, it looked like a MongoDB issue as contacts weren’t being properly added to Sitecore ListManager “Lists” for use in EXM, but after scratching beneath the surface it was a lot more interesting. I used a utility sent my way by Sitecore support — it’s a .zip that has a Sitecore 8.2 update-5 specific tool for seeing Sitecore Lists and their status in terms of what’s in MongoDB and what’s in content search indexes (Solr in my case).

The tool made it pretty clear that the data was being stored properly in MongoDB but NOT in the search index (the screenshot below shows “Contacts in index: 3” which is after we corrected the problem — initially the Contacts in index would only ever show 0 and that’s what helped to isolate the problem to Sitecore Content Search):

lists

Another piece of evidence, in the Sitecore UI when we’d try to add a new contact to ListManager we’d see this message:

Please note that contacts in the list are currently being indexed, so not all contacts are available to view at this time. 0 out of 3 contacts are currently indexed.

Once we enabled verbose logging for search and examined the Search.log output, we see messages like this in the logs:

INFO  Solr Query - ?q=(type_t:(contact) AND contact.tags_sm:(ContactLists\:\{B76B0E74-E94D-4EBB-F219-6A347C75520D\}))&start=0&rows=20&fl=contact.contactid_s,contact.identifier_t,contact.firstname_t,contact.surname_t,contact.preferredemail_t,contactscount_tl,_uniqueid,_datasource&fq=_indexname:(sitecore_analytics_index)

I bolded the contact.tags_sm criteria in the query as that turned out to be key. This is the query that Sitecore issues to Solr when trying to obtain contacts for ListManager.

Through considerable trial and error, Solr schema inspection, and just determination (and I think Dana [https://twitter.com/thesoftwarejedi] was the one who finally yelled “bingo” and discovered this), when we run this query directly against Solr, we would find our missing ListManagement contact:s

http://solr-server:8983/solr/sitecore_analytics_index/select?q=(type_t:(contact) AND contact.tags_tm:(ContactLists\:\{B76B0E74-E94D-4EBB-F219-6A347C75520D\}))&start=0&rows=20&fl=contact.contactid_s,contact.identifier_t,contact.firstname_t,contact.surname_t,contact.preferredemail_t,contactscount_tl,_uniqueid,_datasource&fq=_indexname:(sitecore_analytics_index

The contact.tags_tm is bold above, and that was the crux of our challenge.

Sitecore was indexing contacts using tags_tm while Sitecore queries were looking for tags_sm.

In Sitecore config file CommerceServer\CommerceServer.ContentSearch.Solr.DefaultIndexConfiguration.config is a fragment of XML like the following:

<typeMatches hint="raw:AddTypeMatch">
 <typeMatch typeName="idCollection" type="System.Collections.Generic.List`1[[Sitecore.Data.ID, Sitecore.Kernel]]" fieldNameFormat="{0}_sm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
 <typeMatch typeName="textCollection" type="System.Collections.Generic.List`1[System.String]" fieldNameFormat="{0}_tm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />

The typeMatch for typeName=”textCollection” was the issue, along with how it duplicates the mapping for the System.Collections.Generic.List`1[System.String] type — and the many places that were using the textCollection returnType that depended on this typeMatch. I removed the typeMatch from the config file and updated any dependency on textCollection to use stringCollection instead and . . . magic . . . the contacts properly indexed into Solr and the contact.tags_sm criteria would match the new data.

According to Sitecore Support, this is a defect in the way Commerce search indexing is setup and it’s overlap with Sitecore ListManager (EXM in our case). Commerce should probably use a custom configuration section instead of modifying the default index configuration, but we’ll have to wait and see how this is implemented in a future patch or release.

For the time being, I’ve created the following Sitecore patch configuration file to remove the textCollection elements. This is preferable to editing the standard Sitecore configuration files that come with the product and will make for easier Sitecore upgrades or adjustments when (or if?) a true correction for this defect is released by Sitecore:

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
 <sitecore>
 <contentSearch>
 <indexConfigurations>
 <defaultSolrIndexConfiguration type="Sitecore.ContentSearch.SolrProvider.SolrIndexConfiguration, Sitecore.ContentSearch.SolrProvider">
 <fieldMap type="Sitecore.ContentSearch.SolrProvider.SolrFieldMap, Sitecore.ContentSearch.SolrProvider">
 <typeMatches hint="raw:AddTypeMatch">
 <typeMatch typeName="textCollection">
 <patch:delete />
 </typeMatch>
 </typeMatches>
 <fieldNames>
 <field fieldName="instocklocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="outofstocklocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="orderablelocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="commerceancestornames">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field> 
 </fieldNames>
 <fieldTypes hint="raw:AddFieldByFieldTypeName">
 <fieldType fieldTypeName="catalog selection control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="child categories list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="child products list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="parent categories list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="relationship list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="variant list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 </fieldTypes>
 </fieldMap>
 <documentOptions>
 <fields hint="raw:AddComputedIndexField">
 <field fieldName="instocklocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="outofstocklocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="orderablelocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="commerceancestornames">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 </fields>
 </documentOptions>
 </defaultSolrIndexConfiguration>
 </indexConfigurations>
 </contentSearch>
 </sitecore>
</configuration>

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:

commerceInventory

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!

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
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.

The Odyssey of Sitecore Commerce Staging’s Reliance on SQL CE 3.5

The Problem

I recently completed a fun journey triaging a set of self-inflicted wounds around Sitecore Commerce Staging.  The rest of Sitecore Commerce 8.2.1 ran as expected except for when I setup the Sitecore Commerce Staging between a CM and CD tier . . . I would get an odd “The system cannot find the file specified” dialog box immediately after trying to start a basic Staging replication project:

cannot find the File

And when I say that pop-up appears immediately after clicking the “Start Replication” button, I mean it — we’re talking almost instantaneously.

Background

I’ll pause here just to lay out why one cares about Commerce Server Staging with Sitecore.  The Commerce Staging documentation is on MSDN in a variety of places, and Sitecore is using this sub-system to manage Commerce specific content promotion (such as product catalog changes, promotion codes, etc).  As explained in the summary on CommerceSDN.Sitecore.net :

The role of Commerce Server Staging is to move Commerce Server data between environments or sites.

If the master and web environments are pointing to the same Commerce Server site instance, as soon as data is changed on the master environment, it will be published to web. To ensure that Commerce Server data is not published unexpectedly, it is strongly recommended that you have one Commerce Server site for your Content Management (CM) environment, a separate Commerce Server site for your Content Delivery (CD) environment, and then use Commerce Server [Staging] to move the data.

I added the [Staging] to the final sentence above, as I think it’s pretty key to understanding the topic.

Commerce Server Staging is the vehicle one uses to manage Commerce data the same way we manage marketing data in the rest of Sitecore.  We create and update Sitecore CMS content in a “master” SQL Server database and then promote it to “web” with a Sitecore Publish operation . . . then we create and update Sitecore commerce content via the Sitecore CM and promote changes to the live website via Sitecore Staging operations.  The Sitecore Publish process has been extended with Sitecore Commerce to include Staging, so you can run it as a single integrated process.  Note the “Commerce Server Staging” checkbox included in the Sitecore Publish dialog below:

StagingPublishDialog

It’s usually magic and just works — but I was not able to run any type of Commerce Staging operation, so I needed to peel back the curtain and learn a bit more about how to troubleshoot Commerce Staging.

The Solution

This write-up on Monitoring Commerce Staging laid the foundation for my eventual resolution.  Before exploring that angle, however, I reviewed this write-up with some basic pitfalls on Commerce Staging . . . of course there’s this gem about DCOM permissions (& my write-up on applying that setting when the GUID isn’t readily available) . . . Sitecore’s KB site has some suggestions and the Community.Sitecore.net has some suggestions. There are hundreds of nooks and crannies one can investigate here, so it took me some time to discover what was going on.  Hopefully, my adventure can inform others, so here goes . . .

  1. The link about monitoring Commerce Staging (https://msdn.microsoft.com/en-us/library/ms961837(v=cs.70).aspx) talks about ways to configure what is logged and how to view the events of the Staging process.  I digested this info and, while the write-up assumes some old IIS configurations, I discovered the C:\Program Files (x86)\Commerce Server 11\Staging directory has two folders of particular interest to me: Data and Events.  The Events folder has .mdb and .ldb files (yes, that’s Microsoft Access) that form a type of localized Event Log for Commerce Staging; the Data folder has a StagingLog.sdf file — .sdf is a format used by SQL Server Compact Edition
  2. I opened the Access database and found that it didn’t have any helpful information for my scenario.  My “cannot find the file specified” exception was not in evidence there . . . but there is some interesting meta-data captured in that Access MDB for Staging.  This Events folder appeared to be a dead-end.
  3. I used LinqPad to analyze the StagingLog.sdf file (turns out Commerce Staging uses SQL CE 3.5 edition), and I learned two key things:
    • This StagingLog.sdf file was essentially empty — no data was being written to it when I tried to start a Commerce Staging operation
    • I couldn’t use LINQPad on the server running Commerce Staging as I didn’t have the proper SQL CE 3.5 drivers, but when I moved it to my developer machine I could view the .sdf file without issue.  Interesting . . .
  4. My next step was to use ProcMon (available at https://docs.microsoft.com/en-us/sysinternals/downloads/procmon) and compare a process capture from my environment that raised the exception with a properly functioning Commerce Staging environment.  This took some trial and error, but filtering on the CSSsrv.exe helped me focus on the task at hand.
    • The properly functioning Staging environment had a lot of activity with the StagingLog.sdf file.  We’re talking hundreds of operations.  The broken Staging environment had none.  Once I tracked ProcMon instruction sets 1:1 between the two environments I found the final piece of evidence I needed:

SqlCE

The highlighted area above, C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\sqlceer35EN.dll entries, were entirely missing from the initialization process of my broken Commerce Staging environment.

The lightbulbs finally went off for me, and I did a quick comparison of the C:\Program Files (x86) directories of the two servers.  There were key components missing from the environment where Staging wasn’t working, and it revolved around SQL Server Compact Edition.  Here’s a highlight showing what obviously wasn’t on the server in question:

dirs

Neither MS SQL Server Compact Edition nor MS Synchronization Services were installed to this server.  I did some further research, and learned that the CommerceServer.exe should fold in these elements with any Commerce install, so I needed to do a repair installation with the CommerceServer.exe and that added SQL Server CE and other dependencies necessary to solve the issue.

Our PowerShell that installs the CommerceServer.exe must not have been using a properly elevated account, or this was done with a manual execution of the installer that didn’t use the proper administrative credentials.  Everything worked fine after I completed the repair install of Commerce Server.

There are some additional items to point out here:

  • Based on my testing, the CommerceServer.exe may exclude some key Staging dependencies if it’s not run in a proper Administrator context.  I thought it was written in the docs somewhere, but I don’t see it in Sitecore’s documentation on installing Commerce Server .exe — running that EXE in the context of a local admin account is crucial to the success of that process.  I use this account and the shift + right-click -> Run as different user” option when launching the CommerceServer.exe manually, and this has been successful for me.
  • If you’re like me, and love the post-script to a good story,  here’s what LINQPad shows for that StagingLog.sdf once you’ve run a couple successful Staging operations:

Linqpad

  • I think additional take aways from this write-up are:
    • there’s an MS Access database in C:\Program Files (x86)\Commerce Server 11\Staging\Events that could be a source of interesting diagnostic info on your Sitecore Commerce Staging activities
    • there’s an SQL CE database in C:\Program Files (x86)\Commerce Server 11\Staging\Data that could be a source of interesting diagnostic info on your Sitecore Commerce Staging activities

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!

A note on the Sitecore Commerce DCOM Config Permissions

I’ve been having lots of fun with multiple Sitecore Commerce projects and Azure SQL lately . . . here’s one quick note I can share that might save some hassle for others working in this space . . .

This Sitecore link mentions a DCOM permission to verify in the course of troubleshooting some Sitecore Commerce “Staging” issues.  My problem was that this guid, 7E95698D-CD3C-4C98-93C7-6510C31F7DDF, wasn’t visible in the Component Services treeview under “DCOM Config.”

Sitecore support informed me that they needed to update their documentation to mention that in the absence of that Guid, one should locate the “CSS Replication Server” object in the treeview and proceed from there.

My screen shot shows where the illusive Guid is stored as a property of the CSS Replication Server entry: DCOM

I expect Sitecore will update their documentation shortly, so this blog post may have a brief shelf-life in terms of relevancy . . . but if you’re like me and the Commerce platform, any notes are appreciated, so I’ll see if this helps anyone in the community.