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

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

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

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

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

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

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

FailoverPartner

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

DRCommerce

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

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

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

admindbps

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

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

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

RegWork

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

RegWork2

Yes, let the Registry edits flow through you!

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

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

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

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

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

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

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

Advertisements

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

Powershell installation of Sitecore Performance Counters

Today was the day I bit the bullet and automated this bit of administrative work that we do for all our Sitecore projects. I think it was the fresh crop of 9 new Sitecore IIS nodes that needed this attention that was the final impetus.

Sitecore Performance Counters

This blog reviews all the perf counters Sitecore makes available.  The documentation is a bit thin from Sitecore, since these counters were added to the product several years ago and they apparently don’t get much attention.  They do require special effort to setup as part of your IIS environment, too, so it’s easy to overlook them.  They’re still very handy, though!  A future blog post could discuss the ones most worthy of attention (in my humble opininion).

You can download the .zip file with an installer for the Sitecore specific performance counters at http://sdn.sitecore.net/upload/sdn5/faq/administration/sitecorecounters.zip.  These files are what you’ll end up with:

counters.JPG

The .EXE runs through all the .XML files in the directory that enumerate perf counters relevant to Sitecore, and adds the counter to the computer.  It’s easy.

Scripted Installation

One would typically click the SitecoreCounters.EXE, press the {enter} key, and then the performance counters would quickly install to the server.  Doing this for many servers, or as part of an automated provisioning effort, could be tedious . . . we actually invoke this via remote magic with Ansible (but that’s not the focus of this blog post).  I do want to go over how we automated this, however, since it wasn’t the smooth sailing I expected it to be.

I’ve posted a Powershell baseline for installing Sitecore perf counters to this github gist.  It was straight-forward Powershell until I found the .EXE was running properly, but no performance counters were actually being added.  I had to look at the decompiled .EXE source to figure out why.  Here’s the Main method of the .EXE; I’ve bolded the instruction that was giving me problems:

        private static void Main(string[] args)
        {
            Console.WriteLine("Press enter to begin creating counters");
            Console.ReadLine();
            Console.WriteLine("Creating Sitecore counters");
            string[] files = args;
            if (args.Length == 0)
            {
                files = Directory.GetFiles(Environment.CurrentDirectory, "*counter*.xml");
            }
            foreach (string str in files)
            {
                new Counters(str).Execute();
            }
            Console.WriteLine("Done");
            Console.ReadLine();
        }

The “Environment.CurrentDirectory” in Powershell is by default something like C:\Users\UserName, but my Powershell was using a staging location to download and unzip the perf counter executable and related xml:

$zipFileURI = "https://your.cdn.with.the.Sitecore.zip.resources/sitecorecounters%207.5.zip"
$stageFolder = "C:\staging" 
if( !(test-path $stageFolder) )
{
    mkdir $stageFolder
}
$downLoadZipPath = $stageFolder + "/SitecoreCounters.zip"  
Invoke-WebRequest -Uri $zipFileURI -OutFile $downLoadZipPath
Add-Type -AssemblyName System.IO.Compression.FileSystem
[System.IO.Compression.ZipFile]::ExtractToDirectory($downLoadZipPath, $stageFolder)

Based on what I saw in Reflector, I needed to change my staging directory or to assign the Environment.CurrentDirectory to be $stageFolder in order for the perf counters to properly be installed to the new environment.  I really wanted to use a configurable location instead of some default Powershell directory, so I sifted through Powershell documentation and figured out the magic API call was [System.IO.Directory]SetCurrentDirectory; this example makes it apparent:

Write-Output "Default directory:"
 [System.IO.Directory]::GetCurrentDirectory()
 [System.IO.Directory]::SetCurrentDirectory($stageFolder + "\sitecorecounters 7.5")
 Write-Output "Directory changed to:"
 [System.IO.Directory]::GetCurrentDirectory()

With that in place, the context of the Sitecore perf counter .EXE was set properly and the program could locate all the counters defined in the XML files.

The end result is our new Sitecore specific perf counters are ready:

counters2.JPG

You will need to recycle the Sitecore App Pool in IIS for this to take effect, something like this should suffice:

$theSite = "Cool Sitecore Site"
$thePool = (Get-Item "IIS:\Sites\$theSite"| Select-Object applicationPool).applicationPool
Restart-WebAppPool $thePool