Sitecore and SearchMaxResults for Solr

I’ve consulted with a number of Sitecore implementations in the last month or two that had a variety of challenges with Sitecore integration with Solr. Solr is a powerful distributed system with a variety of angles of interest to Sitecore. I wanted to take this chance to comment on a Sitecore setting that can have a significant impact on how Sitecore search functions, but is easily overlooked. The setting is defined in Sitecore.ContentSearch.config and it’s called ContentSearch.SearchMaxResults. The XML comment for this setting is straight-forward, here’s how it’s presented in that file:

snip

There’s a lot to digest in that xml comment above. One could read it and assume “this can be set but it is best kept as the default” means this shouldn’t be altered, but in my experience that can be problematic.

The .Net int.MaxValue constant is 2,147,483,647. If you leave this setting at the default (so “”), one is telling Solr to return up to 2,147,483,647 results in a single response to a query, which we’ve observed in some cases to cause significant performance problems (Solr will fetch the large volume of records from disk and write them to the Solr response causing IO pressure etc.) It’s not always the case since it really comes down to the number of documents one is querying from Solr, but this sets up the potential for a virtually unbounded Solr query.

It’s interesting to trace this setting through Sitecore and into Solr, and it sheds light on how these two complex systems work together. Fun, right!? I cooked up the diagram below that shows an overview of how Sitecore and Solr work together in completing search queries:

snipp

Each application has it’s own logging which will help trace activity between the systems.

The Sitecore ContentSearch Provider for Solr relies on Solr.Net for connectivity to Solr. It’s common for .Net libraries to copy their open source equivalents from the Java world (like Log4J has a .Net port for logging named Log4net, Lucene has a .Net port for search called Lucene.Net, etc). Solr.Net, however, is not a port of the Solr Java application to .Net. Instead, Solr.Net is a wrapper for the main Solr API elements that can be easily called by .Net applications. When it comes to Sitecore’s ContentSearch Provider for Solr, Solr.Net is Sitecore’s bridge for getting data to and from the Solr application.

Just an aside: some projects do creative things with Solr search and Sitecore, and for certain scenarios it’s necessary to bypass Solr.Net and use the REST API directly from Sitecore. This write-up focuses on a conventional Sitecore -> Solr.Net -> Solr pattern, but I wanted to acknowledge that it’s not the only pattern.

Tracking ContentSearch.SearchMaxResults in Sitecore

On the Sitecore side, one can see the ContentSearch.SearchMaxResults setting in the Sitecore logs when you turn up the diagnostics to get more granular data; this isn’t a configuration that’s recommended for using beyond a discrete troubleshooting session as the amount of data it can generate can be significant . . . but here’s how you dial up the diagnostic data Sitecore reports about queries:

snip3

If you run a few queries that exercise your Sitecore implementation code that queries Solr, you can review the contents of the Search log in the Sitecore /data directory and find entries such as:

INFO Solr Query – ?q=associated_docs:(“\*A5C71A21-47B5-156E-FBD1-B0E5EFED4D33\*”)&rows=2147483647&fq=_indexname:(domain_index_web)

or

