Multi-region Sitecore Publishing

Sitecore’s Publishing Service that runs on .NET Core is a great addition to the Sitecore ecosystem. It allows us to solve some interesting customer scaling challenges by using this micro-services approach to Publishing content. I’m going to write-up a pattern we’re using these days that updates our approach from a few years ago.

See an example of the older pattern in this piece I wrote for the Rackspace site at https://developer.rackspace.com/blog/Sitecore-Enterprise-Architecture-For-Global-Publishing/.

Now in May 2019, we’re shifting away from the SQL replication game and using Sitecore’s new Publishing Service to connect Sitecore across multiple regions. Refer to this general diagram below to see how we’re approaching it:

2RegionPublishingService

Sitecore’s Publishing Service is the key element between the two regions and the blue arrows show the flow of publishing activities coordinated through the one “Sitecore Publishing Service” host in Region 1.

A few caveats on the picture above:

  1. It’s Sitecore 8.2, so MongoDB is present but not shown on the diagram for simplicity (we use ObjectRocket’s hosted MongoDB service for the majority of these types of customers — but I don’t want to get into that here); Redis and other elements are also not included in the diagram
  2. This applies for any multi-region setup with Sitecore. . . it could be East US and West US, for example, but we used Europe and Asia in the diagram. This approach is most useful where network latency between the regions is enough to make synchronous database connectivity unacceptably slow. This model can apply to more than 2 regions, too, as the pattern can be repeated to support as many regions as you require.

There are just a few crucial configuration steps to make this happen, but it’s built on a lot of lessons learned along the way. Let me catalog the key elements:

  1. The Publishing Service runs in Region 1, but requires a Sitecore Publishing Target to the Region 2 database. The documentation on setting up this type of Publishing Target is vague, so I summarized this process at https://grantkillian.wordpress.com/2018/12/17/how-i-add-custom-sitecore-publishing-service-targets/.
  2. Each region has an isolated Solr cluster (because Solr CDCR or file synchronization for Solr were not suitable in this use-case). This means one of the Region 2 Sitecore CD servers needs to employ the onPublishEndAsync strategy to update the Solr Cloud collections relevant to the implementation. This is standard ContentSearch configuration material, but if you use the manual strategy here with the CDs (which is the general best practice for Sitecore CD servers connected to a Solr cluster with a CM that drives search indexing), the Solr data will never get updated in the other region:
    • <strategies hint="list:AddStrategy">
        <strategy 
        ref="contentSearch/indexConfigurations/indexUpdateStrategies/onPublishEndAsync"/>
      </strategies>
  3. If you are using Sitecore ContentTesting with this approach (<setting name=”ContentTesting.AutomaticContentTesting.Enabled” value=”true” />), you should be aware that Sitecore CM performance can occasionally stall for several minutes (we’ve seen it last up to 20 minutes!) due to an aspect of the ContentTesting logic that checks every content database for eligible published items to factor into the content testing system. Part of setting up the Region 2 Publishing Target involves adding a ConnectionStrings.config entry to the Region 2 “web” database on the Region 1 Sitecore CM server. This adds the Region 2 “web” database into this ContentTesting routine, and the network latency between Region 1 and Region 2 makes this ContentTesting behaviour slow the CM to a crawl every so often.  If you don’t want to disable Sitecore ContentTesting, you can address this by customizing the Sitecore.ContentTesting.Helpers.VersionHelper.GetLatestPublishedVersion method to employ logic to exclude the Region 2 “web” database. Once you dig deep into this topic, you’ll see the Sitecore.ContentTesting.Helpers.VersionHelper class contains this logic and it’s used in 3 places (according to the decompilation of the .dll):

dude

To adjust ContentTesting to ignore our Region 2 “web” database, we can alter the foreach loop above with something like this that uses a custom “ContentTesting.IgnoredDatabases” setting:

foreach (Database db in Factory.GetDatabases())
{
  string[] excludeList = 
    Sitecore.Configuration.Settings.GetSetting(
    "ContentTesting.IgnoredDatabases")
    .ToLowerInvariant().Split(
        new char[1]
       {
        '|'
       }, 
   StringSplitOptions.RemoveEmptyEntries);
  if (database != null && 
    db.Name != database.Name && 
    !excludeList.Contains(db.Name))
  {
    Item item2 = db.GetItem(item.ID, item.Language);
    if (item2 != null && item2.Version.Number > num)
    {
      num = item2.Version.Number;
    }
  }
}

We can define our custom setting like the following, if we assume region2web is the “web” database ConnectionString name for the Region 2 publishing target on the Sitecore CM:

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <settings>
      <setting name="ContentTesting.IgnoredDatabases">
        <patch:attribute name="value">core|region2web</patch:attribute>
      </setting> 
    </settings>
  </sitecore>
</configuration>

This work to override the default configuration from . . .

<getVersionedTestCandidates>
  <processor 
    type="Sitecore.ContentTesting.Pipelines.GetTestCandidates.GetPageVersionTestCandidates, Sitecore.ContentTesting">

. . . can dramatically improve the Sitecore CM performance when using this formula for multi-region Sitecore with the new Publishing Service.

Hopefully these notes help other efforts on their Sitecore journey!

Sitecore 9 CD Servers May Assume “Master” EventQueue by default

Here’s a quick one, and I wish it was a clever April Fools’ joke but it isn’t.

Sitecore support recently confirmed a bug for me in Sitecore 9.0 update-2 (may be present for other versions in the Sitecore 9 space — I’m unsure). A Sitecore CD environment might report exceptions assuming a “master” database endpoint like this:

Unknown connection string. Name: 'master'

