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.

Advertisements

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

Sitecore Commerce is an interesting landscape — it’s never a dull moment.  After recently swapping the backing store from Azure SQL to SQL Server (due to an interesting Inventory gotcha with the Reference Storefront that I’ll maybe share at some other time), I’m finding nooks and crannies of configuration I never knew existed with the Commerce product until now.

After I migrated to Azure IaaS SQL Server VMs from Azure SQL, I thought I had everything tidied up.

  • Commerce Server Manager references?  ✔
  • Sitecore application connection strings?  ✔
  • Bootstrap configuration (I posted this gist on manipulating those files to make this easier)? 

I updated the Azure SQL database credentials to prove that I had no lingering connections to Azure SQL.  I encountered an exception at Sitecore start-up related to initialization of the profile service, however, and had to start digging.  CommerceProfileSystemException was the exception type and the stacktrace started as follows:

Exception type: CommerceProfileSystemException 
 Exception message: Failed to initialize profile service handle.
 at CommerceServer.Core.Runtime.Profiles.ProfileContext..ctor(String profileServiceConnectionString, String providerConnectionString, String bdaoConnectionString, DebugContext debugContext)
 at CommerceServer.Core.Runtime.CommerceContextFactory.CreateProfileContext()
 at CommerceServer.Core.Runtime.CommerceContextFactory.get_ProfileContextSingleton()
 at CommerceServer.Core.Runtime.Profiles.CommerceProfileModule.get_ModuleProfileContext()
 at CommerceServer.Core.Runtime.Profiles.CommerceProfileModule.get_ProfileContext()

The Commerce Server Manager encapsulates the connection strings, and I thought I had them all updated to the SQL Server VM equivalents, even going so far as to inspect MSCS_Admin in SQL Server with a query like this:

SELECT [i_ResourceID]
 ,[s_PropertyName]
 ,[s_Value]
 FROM [MSCS_Admin].[dbo].[ResourceProps]
 where f_IsConnStr=1
 ORDER BY 1

While interesting to find where this information is stored (may or may not be permanent, though — tough to tell with Commerce!), this output didn’t shed light on what might be going on, though:

Eventually I stumbled across some 13 year old documentation on Commerce Server discussing updating the ProfileService data source in some detail (http://microsoft.public.commerceserver.general.narkive.com/NPLMLusv/commerce-2002sp3-on-windows-2003-can-t-change-profiles-data-source).  It turns out, this 13 year old solution was completely applicable to my 2017 Sitecore Commerce predicament.

Succinctly, within Commerce Server manager you should do the following:

  1. Expand the Commerce Server “Global Resources” node, then “Profiles” node, then “Profile Catalog” node, then “Data Sources” node, and finally expand the “ProfileService_SQLSource” node
  2. Click on the Partitions node:
  3. In the right pane, there’s a SQLSource element you right-click and choose “Properties”
  4. Select the Partitions Tab, then “Edit” the connection string
  5. Make your connection string modifications here.  This is where my elusive reference to Azure SQL was hiding and causing Sitecore to fail to initialize.

The more work I do with Sitecore Commerce, the more I’m appreciating the value of the older documentation targeting previous editions of the product.  The catch is, it’s not 100% relevant to the modern experience with Sitecore Commerce . . . and knowing what is and isn’t applicable to the Sitecore Commerce 8.2.1 world is a challenge.  I think we’re getting there, a little bit at a time!

Encrypting Sitecore connection strings for Sitecore Commerce, Azure SQL, and beyond

There’s been a lot of Sitecore Commerce on my plate this Summer, and sadly one limitation of using that product for some customers is the requirement for SQL Server authentication instead of Active Directory and Windows Auth; I won’t get into why they need SQL auth at this point, but trust that in many use-cases this is a necessity.

In an effort to always deliver a secured platform for customers, at Rackspace we encrypt the App_Config/connectionStrings.config file to avoid having plaintext usernames and passwords on disk.    This is a link to our Rackspace GitHub “gist” performing such encryption with the ASP.Net tool aspnet_regiis.exe.  The logic is also there to un-encrypt, in case that’s necessary.

Encryption success
You can update the $configLocation variable at the top of the script to point to where your Sitecore installation is located; you then run this script using PowerShell, and you’ll get an output like this.

Once you’ve run the script, your connectionStrings.config file will resemble this:

Before you get too excited, for Sitecore Commerce in the current incarnation, there are several other plaintext passwords on disk in the \CommerceAuthoring\wwwroot\data\Environments and related .json files for both SQL and Sitecore.  The PowerShell I’ve shared doesn’t address those areas.  The Sitecore Commerce documentation does a good job of cataloging where to find these references, at least, but this still leaves a lot to be desired in terms of security.

I’m not going to go too far down this path, since I mostly wanted to post the PowerShell we use to automate SQL Server connection string encryption.  This technique can be useful for a variety of projects, not just for Sitecore Commerce — although this is the use case we’re repeatedly seeing right now.  If I have time, I’ll share some other Sitecore Commerce tips around Azure SQL friendly deployments (Sitecore’s documentation is a decent start, but lacking in some respects).

Here’s the script to encrypt/decrypt those Sitecore connectionStrings.config file:

Sitecore Session Persistence Notes

I’ve neglected this blog of late, being focused on a number of “not easily blogged about” scenarios across several Sitecore projects.  It’s too bad, because the work is very interesting, but it doesn’t lend itself to a page or two write-up with a digestible take-away for the general Sitecore community out there.

I do want to keep in the habit of blogging, though, so I’m going to mention this ongoing discussion I’ve been a part of about session management with regards to Sitecore.  There are a few options for managing HTTP session state with Sitecore covered in https://doc.sitecore.net/sitecore_experience_platform/setting_up_and_maintaining/xdb/session_state/session_state: SQL Server, MongoDB, and Redis.  Those three technologies are really just the tip of the mountain, as implementation details for each can get quite detailed.  For the discerning Sitecore implementation, it can be useful to understand the nuances of each session state provider.  While not an exhaustive look at any one of these solutions, I wanted to post some notes on each one given the current state of Sitecore architecture (June 2017):

SQL Server

This is often the default session provider we gravitate to.  The SQL Server “Boost” script from Sitecore is something we’ve used on implementations (see “Optimize SQL Server performance” on that link), but it is not without it’s rough edges (see our Rackspace write-up on how to alter permissions so TempDB is reliably available across service restarts).

You’ll notice the approach for improving SQL Server performance with session state is all about getting session state “in-memory” to the furthest extent possible.  Remember this when we examine the other two providers below . . .

I will say that, generally speaking, SQL Server is easy to administer as it’s a well-known technology and updating it, scaling it, managing fail-overs, etc is simple compared with the alternatives.  SQL Server has been part of the Windows dev stack for ages, now, so it’s often the default session provider one gravitates to.

MongoDB

With MongoDB serving as the persistence layer for Sitecore’s xDB, it became a fully supported and viable option for HTTP session state with Sitecore at the same time.  The comparative performance between MongoDB and SQL Server is up for debate (Redis too, for that matter!), and it usually comes down to testing based on how the specific implementation is using session with Sitecore etc; I’m not going to hazard any generalizations on relative perf, as that’s not really the point of this post.

Instead, I’d like to point out how MongoDB does not come in just a single flavor.  The two most common flavors, or “storage engines,” are MMAP and WiredTiger, but there are still others designed to serve specific use cases.  Take, for example, the Percona Server for MongoDB hosted by ObjectRocket that has a posted option for the RocksDB storage engine.  RocksDB with MongoDB may not be a great fit for Sitecore session state (RocksDB is tuned for write-heavy work loads — and, in some cases, if you’re making extensive use of TTL indexes for Sitecore then RocksDB fits those scenarios in certain appealing ways), but it does open the door to MongoDB being more than just a one-size-fits-all data repository (read more about RocksDB and it’s Facebook pedigree here).  One MongoDB storage engine option that is easily overlooked is for WiredTiger “in-memory” that will force data to be stored in RAM . . . and this is perfect for HTTP Session State for most Sitecore builds.

In fact, if you consider the SQL Server “boost” approach that uses TempDB to store session state for Sitecore . . . WiredTiger “in-memory” is attacking the problem from the same direction.  Store everything in RAM!  This is why one must be cautious with general comparisons between SQL Server and MongoDB, the devil is always in the details: a far better comparison would be “boosted” SQL Server for Sitecore using TempDB vs MongoDB WiredTiger “in-memory” storage engine.  And note the network latency . . . and the size of the session objects . . . and you’re getting the point, I trust.  To really answer the SQL Server vs MongoDB question for Sitecore sessions, one has to develop a matrix of performance evaluations and level assumptions across the board.  “It depends” is the only honest answer that doesn’t come with a list of caveats.

If you’re curious on this MongoDB topic for your project, go to http://objectrocket.com/docs/mongodb_plans.html and spin up a WT 3.2 storage engine plan for 5 GB of storage (this allows 1.5 GB for RAM).  1.5 GB for RAM is going to be overkill for most small/medium Sitecore implementations — but again, you’ll want to test with your specific session data set to see!  Furthermore, network latency of 10 ms or less is going to help make the most of an ObjectRocket hosted MongoDB service like this — otherwise, the network latency may not make it worth the money.  Let me know if you pursue this with ObjectRocket, as there are some benchmarking measures we want to do but we haven’t had a real implementation to try it out on.  So if you feel like being a guinea pig, please let me know at grant.killian [at] rackspace.com.  It would be great to have real world metrics to prove this all out.

Redis

If the way to get the best session management perf out of SQL Server and MongoDB is to find in-memory solutions, Redis looks like the slam dunk since it’s just an in-memory storage solution.  We find most clients aren’t interested in managing Redis infrastructure, so again a hosted option such as ObjectRocket has appeal.

Sitecore relies on the StackExchange.Redis assembly, which doesn’t support Redis Sentinel — it’s a bit of a saga at https://github.com/StackExchange/StackExchange.Redis/pull/406;  therefore there’s not a great high availability story with the self-hosted Redis and Sitecore right now.  How concerned one should be with HA of fairly transient HTTP Session State for Sitecore, however, is an open question.  I usually wouldn’t worry about it too much.  Honestly, Redis is a technology that we’re just now starting to get really serious about at Rackspace so our sophistication in this space will improve dramatically in the months to come.  Between Azure Redis and all the Sitecore PaaS movement we’re seeing, it’s become a key player in a lot of Sitecore architectures.

xDB Reporting Database Rebuild Help

I’ve created something like this every time I need to rebuild the Sitecore “reporting” database (this link covers the basic process), this time I’m posting it online so I can re-use it next time around!

This is the script for generating the T-SQL that’s required to complete step #3 in the write-up when you’re following the “Rebuild Reporting Database” instructions:

“In the Rebuild Reporting Database page, when you see Waiting to receive to data status, copy the following marketing definition tables from the primary to the secondary reporting database”

I have written the SQL several times to do this, but this time I took a run at DRY (don’t repeat yourself) to script this SQL out.  Alas, I think my T-SQL comes in at 40+ lines of code versus the raw SQL to run which is just 35 lines and much easier to read, in my opinion.

Either way, you can pick which you prefer as I’ll share them both here

First, the plain vanilla SQL commands for copying those database tables:

INSERT INTO target_Analytics.dbo.CampaignActivityDefinitions
         SELECT source_Analytics.dbo.CampaignActivityDefinitions.*
         FROM  source_Analytics.dbo.CampaignActivityDefinitions ;

INSERT INTO target_Analytics.dbo.GoalDefinitions
         SELECT source_Analytics.dbo.GoalDefinitions.*
         FROM  source_Analytics.dbo.GoalDefinitions ;

INSERT INTO target_Analytics.dbo.OutcomeDefinitions
         SELECT source_Analytics.dbo.OutcomeDefinitions.*
         FROM  source_Analytics.dbo.OutcomeDefinitions ;

INSERT INTO target_Analytics.dbo.MarketingAssetDefinitions
         SELECT source_Analytics.dbo.MarketingAssetDefinitions.*
         FROM  source_Analytics.dbo.MarketingAssetDefinitions ;

INSERT INTO target_Analytics.dbo.Taxonomy_TaxonEntity
         SELECT source_Analytics.dbo.Taxonomy_TaxonEntity.*
         FROM  source_Analytics.dbo.Taxonomy_TaxonEntity ;

INSERT INTO target_Analytics.dbo.Taxonomy_TaxonEntityFieldDefinition
         SELECT source_Analytics.dbo.Taxonomy_TaxonEntityFieldDefinition.*
         FROM  source_Analytics.dbo.Taxonomy_TaxonEntityFieldDefinition ;

INSERT INTO target_Analytics.dbo.Taxonomy_TaxonEntityFieldValue
         SELECT source_Analytics.dbo.Taxonomy_TaxonEntityFieldValue.*
         FROM  source_Analytics.dbo.Taxonomy_TaxonEntityFieldValue ;

And now, here’s the T-SQL attempt to “simplify” the process of creating a script like the above for future projects (yet I prefer it less to the brute force approach):

The advantage to the below is you set your source and target variables to the names of the SQL Server databases, and then you’re all set.

DECLARE @source VARCHAR(100)
DECLARE @target VARCHAR(100)
SET @source = 'source_Analytics'
SET @target = 'target_Analytics'

SET NOCOUNT ON
--List approach will work in SQL Server 2012 only
DECLARE @ListOfTables TABLE(IDs VARCHAR(100));
INSERT INTO @ListOfTables
VALUES('CampaignActivityDefinitions'),
  ('GoalDefinitions'),
  ('OutcomeDefinitions'),
  ('MarketingAssetDefinitions'),
  ('Taxonomy_TaxonEntity'),
  ('Taxonomy_TaxonEntityFieldDefinition'),
  ('Taxonomy_TaxonEntityFieldValue');

SET ROWCOUNT 0
SELECTX NULL mykey, * INTO #mytemp FROM @ListOfTables
DECLARE @theTable varchar(100)
DECLARE @sql varchar(1000)

SET ROWCOUNT 1
UPDATE #mytemp SET mykey = 1

WHILE @@rowcount > 0
BEGIN
    SET ROWCOUNT 0
    SELECT @theTable = (SELECT IDs FROM #mytemp WHERE mykey = 1)
    PRINT 'INSERT INTO ' + @target + '.dbo.' + @theTable + '
         SELECT ' + @source +  '.dbo.' + @theTable + '.*
         FROM  ' + @source + '.dbo.' + @theTable + ' ;'
     --use 'EXEC to run the dynamic SQL, instead of PRINT, 
     --if you're feeling brave

    DELETE #mytemp WHERE mykey = 1
    SET ROWCOUNT 1
    UPDATE #mytemp SET mykey = 1
END
SET ROWCOUNT 0
DROP TABLE #mytemp

 

Sitecore Publishing Data Through the EventQueue

Our Challenge

We work with a variety of Sitecore implementations at Rackspace, and sometimes we need to do a surgical task without requiring customers to install anything in particular, trigger app pool recycles, or other changes to an environment.  One such example came up today.

We needed to provide insight into a recent Sitecore publishing operation, but the customer didn’t have logging or other instrumentation showing what was published by whom, published where, and when.  While there may be Sitecore Marketplace modules and other solutions to this sort of challenge, they require customizations or at least package installations by the customer — and none of them can go back in time to answer the question about “who published XYZ in the last few hours?”

Preferably, by using the dedicated Publish log set to INFO in Sitecore, one can get at a ton of handy publishing information . . . and this is our general recommendation for implementations (provided logs aren’t retained for so long that they cause a space concern on disk).  In this case, however, the publish log wasn’t an option and so we had to get creative.

Our Solution

For this scenario, knowing the customer is using a global publishing pattern for Sitecore that we like to employ at Rackspace, we turned to the Sitecore EventQueue since we couldn’t rely on the Publish log.  Even though the EventQueue is mainly about propagating events to other Sitecore instances, we can take advantage of the fact that publishing events are some of those operations that run through the EventQueue.  We can run a query like the following to get a rough handle on what has been recently published:

SELECT Created as [Publish Timestamp]
        --, Username as [Initiator] -- not for distribution!
        , CAST(SUBSTRING(HASHBYTES('SHA1', UserName),1,3) as bigint)  as [Hashed Initiator]
        , IIF(CHARINDEX('"TargetDatabaseName":"pubTarget1"', InstanceData)>0,'1','0') AS [To 1]
        , IIF(CHARINDEX('"TargetDatabaseName":"pubTarget2"', InstanceData)>0,'1','0') AS [To 2]
        , IIF(CHARINDEX('"TargetDatabaseName":"pubTarget3"', InstanceData)>0,'1','0') AS [To 3]
        , IIF(CHARINDEX('"TargetDatabaseName":"pubTarget4"', InstanceData)>0,'1','0') AS [To 4]
       , (LEN(InstanceData) - LEN(REPLACE(InstanceData, '"LanguageName"', ''))) / LEN('"LanguageName"') as [# of Langs] --this is a proxy for how heavy a publish is
        , InstanceData AS [Raw Data]
FROM EventQueue
WHERE EventType LIKE 'Sitecore.Publishing.StartPublishingRemoteEvent%'
ORDER BY Created DESC

Here’s a glance of what the data might look like . . .

pubDump

Explanations & Caveats

Regarding the query, we use an SHA hash of the Sitecore login instead of showing the login (Username) in plain text.  A plain text username could be a security concern, so we don’t want to email that or casually distribute it.  Instead of generic “pubTarget1” etc, one should name specific publishing targets defined in the implementation.  This tells us if a publish went out to all the targets or just selectively.  Our use of “# of Langs” is a way of seeing how much data went out with the publish . . . it’s not perfect, but in most cases we’ve found counting the number of “LanguageName” elements in the JSON to be a reasonable barometer.  When in doubt, the Raw Data can be used to get at lots of other details.  I’d use a JSON viewer to format the JSON; it will look something like:

{
  "ClientLanguage": "en",
  "EventName": "publish:startPublishing",
  "Options": [
    {
      "CompareRevisions": true,
      "Deep": false,
      "FromDate": "\/Date(1464134576584)\/",
      "LanguageName": "de-DE",
      "Mode": 3,
      "PublishDate": "\/Date(1464183788221)\/",
      "PublishRelatedItems": false,
      "PublishingTargets": [
        "{8E080626-DDC3-4EF4-A1A1-F0BE4A200254}"
      ],
      "RecoveryId": "cd00ba58-61cb-4446-82ae-356eaae71957",
      "RepublishAll": false,
      "RootItemId": "afde64e9-7176-43ad-a1f2-89162d8ba4cb",
      "SourceDatabaseName": "master",
      "TargetDatabaseName": "web"
    }
  ],
  "PublishingServer": "SCMASTER-1-CLONE",
  "StatusHandle": {
    "instanceName": "SCMASTER-1-CLONE",
    "m_handle": "e3b5831f-2698-41b5-9bf9-3d88a5238e5a"
  },
  "UserName": "sitecore\\notalegituser"
}

One key caveat to this SQL approach is that data in the Sitecore EventQueue doesn’t remain for long.  Depending on how one tunes their Sitecore.Tasks.CleanupEventQueue agent, EventQueue records might be available for only a few hours.  It’s certainly not a good source for long term publishing history!  This is another reason why using the Sitecore Publishing log is really the way to go — but again, that text log isn’t available to us in this scenario.

Basic Azure Benchmarking for Sitecore

We used this approach to run through some database performance benchmarking for Azure for a real production Sitecore implementation (Sitecore version 8.0).  The idea is to use real resource utilization statistics to estimate DTU requirements and Azure SQL database tiers for if/when this workload shifts to Azure.  This was for a real site, not LaunchSitecore or JetStream or Habitat 🙂

We ran the tests for an hour, then six hours, then at different times of the day . . . yet the results were consistent each time.  The Standard S2 Tier was the recommendation based on our observed workload for every database (Core, Master, and Web databases).  This implementation used MongoDB for session state, however, so this doesn’t include SQL Server session state into the calculation.

There were usually around 9% of the time when the DTUs would spike and exceed the Standard S2 capacity, so I’m a bit concerned and curious about what that would translate into if we were running 100% in Azure (probably slow perf for those times — which roughly appeared to align with Sitecore publishing — which means the Standard S2 Tier might not really cut it).  We could see Content Authors complaining of slow perf, stalled content updates, etc . . . so this is just a starting point.