INFO  Solr Query – ?q=((_fullpath:(\/sitecore/content/Branches/ABC/*) AND _templates:(d0351826b1cd4f57ac05816471ba3ebc)))&rows=2147483647&fq=_indexname:(domain_index_web)

The .Net int.MaxValue 2147483647 is what Sitecore, through Solr.Net, is specifying as the number of rows to return from this query. For Solr cores with only a few hundred results matching this query, it’s not that big a deal because the query has a fairly small universe to process and retrieve. If you have 100,000 documents, however, that’s a very heavy query for Solr to respond to and it will probably impact the performance of your Sitecore implementation.

Tracking ContentSearch.SearchMaxResults in Solr

Solr has it’s own logging systems and this 2147483647 value can be seen in these logs once Solr has completed the API call. In a default Solr installation, the logs will be located at server/logs (check your log4j.properties file if you don’t know for sure where your logs are being stored) and you can a open up the log and scan for our ContentSearch.SearchMaxResults setting value. You’ll see entries such as:

INFO  – 2018-03-26 21:20:19.624; org.apache.solr.core.SolrCore; [domain_index_web] webapp=/solr path=/select params={q=(_template:(a6f3979d03df4441904309e4d281c11b)+AND+_path:(1f6ce22fa51943f5b6c20be96502e6a7))&fl=*,score&fq=_indexname:(domain_index_web)&rows=2147483647&version=2.2} hits=2681 status=0 QTime=88

  • The above Solr query returned 2,681 results (hits) and the QTime (time elapsed between the arrival of the query request to Solr and the completion of the request handler) was 88 milliseconds. This is probably no big deal as it relates to the ContentSearch.SearchMaxResults, but you don’t know if this data will increase over time…

INFO  – 2018-03-26 21:20:19.703; org.apache.solr.core.SolrCore; [domain_index_web] webapp=/solr path=/select params={q=((((include_nav_xml_b:(True)+AND+_path:(00ada316e3e4498797916f411bc283cf)))+AND+url_s:[*+TO+*])+AND+(_language:(no-NO)+OR+_language:(en)))&fl=*,score&fq=_indexname:( domain_index_web)&rows=2147483647&version=2.2} hits=9 status=0 QTime=16

  • The above Solr query returned 9 results (hits) and the QTime was 16 milliseconds. This is unlikely a problem when it comes to ContentSearch.SearchMaxResults.

 INFO  – 2018-03-26 21:20:19.812; org.apache.solr.core.SolrCore; [domain_index_web] webapp=/solr path=/select params={q=(_template:(8ed95d51A5f64ae1927622f3209a661f)+AND+regionids_sm:(33ada316e3e4498799916f411bc283cf))&fl=*,score&fq=_indexname:(domain_index_web)&rows=2147483647&version=2.2} hits=89372 status=0 QTime=1600

  • The above Solr query returned 89,372 results (hits) and the QTime was 1600 milliseconds. Look out. This is the type of query that could easily cause problems based on the Sitecore ContentSearch.SearchMaxResults setting as the volume of data Solr is working with is getting large. That query time is already climbing high and that’s a lot of results for Sitecore to require in a single request.

The impact of retrieving so many documents from Solr can cause a cascade of difficulties besides just the handling of the query. Solr caches the query results in memory and if you request 1,000,000 documents you could also be caching 1,000,000 million documents. Too much of this activity and it can stress Solr to the breaking point.

Best Practices

There is no magic value to set for ContentSearch.SearchMaxResults other than not “”. General best practice when retrieving lots of data from most any system is to use paging. It’s recommended to do that for Sitecore ContentSearch queries, too. A general recommendation would be to set a specific value for the ContentSearch.SearchMaxResults setting, such as “500” or “1000”. This should be thoroughly tested, however, as limiting the search results for an implementation that isn’t properly using search result paging can lead to inconsistent behavior across the site. Areas such as site maps, general site search, and other areas with implementation logic that could assume all the search results are available in a single request deserve special attention when tuning this setting.

What About Noisy Solr Neighbors?

I’ve worked on some implementations where Solr was a resource shared between a variety of Sitecore implementations. One project, in this example, might set ContentSearch.SearchMaxResults to “2000” for their Sitecore sites while another project sets a value of “500” – but what if there’s a third organization making use of the same Solr resources and that project doesn’t explicitly set a value for ContentSearch.SearchMaxResults? That one project leaves the setting at the “” default, so it uses the .Net int.MaxValue. This is a classic noisy neighbor problem where shared services become a point of vulnerability to all the consuming applications. The one project with “” for ContentSearch.SearchMaxResults could be responsible for dragging Solr performance down across all the sites.

Solr is an extensible platform much like Sitecore, and in some ways even more so. In Sitecore one extends pipelines or overrides events to achieve the customizations you desire; the same general idea can be applied to Solr – you just use Java with Solr instead of C#.

In this case, our concern being unbounded Solr queries, we can use an extension to a search component (org.apache.solr.handler.component.SearchComponent) to introduce our custom logic into the Solr query processing. In our case, we want to enforce limits to the number of rows a query can request. This would be a safety net in case an un-tuned Sitecore implementation left a ContentSearch.SearchMaxResults setting at the default.

Some care must be taken in how this is introduced into the Solr execution context and where exactly the custom logic is handled. This topic is all covered very well, along with sample code etc, at https://jorgelbg.wordpress.com/2015/03/12/adding-some-safeguard-measures-to-solr-with-a-searchcomponent/. For an enterprise Solr implementation with a variety of Sitecore consumers, a safety measure such as this could be vital to the general stability and perf of the platform – especially if one doesn’t have control over the specific Sitecore projects and their use (or abuse!) of the ContentSearch.SearchMaxResults setting. Maybe file this under best practices for governing Sitecore implementations with shared Solr infrastructure.

Advertisements

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.

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 9 SIF – WebDeploy Gets What WebDeploy Wants

I’ve been doing a lot with Sitecore 9 lately as we work to retrofit automation processes into the new Sitecore Installation Framework (SIF) paradigm.  It will be nice to have Sitecore’s approved way of provisioning a Sitecore environment with JSON and PowerShell . . . but it’s a shame we’ve spent years investing in our own libraries doing the same thing with essentially the same technologies 🙂

That may sound like sour grapes from me, but that’s not really the case.  It’s just not trivial to master the SIF specifics at the same time as one is digesting the whole of Sitecore 9 with xConnect, Sitecore PaaS, and Sitecore 9 for Commerce coming any day now.  Taking a step back, I’m now in the process of crafting PowerShell to automate the prequisites for Sitecore 9 SIF — and if that strikes you as ironic, I think you get my point.  Can a publicly available Sitecore 9 bootstrapper be that far away?  Lets just hope it’s not a custom miasma of JSON with PowerShell and with it’s very own prerequisites to perpetuate the cycle!

Just an aside: I think many of us are doing the same thing, just refer to this effort around Solr or this GUI to layer on top of Sitecore’s new framework.  Both these efforts, and many others, are excellent, but illustrate how Sitecore 9 SIF feels unfinished.

With that all off my chest . . . I thought I’d share a gotcha with SIF that I haven’t seen documented elsewhere in case it helps others.  Categorize this as a self-inflicted Sitecore 9 SIF wound, I can’t really blame the framework here — the exception message is actually very helpful.  While the WebDeploy task was running with any Sitecore 9 SIF installation, I would get this error every time from a few of our servers:

Could not deploy package.
Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name
of a valid database platform service.

Here’s how it looks in situ as part of the InstallWDP : WebDeploy task with SIF:

scriptdom2

This didn’t happen on development environments, but on some build servers or new virtual machines.

I reviewed the Sitecore installation documentation but couldn’t see anything explicitly speaking to this, and I verified the SQL Server DAC fx was consistent with the installation docs.  My initial solution was to install SQL Server Management Studio (SSMS) as that package includes the key dependencies for automating SQL Server 2016 the way SIF expects.  Then I realized it’s probably encompassed in this line item tucked into the Installation Guide:

scriptdom

The link https://msdn.microsoft.com/en-us/dn864412 reads more like VS.Net integration tooling when I glanced at it, so I initially dismissed it for my use-case . . . but WebDeploy gets what WebDeploy wants, and to get Sitecore 9 SIF running you’ll need these libraries one way or the other.

This explains why development machines or other servers that had been around a while wouldn’t exhibit this problem — they already had the SQL tooling installed through one means or another.

For posterity, here’s the output from my console in case it helps a web searcher find their resolution . . .

InstallWDP : WebDeploy 
[WebDeploy]:[Path] C:\Program Files\iis\Microsoft Web Deploy V3\msdeploy.exe
Info: Adding MsDeploy.Site (MsDeploy.Site).
Info: Adding database (user id=sqlUserNotTelling;data source=SC9Run482-SQL;initial catalog=xp0482_Processing.Pools)
Info: Initializing deployment: Pending.
Info: Analyzing deployment plan: Pending.
Info: Updating database: Pending.
Info: Creating deployment plan: Pending.
Info: Verifying deployment plan: Pending.
Info: Deploying package to database: Pending.
Info: Creating deployment plan: Running.
Info: Initializing deployment: Running.
Info: Initializing deployment (Start)
Info: Initializing deployment: Faulted.
Info: Initializing deployment (Failed)
Info: Creating deployment plan: Faulted.
Info: Verifying deployment plan: Faulted.
Info: Deploying package to database: Faulted.
msdeploy.exe : Error Code: ERROR_EXECUTING_METHOD
At C:\Program Files\WindowsPowerShell\Modules\SitecoreInstallFramework\1.1.0\Public\Tasks\Invoke-CommandTask.ps1:31 char:13
+ & $Path $Arguments | Out-Default
+ ~~~~~~~~~~~~~~~~~~
 + CategoryInfo : NotSpecified: (Error Code: ERROR_EXECUTING_METHOD:String) [], RemoteException
 + FullyQualifiedErrorId : NativeCommandError
 
More Information: Could not deploy package.
Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name
 of a valid database platform service.

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