For the stacktraces I’ve seen with this issue, it’s something like the following:

ERROR One or more exceptions occurred while processing the 
subscribers to the 'publish:end:remote' event.

In the days of Sitecore 8 (I guess those are the olden times now?), we’d adjust our SwitchMasterToWeb.config to address the EventQueue configuration that assumes the presence of a “master” database. For what it’s worth, I always thought Kam Figy’s was the most thorough at https://gist.github.com/kamsar/8096336f141c0e5e97b3.

In the case of this Sitecore 9 issue, we could brew up our own SwitchMasterToWeb.config patch file or work around the issue using role:require logic on the <eventQueue> node in Sitecore.config file. I thought the Sitecore 9 role:define features were designed to make the SwitchMasterToWeb.config obsolete, but if we don’t want to alter Sitecore’s default sitecore.config file, we may need a SwitchMasterToWebForSitecore9.config. History is cyclical!

Here’s the fragment of sitecore.config I’m referring to:

<eventQueueProvider defaultEventQueue=”core”>

<eventQueue role:require=”ContentManagement or Standalone” name=”master” type=”Sitecore.Data.Eventing.$(database)EventQueue, Sitecore.Kernel”>
<param ref=”dataApis/dataApi[@name=’$(database)’]” param1=”$(name)” />
<param hint=”” ref=”PropertyStoreProvider/store[@name=’$(name)’]” />
</eventQueue>

Some notes on the Sitecore Commerce PaaS Marketplace provisioning

I’m on the hook to do a session at the Manchester, NH Sitecore User Group next month and the topic will be a deep-dive into diagnostics for Azure PaaS Sitecore implementations. This is an absolutely huge surface area for a one hour talk, so I have a lot of flexibility in where to focus. One decision I made was to use a Sitecore Commerce PaaS sandbox as the foundation for the session. We’ll dive into the Azure Redis, Azure SQL, Azure Search, Azure App Services, and all the other PaaS goodness that comprise this Sitecore 9 Commerce in PaaS implementation I create.

I haven’t worked with the Azure Marketplace for a Sitecore PaaS implementation in about a year, so I worked through that interface to create a shiny new Sitecore Commerce environment . . .paas

. . .  it’s a straight-forward process and after a few minutes I was patiently waiting for my new Azure PaaS environment to be ready.

Issue #1

Then some bad news: Azure’s interface reported that the Sitecore PaaS deployment had failed after about 30 minutes of work.

DeploymentFailed:  At least one resource deployment operation failed. Please list deployment operations for details

I’ll post the full error at the end of the post, in case it helps a web searcher to find the resolution. For brevity, I scanned the details of the failure in JSON format and picked out this nugget:

ERROR_SQL_EXECUTION_FAILURE. ---&gt; System.Data.SqlClient.SqlException: Incorrect syntax near 't'.

I’ve done enough Sitecore and PowerShell automation for this to set off familiar alarm bells. This looked like a character encoding or invalid escape sequence issue — like where an & (ampersand) or another “special” character was invalidating the script.  Several of the inputs in the Sitecore Marketplace forms are password fields, and like a good computer worker I used a strong password (like from https://www.grc.com/passwords.htm for example). One of my special character’s was a single quote (‘), to it really had me wondering about the password fields.

Regardless, I tried a second time, thinking maybe my Azure resources had a transient issue; I picked different Azure regions for the resources, because occasionally I’ve seen the same operation succeed with one Azure region but fail for another. Unfortunately, this second attempt also failed with the same type of exception.

For my third attempt, I tried using a weak password, just a simple alphanumeric like “123abc”– and the provisioning succeeded. It took almost 2 hours to run to completion, but I didn’t hit any failures. I was able to update the passwords to something more secure afterwards, since the automation creating the PaaS resources is a one-time execution, so I don’t have to live long term with the rather weak password.

I reached out to Sitecore Support and they confirmed it’s a defect they’re tracking under reference number 320324. I suggested the put a bit of validation in the marketplace module, then, because surely everyone is running into this?

Issue #2

The next issue I encountered was the Sitecore Commerce Catalog wasn’t properly bootstrapped into the PaaS environment. The Sitecore CM showed no catalogs in any of the usual places:

Sitecore support confirmed this behaviour and explained it’s an oversight of the installation routines. In the Azure Marketplace setup, I correctly selected the sample Habitat catalog with the SXA components etc. To fix this problem, I needed to “clean an initialize” the environment myself to include the sample catalog. With IaaS deployments, this magic is handled by SIF routines (go down that rabbit hole at https://github.com/CommerceMinion/Sitecore-Commerce-v902-Scaled-Installation if you’re brave).

These two blog posts were particularly helpful as I worked through the manual PostMan initialization process:

  1. https://tothecore.sk/2018/07/25/setting-up-development-environment-with-postman-and-sitecore-experience-commerce-sxc-9/
  2. https://naveed-ahmad.com/2018/02/21/sitecore-experience-commerce-xc9-getting-started-with-postman/

Many thanks to the authors https://tothecore.sk/about/ and https://naveed-ahmad.com/!

Finally, from Issue #1, here’s the full details of the provisioning failure message related to the password:

{“code\”: \”Conflict\”,”message\”: \”{\\r\\n “status”: “failed”,\\r\\n “error”: {\\r\\n “code”: “ResourceDeploymentFailure”,\\r\\n “message”: “The resource operation completed with terminal provisioning state ‘failed’.”,\\r\\n “details”: [\\r\\n {\\r\\n “code”: “Failed”,\\r\\n “message”: “Package deployment failed\AppGallery Deploy Failed: ‘Microsoft.Web.Deployment.DeploymentDetailedClientServerException: An error occurred during execution of the database script. The error occurred between the following lines of the script: “1” and “43”. The verbose log might have more information about the error. The command started with the following:\”declare @ApplicationName nvarchar(256) = ‘sitecore”\ Incorrect syntax near ‘t’.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘sql’ has already been declared. Label names must be unique within a query batch or stored procedure.\Unclosed quotation mark after the character string ‘\ ) from (select @Salt as [bin] ) T \execute [dbo].[aspnet_Membership_SetPassword] \ @ApplicationName\ ,@UserName\ ,@EncodedHash\ ,@EncodedSalt\ ,@CurrentTimeUtc\ ,@PasswordFormat\’. http://go.microsoft.com/fwlink/?LinkId=178587 Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE. —&gt; System.Data.SqlClient.SqlException: Incorrect syntax near ‘t’.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘sql’ has already been declared. Label names must be unique within a query batch or stored procedure.\Unclosed quotation mark after the character string ‘\ ) from (select @Salt as [bin] ) T \execute [dbo].[aspnet_Membership_SetPassword] \ @ApplicationName\ ,@UserName\ ,@EncodedHash\ ,@EncodedSalt\ ,@CurrentTimeUtc\ ,@PasswordFormat\’.\ at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\ at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\ at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean&amp; dataReady)\ at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)\ at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean&amp; usedCache, Boolean asyncWrite, Boolean inRetry)\ at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\ at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)\ — End of inner exception stack trace —\ at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)\ at Microsoft.Web.Deployment.DBConnectionWrapper.ExecuteSql(DBStatementInfo sqlStatement, DeploymentBaseContext baseContext, Int32 timeout)\ at Microsoft.Web.Deployment.SqlScriptToDBProvider.AddHelper(DeploymentObject source, Boolean whatIf)\ at Microsoft.Web.Deployment.DeploymentObject.AddChild(DeploymentObject source, Int32 position, DeploymentSyncContext syncContext)\ at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAddChild(DeploymentObject destParent, DeploymentObject sourceObject, Int32 position)\ at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)\ at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)\ at Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObject destinationObject, DeploymentObject sourceObject)\ at Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, ContentRootTable contentRootTable, Nullable`1 syncPassId, String syncSessionId)\ at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentProviderOptions providerOptions, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)\ at Microsoft.Web.Deployment.WebApi.AppGalleryPackage.Deploy(String deploymentSite, String siteSlotId, Boolean doNotDelete)\ at Microsoft.Web.Deployment.WebApi.DeploymentController.&lt;DownloadAndDeployPackage&gt;d__17.MoveNext()

OPTIONS and PUT Verbs for the Sitecore Publishing Service

I’ve been involved with a lot of implementations of the new Sitecore Publishing Service lately and they’re starting to all run together. Before this episode fades too far into history, I want to record what I learned in case it’s useful to others (or myself!) at some point.

I’m going to skip all the preamble about .NET Core and how the new Publishing Service is designed to be lean and mean . . . that’s covered adequately in other places by now. I’ll jump right into this scenario where the Publishing Service was installed but all publishing operations would fail from the Sitecore Content Management server. It seemed like the CM wasn’t able to reach the .NET Core Publishing Service endpoint, but I confirmed the following were all in place:

  1. the host file included an entry for 127.0.0.1 pointing to Sitecore.Publishing.Service
  2. the Publishing Service was hosted through IIS with the Sitecore.Publishing.Service binding
  3. http://sitecore.publishing.service/api/publishing/operations/status responded with the expected JSON response “status 0” — so I concluded the service itself was OK

The Sitecore logs did have this cryptic “NotFound Not Found” exception, which was all I had to go on at first:

ERROR [Sitecore Services]: HTTP POST
URL http://cm.website.com/sitecore/api/ssc/publishing/jobs/0/ItemPublish

Exception System.AggregateException: One or more errors occurred. ---> Sitecore.Framework.Publishing.Client.Http.HttpServiceResponseException: The remote service encountered a problem processing the request:
NotFound Not Found
System.Net.Http.StreamContent
   at Sitecore.Framework.Publishing.Client.Http.JsonClient.<SendRequest>d__14.MoveNext()
--- End of stack trace from previous location where exception was thrown ---

I envisioned a frustrated IIS server yelling “NotFound Not Found” — and, actually, that wasn’t so far from the truth. To discover the truth, I needed Fiddler.

There’s a ton of API calls going on when Sitecore CM servers interact with the Publishing Service, so Fiddler was the way to get a handle on all that communication. Sitecore has a KB article on how to wire up Fiddler to your Sitecore implementation, which is handy, and I put it to good use.

It was also necessary to dial up the verbosity of the Publishing Service instrumentation so we can see the full picture. One does this through the \config\sitecore\sc.logging.xml file under the Publishing Service installation. Set the filters like this . . .

 <Filters>
<Sitecore>Debug</Sitecore>
<Default>Debug</Default>
</Filters>

. . . and restart the .NET Core service to make sure these take effect.

Fiddler can provide an overwhelming volume of information; imagine hundreds of entries like the following:

Fiddler1

One technique is to carefully clear the Fiddler traces until just before you interact with the browser to perform your action, in my case just before I press the final Publish button in the Sitecore CM environment. I still had a lot of noisy Fiddler data to ignore, which I’ll remove from the screenshot below, but finally it revealed this red entry meaning failure.Fiddler2

And if you click on the details of that Fiddler trace you can inspect all the particulars. In this case it was pretty obvious the HTTP Verb Put wasn’t allowed here:

Fiddler3

The fix was fairly complicated because there are good reasons for servers to block HTTP Verbs that aren’t in use, so we had to make the case PUT was required for the Publishing Service to work (and then add HTTP Verb blocking at a different layer of the implementation). We also found the OPTIONS HTTP verb was crucial for Publishing Service work using the same Fiddler technique described above. We ended up needing to permit both those 2 additional HTTP Verbs to enable proper Sitecore Publishing Service activity.

This isn’t really the final way to permit those verbs in a real production implementation, but it’s close enough for the purposes of this blog . . . check out the IIS Request Filtering options we needed to explicitly permit for this CM server to communicate to the Publishing Service running on the same VM host:

Fiddler4

Sitecore’s had API service layers that depended on these HTTP Verbs for many years, so I shouldn’t be surprised to find the Publishing Service is also making use of them. I just never connected the two together. This customer, too, is very security conscious and features are thoroughly locked-down unless you explicitly enable them. This Sitecore 8 project isn’t otherwise using the Sitecore Sevices Client, for example, or the Item API. The good news is these PUT and OPTIONS exceptions are only internal to the system and not opened up outside to the public internet thanks to firewalls and other networking.

I will also note that we briefly explored the idea of customizing the HTTP Verbs the Publishing Service made use of  . . . but you can imagine threading the needle of dependencies through that stack of libraries would be a very tough challenge.

New Sitecore 8.2 & Sitecore 9 Security Patch

I guess I’m on a security hardening binge for 2019, since I’ll share a hot-off-the-presses security hardening measure from Sitecore today. I don’t want to say too much about the vulnerability, but this article explains in general terms and applying it to all Sitecore server roles for version 8.2 through any current 9 releases is emphasized as the best practice. I’d do it at the next opportunity.

I created a gist with the PowerShell necessary to apply this patch, just update line #8 with the path to your Sitecore website:


Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
Import-Module WebAdministration
$url = "https://kb.sitecore.net/~/media/7A638A36A71D4494981A8655E297AD23.ashx?la=en&quot;
$tempLocation = "C:\tempLocation"
$zippedPatch = "$tempLocation\SitecoreSupportPackage.302938.zip"
$unzippedPatch = "$tempLocation\SitecoreSupportPackage"
$sitecoreRoot = "C:\InetPub\Your\Sitecore\Website"
if (!(Test-Path $tempLocation))
{
New-Item -ItemType Directory -Path $tempLocation
}
Invoke-WebRequest -Uri $url -OutFile $zippedPatch
Expand-Archive -Path $zippedPatch -DestinationPath $unzippedPatch -Force
Copy-Item "$unzippedPatch/website/*" -Destination $sitecoreRoot -Recurse -Force
Write-Host "Patch applied to $sitecoreRoot"
Remove-Item $unzippedPatch -Recurse
Remove-Item $zippedPatch -Recurse

Our team has internal automation taking the above a bit further and using another layer of abstraction, and that’s secret Rackspace sauce I won’t share publicly,  but the snippet above should have your environment patched in just a few seconds.

One of the key elements to the patch involves an update to the /sitecore/admin/logs.aspx page which, if you dig into it, reveals a grip-load of additional C# validation logic and other stuff going on . . .

secpatch

There’s a lot to unpack in there if you’re curious, but suffice it to say that Sitecore’s keeping all your bases covered and isn’t trusting user input (using a broad interpretation of that principle).

Sitecore Commerce security hardening note

Let’s start the New Year off with a fun Sitecore Commerce note. Using the latest Sitecore Commerce available today, that is running Sitecore 9.0 update-2 with Sitecore Commerce update-3 (you have to cross-reference https://dev.sitecore.net/Downloads/Sitecore_Commerce.aspx and https://kb.sitecore.net/articles/804595 to really sort this out), we’re applying routine security hardening.

Now that Sitecore is truly built on a hybrid of “plain” .Net and .Net Core, this security hardening effort is more nuanced.

Sitecore is still updating their documentation for the Sitecore 9 space and one can end up at dead-ends like https://doc.sitecore.com/developers/90/platform-administration-and-architecture/en/security-guide-251908.html that leads you over to the .Net Framework documentation when there are better notes with 100% relevancy to Sitecore elsewhere on the Sitecore site. I persevered and eventually found Sitecore’s updated information like this on the hash algorithm https://doc.sitecore.com/developers/90/platform-administration-and-architecture/en/change-the-hash-algorithm-for-password-encryption.html. Still, this documentation overlooks the .Net Core details and given that this Sitecore Commerce project we’re working on will use the latest and greatest, we had to do our own research.

Fortunately, we have some history with this having published https://developer.rackspace.com/blog/Updated-Security-Hardening-For-Sitecore-8.2 or earlier versions going back several years. The PowerShell we’ve used for ages to automate this work, however, wasn’t going to cut it with this new Commerce and .Net Core dimension:

snippet

Instead, we need to do something like this to update the JSON configuration for the Sitecore Identity Server. While you could get fancy and parse the JSON, I used a more direct replace approach to knock this out quickly:

$siteNamePrompt = Read-Host "enter Identity Server website name"
$site = get-website -name $siteNamePrompt
$appSettingsPath = "{0}\wwwroot\appsettings.json" -f $site.physicalPath
Get-Content $appSettingsPath).replace("""PasswordHashAlgorithm"":""SHA1""},", """PasswordHashAlgorithm"":""SHA512""},") | Set-Content $appSettingsPath

The end result is  that SitecoreIdentityServer\wwwroot\appsettings.json file needs an updated PasswordHashAlgorithm value:

        “IDServerCertificateStoreLocation”: “LocalMachine”,
“IDServerCertificateStoreName”: “My”,
        “PasswordHashAlgorithm”: “SHA512”
}

Given the distributed nature of Sitecore 9 with Commerce, I think a discrete change like this just for the IdentityServer doesn’t warrant a lot of effort to integrate into the bigger security hardening Powershell script we use. It may be worthwhile to just update SIF at this point instead of applying security hardening after the Sitecore installation is complete. We’re also talking about SIF extension modules to run this type of logic after SIF is complete. For now, I’ll probably just keep this note handy for the foreseeable future and see whether Sitecore integrates the security hardening guidance directly into SIF in a future release (hint hint!) — or, over time we may collect a set of these best practice adjustments that deserves more effort to automate into a scripted deployment. For now, I think I’ve taken it as far as it deserves.

How I Add Custom Sitecore Publishing Service Targets

At this point, I think I’ve installed, configured, or customized the new Sitecore Publishing Service at least a dozen times for various projects. Sometimes it’s on PaaS, sometimes on IaaS . . . I’ve used a variety of different versions depending on the compatibility matrix (see below as of Dec 16, 2018):

PubSvcVisual

I’m going to skip all the preamble about how the new Sitecore Publishing Service works, about .Net core being the new hotness, why this component can be a great addition to many distributed Sitecore implementations, etc — smart people have written a lot about this already. For example, check out Stephen Pope’s no-holds-barred look at the Publishing Service at http://www.stephenpope.co.uk/publishing or Jonathan Robbins has a nice overview piece at https://jonathanrobbins.co.uk/2016/09/02/setting-up-sitecore-publishing-service/.

I’ve learned a good bit from all the iterations of working with the component and I think consistently the most error-prone part of the setup is aligning any additional custom Sitecore publishing targets one is using in an implementation. This write-up from Geykel Moreno at AlphaSolutions has all the good information, but it’s not as easy to follow because it doesn’t post a comprehensive sc.publishing.xml file — it took a bit of trial and error for me, so to simplify for posterity I’m going to share a reference sample Gist at https://gist.github.com/grant-killian/d2fe8d3e89c5d7b15f47464dd1809d62 that includes 2 additional custom publishing targets. I’ve inserted XML comments for the 3 locations one must update in the config\sitecore\publishing\sc.publishing.xml file:

  1. You need to add your ConnectionString entry for each database to the Publishing/ConnectionStrings XML
  2. You need to add your Services/DefaultConnectionFactory/Options/Connections XML definition for each custom target
  3. You need to add entries for each target to the StoreFactory/Options/Stores/Targets XML that will include the GUID of the Sitecore item that defines each publishing target, along with the Name of the item and additional details

Here’s the gist with the full XML for reference:


<?xml version="1.0" encoding="UTF-8"?>
<Settings>
<Sitecore>
<Publishing>
<InstanceName>${SITECORE_InstanceName}</InstanceName>
<ConnectionStrings>
<Service>${Sitecore:Publishing:ConnectionStrings:Master}</Service>
<!– Add any additional publishing targets you may use (first location for changes to this file) –>
<previewweb>Data Source=Server-012345;Initial Catalog=PrevWeb;Integrated Security=True;MultipleActiveResultSets=True;ConnectRetryCount=15;ConnectRetryInterval=1</previewweb>
<liveweb>Data Source=Server-012345;Initial Catalog=LiveWeb;Integrated Security=True;MultipleActiveResultSets=True;ConnectRetryCount=15;ConnectRetryInterval=1</liveweb>
<!– end first location for changes –>
</ConnectionStrings>
<Services>
<DefaultConnectionFactory>
<Options>
<Connections>
<Links>
<Type>Sitecore.Framework.Publishing.Data.AdoNet.SqlDatabaseConnection, Sitecore.Framework.Publishing.Data</Type>
<LifeTime>Transient</LifeTime>
<Options>
<ConnectionString>${Sitecore:Publishing:ConnectionStrings:Core}</ConnectionString>
<DefaultCommandTimeout>120</DefaultCommandTimeout>
<Behaviours>
<backend>sql-backend-default</backend>
<api>sql-api-default</api>
</Behaviours>
</Options>
</Links>
<Service>
<Type>Sitecore.Framework.Publishing.Data.AdoNet.SqlDatabaseConnection, Sitecore.Framework.Publishing.Data</Type>
<LifeTime>Transient</LifeTime>
<Options>
<ConnectionString>${Sitecore:Publishing:ConnectionStrings:Service}</ConnectionString>
<DefaultCommandTimeout>120</DefaultCommandTimeout>
<Behaviours>
<backend>sql-backend-default</backend>
<api>sql-api-default</api>
</Behaviours>
</Options>
</Service>
<Master>
<Type>Sitecore.Framework.Publishing.Data.AdoNet.SqlDatabaseConnection, Sitecore.Framework.Publishing.Data</Type>
<LifeTime>Transient</LifeTime>
<Options>
<ConnectionString>${Sitecore:Publishing:ConnectionStrings:Master}</ConnectionString>
<DefaultCommandTimeout>120</DefaultCommandTimeout>
<Behaviours>
<backend>sql-backend-default</backend>
<api>sql-api-default</api>
</Behaviours>
</Options>
</Master>
<Internet>
<!– Should match the name of the publishing target configured in SC. –>
<Type>Sitecore.Framework.Publishing.Data.AdoNet.SqlDatabaseConnection, Sitecore.Framework.Publishing.Data</Type>
<LifeTime>Transient</LifeTime>
<Options>
<ConnectionString>${Sitecore:Publishing:ConnectionStrings:Web}</ConnectionString>
<DefaultCommandTimeout>120</DefaultCommandTimeout>
<Behaviours>
<backend>sql-backend-default</backend>
<api>sql-api-default</api>
</Behaviours>
</Options>
</Internet>
<!– start custom publishing target additions (2nd location) –>
<Preview>
<!– Should match the name of the publishing target configured in Sitecore –>
<Type>Sitecore.Framework.Publishing.Data.AdoNet.SqlDatabaseConnection, Sitecore.Framework.Publishing.Data</Type>
<LifeTime>Transient</LifeTime>
<Options>
<ConnectionString>${Sitecore:Publishing:ConnectionStrings:previewweb}</ConnectionString>
<DefaultCommandTimeout>120</DefaultCommandTimeout>
<Behaviours>
<backend>sql-backend-default</backend>
<api>sql-api-default</api>
</Behaviours>
</Options>
</Preview>
<Live>
<!– Should match the name of the publishing target configured in Sitecore –>
<Type>Sitecore.Framework.Publishing.Data.AdoNet.SqlDatabaseConnection, Sitecore.Framework.Publishing.Data</Type>
<LifeTime>Transient</LifeTime>
<Options>
<ConnectionString>${Sitecore:Publishing:ConnectionStrings:liveweb}</ConnectionString>
<DefaultCommandTimeout>120</DefaultCommandTimeout>
<Behaviours>
<backend>sql-backend-default</backend>
<api>sql-api-default</api>
</Behaviours>
</Options>
</Live>
<!– end custom publishing target additions (2nd location) –>
</Connections>
</Options>
</DefaultConnectionFactory>
<DbConnectionBehaviours>
<Options>
<Entries>
<sql-backend-default>
<Type>Sitecore.Framework.Publishing.Data.AdoNet.NoRetryConnectionBehaviour, Sitecore.Framework.Publishing.Data</Type>
<Options>
<Name>Default Backend No Retry behaviour</Name>
<CommandTimeout>120</CommandTimeout>
</Options>
</sql-backend-default>
<sql-api-default>
<Type>Sitecore.Framework.Publishing.Data.AdoNet.NoRetryConnectionBehaviour, Sitecore.Framework.Publishing.Data</Type>
<Options>
<Name>Default Api No Retry behaviour</Name>
<CommandTimeout>10</CommandTimeout>
</Options>
</sql-api-default>
</Entries>
</Options>
</DbConnectionBehaviours>
<StoreFactory>
<Options>
<Stores>
<Service>
<Type>Sitecore.Framework.Publishing.Data.ServiceStore, Sitecore.Framework.Publishing.Data</Type>
<ConnectionName>Service</ConnectionName>
<FeaturesListName>ServiceStoreFeatures</FeaturesListName>
</Service>
<Sources>
<Master>
<Type>Sitecore.Framework.Publishing.Data.SourceStore, Sitecore.Framework.Publishing.Data</Type>
<ConnectionNames>
<master>Master</master>
</ConnectionNames>
<FeaturesListName>SourceStoreFeatures</FeaturesListName>
<!– The name of the Database entity in Sitecore. –>
<ScDatabase>master</ScDatabase>
</Master>
</Sources>
<Targets>
<!–Additional targets can be configured here–>
<Internet>
<Type>Sitecore.Framework.Publishing.Data.TargetStore, Sitecore.Framework.Publishing.Data</Type>
<ConnectionName>Internet</ConnectionName>
<FeaturesListName>TargetStoreFeatures</FeaturesListName>
<!– The id of the target item definition in Sitecore. –>
<Id>8E080626-DDC3-4EF4-A1D1-F0BE4A200254</Id>
<!– The name of the Database entity in Sitecore. –>
<ScDatabase>web</ScDatabase>
</Internet>
<!– start custom publishing target additions (third location) –>
<!– this XML node should be named the same as the item in Sitecore (not the "Display Name", but the Item name) –>
<Preview>
<Type>Sitecore.Framework.Publishing.Data.TargetStore, Sitecore.Framework.Publishing.Data</Type>
<ConnectionName>Preview</ConnectionName>
<FeaturesListName>TargetStoreFeatures</FeaturesListName>
<!– make sure the GUID below matches the GUID stored in Sitecore for the Publishing Target –>
<Id>8D1249E6-9413-4C2D-8C72-06561CE1D026</Id>
<ScDatabase>preveiwweb</ScDatabase>
</Preview>
<!– this XML node should be named the same as the item in Sitecore (not the "Display Name", but the Item name) –>
<Live>
<Type>Sitecore.Framework.Publishing.Data.TargetStore, Sitecore.Framework.Publishing.Data</Type>
<ConnectionName>Live</ConnectionName>
<FeaturesListName>TargetStoreFeatures</FeaturesListName>
<!– make sure the GUID below matches the GUID stored in Sitecore for the Publishing Target –>
<Id>0EA57D57-7837-4B51-A72C-E8B3F1322C07</Id>
<ScDatabase>liveweb</ScDatabase>
</Live>
<!– end custom publishing target additions (third location) –>
</Targets>
<ItemsRelationship>
<Type>Sitecore.Framework.Publishing.Data.ItemsRelationshipStore, Sitecore.Framework.Publishing.Data</Type>
<ConnectionName>Links</ConnectionName>
<FeaturesListName>ItemsRelationshipStoreFeatures</FeaturesListName>
</ItemsRelationship>
</Stores>
</Options>
</StoreFactory>
<StoreFeaturesLists>
<Options>
<FeatureLists>
<!–Source Store Features–>
<SourceStoreFeatures>
<ItemReadRepositoryFeature>
<Type>Sitecore.Framework.Publishing.Data.CompositeItemReadRepository, Sitecore.Framework.Publishing.Data</Type>
</ItemReadRepositoryFeature>
<TestableContentRepositoryFeature>
<Type>Sitecore.Framework.Publishing.Data.CompositeTestableContentRepository, Sitecore.Framework.Publishing.Data</Type>
</TestableContentRepositoryFeature>
<WorkflowStateRepositoryFeature>
<Type>Sitecore.Framework.Publishing.Data.CompositeWorkflowStateRepository, Sitecore.Framework.Publishing.Data</Type>
</WorkflowStateRepositoryFeature>
<EventQueueRepositoryFeature>
<Type>Sitecore.Framework.Publishing.Data.CompositeEventQueueRepository, Sitecore.Framework.Publishing.Data</Type>
<options>
<ConnectionName>master</ConnectionName>
</options>
</EventQueueRepositoryFeature>
<SourceIndexFeature>
<Type>Sitecore.Framework.Publishing.ItemIndex.SourceIndexWrapper, Sitecore.Framework.Publishing</Type>
</SourceIndexFeature>
</SourceStoreFeatures>
<!–Service Store Features–>
<ServiceStoreFeatures>
<ManifestRepositoryFeature>
<Type>Sitecore.Framework.Publishing.Manifest.ManifestRepository, Sitecore.Framework.Publishing</Type>
</ManifestRepositoryFeature>
<PublisherOperationRepositoryFeature>
<Type>Sitecore.Framework.Publishing.PublisherOperations.PublisherOperationRepository, Sitecore.Framework.Publishing</Type>
</PublisherOperationRepositoryFeature>
<PublishJobQueueRepositoryFeature>
<Type>Sitecore.Framework.Publishing.PublishJobQueue.PublishJobQueueRepository, Sitecore.Framework.Publishing</Type>
</PublishJobQueueRepositoryFeature>
<TargetSyncStateRepositoryFeature>
<Type>Sitecore.Framework.Publishing.TargetSyncState.TargetSyncStateRepository, Sitecore.Framework.Publishing</Type>
</TargetSyncStateRepositoryFeature>
<ActivationLockRepositoryFeature>
<Type>Sitecore.Framework.Publishing.InstanceActivation.ActivationLockRepository, Sitecore.Framework.Publishing</Type>
</ActivationLockRepositoryFeature>
</ServiceStoreFeatures>
<!–Target Store Features–>
<TargetStoreFeatures>
<IndexableItemRepositoryFeature>
<Type>Sitecore.Framework.Publishing.Data.Classic.ClassicIndexableItemRepository, Sitecore.Framework.Publishing.Data.Classic</Type>
</IndexableItemRepositoryFeature>
<ItemWriteRepositoryFeature>
<Type>Sitecore.Framework.Publishing.Data.Classic.ClassicItemRepository, Sitecore.Framework.Publishing.Data.Classic</Type>
</ItemWriteRepositoryFeature>
<MediaRepositoryFeature>
<Type>Sitecore.Framework.Publishing.Data.Classic.Repositories.ClassicMediaRepository, Sitecore.Framework.Publishing.Data.Classic</Type>
</MediaRepositoryFeature>
<TargetIndexFeature>
<Type>Sitecore.Framework.Publishing.ItemIndex.TargetIndexWrapper, Sitecore.Framework.Publishing</Type>
</TargetIndexFeature>
</TargetStoreFeatures>
<!–ItemsRelationship Store Features–>
<ItemsRelationshipStoreFeatures>
<DatabaseItemRelationshipRepositoryFeature>
<Type>Sitecore.Framework.Publishing.Data.Classic.ClassicItemRelationshipRepository, Sitecore.Framework.Publishing.Data.Classic</Type>
</DatabaseItemRelationshipRepositoryFeature>
</ItemsRelationshipStoreFeatures>
</FeatureLists>
</Options>
</StoreFeaturesLists>
</Services>
</Publishing>
</Sitecore>
</Settings>

https://gist.github.com/grant-killian/d2fe8d3e89c5d7b15f47464dd1809d62.js

Sitecore artifact table patch config

I’ve patched EventQueues several times through the years, so I saved this Gist to make it easier for future opportunities.  There’s not much new to share in terms of introducing why one does this, refer to this blog about Sitecore artifact tables (or the old reliable Sitecore CMS Tuning Guide). You also have to take care around the order of configuration file processing, so zzzzzArtifactTableRetention.config this sucker if you really must 🙂

Here’s the Gist – https://gist.github.com/grant-killian/ffa1e84770b10a90e2454e241986b911  and here’s the expanded XML:


<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/&quot; xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform"&gt;
<sitecore>
<scheduling>
<agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel">
<patch:delete />
</agent>
<agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" method="Run" interval="01:00:00">
<IntervalToKeep>06:00:00</IntervalToKeep>
</agent>
<agent type="Sitecore.Tasks.CleanupPublishQueue, Sitecore.Kernel">
<patch:delete />
</agent>
<agent type="Sitecore.Tasks.CleanupPublishQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
<DaysToKeep>7</DaysToKeep>
</agent>
</scheduling>
<databases>
<database id="master">
<Engines.HistoryEngine.Storage>
<patch:delete />
</Engines.HistoryEngine.Storage>
<Engines.HistoryEngine.Storage>
<obj type="Sitecore.Data.SqlServer.SqlServerHistoryStorage, Sitecore.Kernel">
<param connectionStringName="$(id)" />
<EntryLifeTime>7.00:00:00</EntryLifeTime>
</obj>
</Engines.HistoryEngine.Storage>
</database>
<database id="web">
<Engines.HistoryEngine.Storage>
<patch:delete />
</Engines.HistoryEngine.Storage>
<Engines.HistoryEngine.Storage>
<obj type="Sitecore.Data.SqlServer.SqlServerHistoryStorage, Sitecore.Kernel">
<param connectionStringName="$(id)" />
<EntryLifeTime>7.00:00:00</EntryLifeTime>
</obj>
</Engines.HistoryEngine.Storage>
</database>
</databases>
</sitecore>
</configuration>

Walkthrough of Solr Query Analysis for Sitecore

Anton Tishchenko wrote a good quick piece on “stemming” for Solr, and I wanted to build on what he shared.

Solr is generally way underutilized by the Sitecore implementations I’ve seen. It makes for plenty of territory for blogging, though, so maybe bit-by-bit the word will get out about what a powerful distributed system Solr really is.

Let me setup a specific example using Sitecore 9 update-2 with Commerce. I’ll use the CatalogItemsScope Solr core, but you could review most any Solr core with the standard Sitecore schema.

Let me pause here to explain where the default Sitecore Commerce Solr Configuration defines this search index, because it took some digging. In the  . . . SIF\Configuration\Commerce\Solr\sitecore-commerce-solr.json file you’ll find:

// The names of the cores to create
“Customers.Name”: “[concat(parameter(‘CorePrefix’), ‘CustomersScope’)]”,
“Orders.Name”: “[concat(parameter(‘CorePrefix’), ‘OrdersScope’)]”,
“CatalogItems.Name”: “[concat(parameter(‘CorePrefix’), ‘CatalogItemsScope’)]”,

Using a fresh IaaS install of Sitecore 9 with Commerce, I’ll go to the Solr admin interface. Select the CatalogItemsScope Solr core from the dropdown list on the left side navigation. Choose the “Analysis” option to access this powerful way of evaluating two key operations one does with Solr: indexing and querying.  Solr defines different sets of analyzers for these operations, sort of like how Sitecore exposes the httpRequestBegin pipeline or other extensibility points that projects are always customizing. For this exercise, I’ll focus on the Query operation. The documentation on this Analysis screen has a lot more information on this, if you’re interested.

Enter the search phrase “an AWESOME television!” in the textbox for the Field Value(Query), and then specify the text_general as the Fieldname to Analyse: step1

Solr is going to run “an AWESOME television!” through the analysis process and show the results as they progress through each step of that analysis. For this write-up, I’ll uncheck the “Verbose Output” checkbox — but definitely play around with that feature as it shows the offsets and ordinal positions as Solr works it’s magic through each transformation.

After clicking the blue “Analyse Values” button you’ll see output like the following:

step2

Each row of output starts with an abbreviation (ST, SF, etc). That’s the key to which process of the “analyzer” has run; the pipe-separated list to the right of the abbreviation shows the search phrase as it’s progressing through Solr’s transformation logic.

  1. ST is the StandardTokenizerFactory. I removes the “!” punctuation mark, but otherwise doesn’t make any changes to the search query.
  2. SF is the StopFilterFactory. This would remove words listed in the CatalogItemsScope\conf\stopwords.txt file. In this case, with an out-of-the-box Sitecore 9 Commerce installation, there are no stopwords specified. This doesn’t change our search query in any way.
  3. SGF is the SynonymGraphFilterFactory. This component reads a list of synonyms from CatalogItemsScope\conf\synonyms.txt . . . and “television” is one of the samples they provide in that file, so now our query includes those synonyms
    • synonyms.txt includes this entry for example purposes:
      • Television, Televisions, TV, TVs
  4. The final step, LCF, is to run through LowerCaseFilterFactory which is takes “AWESOME” to “awesome” to ensure case isn’t evaluated in the query.

To drive home the point of this example, change the Fieldname to “text_en” from “text_general” and click the blue “Analyse Values” button. The results are different because the text_general and text_en Solr fields have a different set of components defined for the Query operation. Specifically, text_en adds:

  • EPF (EnglishPossessiveFilterFactory)
  • SKF (KeywordMarkerFilterFactory)
  • PSF (PorterStemFilterFactory)

Here’s what the Solr Analysis screen looks like:

step3

There is a lot that could be said about all this, and I’ll probably build on this in a future blog post . . . but I want to return to Anton’s original point about the Porter stemmer and highlight how powerful the Porter stemmer can be. Solr’s text_general is significantly different than text_en, and hopefully I’ve shed light on precisely how they differ on the query side. The docs at http://snowball.tartarus.org/algorithms/porter/stemmer.html review this in some detail. I’ve also used this online Porter stemmer to quickly see how words are decomposed into their key fragments for search relevancy. You don’t really need that online stemming tool, however, since you now see how to use the Solr administrative UI with the standard “text_en” field to have your own Porter stemming sandbox.

Quick note on onPublishEndAsyncSingleInstance vs onPublishEndAsync

This is more a note for my benefit — for search index update strategies, the onPublishEndAsyncSingleInstance’ makes the ‘onPublishEndAsync’ a deprecated option.
The legacy onPublishEndAsync remains to ensure backwards compatibility, but from Sitecore 9.0 onward it’s the default index update strategy used by Sitecore.
With that said, it appears in Sitecore 9.0 update-2 there’s a major defect with OnPublishEndAsynchronousSingleInstanceStrategy. The ContentSearch.ParallelIndexing.MaxThreadLimit setting is ignored by the onPublishEndAsyncSingleInstance strategy — so incorrect thread limits can be used (slow perf!). Sitecore’s patch reference # 285903 can be requested through Sitecore Support to address this.
I suppose it’s a consequence of the new  onPublishEndAsyncSingleInstance not having a mature and well-tested codebase surrounding it (onPublishEndAsync has been around for ages!).