I got this error message when trying to run the Invoke-Sqlcmd powershell cmdlet against a server remotely using HP Server Automation (HPSA).  The full error message is:

Invoke-Sqlcmd : Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

If you search the error message online, you’ll discover some solutions like this, which involve editing XML configuration files.  As HPSA doesn’t use the regular powershell configuration files, I got stuck for a while trying to figure out which configuration file it does use.

As it turns out, the configuration file that HPSA uses is stored (by default) at the following location: C:\Program Files\Opsware\agent\bin\PsSasHost.exe.config.  The solution is to edit the configuration file to include the bolded section:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <startup useLegacyV2RuntimeActivationPolicy="true">

    <supportedRuntime version="v4.0"/>

    <supportedRuntime version="v2.0.50727"/>

  </startup>

</configuration>

You can also add the following code to a powershell script, which will make the necessary edits to the config file on the server.  This script will have to be run prior to the script which runs the Invoke-Sqlcmd commands.

$ConfigFile = "C:\Program Files\Opsware\agent\bin\PsSasHost.exe.config"
$Xml = [xml](Get-Content -Path $ConfigFile)
$Xml.configuration.startup.SetAttribute("useLegacyV2RuntimeActivationPolicy","true")
$Xml.Save($ConfigFile)

Problem solved!

N.B. The problem with the SQLPS module which causes this error to pop up is fixed in SQL 2016.  However, the 2016 SQLPS module comes with its own issues.  As has been documented in official bug reports (1, 2), installing the SQL 2016 version of the SQLPS module will break backwards compatibility with SQL 2014 instances even if you don’t load the 2016 SQLPS module.  Apparently, the 2014 SQLPS module exhibits the same behaviour when trying to manage SQL 2012 instances.  Well done Microsoft!

A vMSC – perhaps more commonly known as a “metro cluster” –  is an architecture in which individual vSphere clusters will be spread across multiple geographical sites.  Since a vSphere cluster requires shared storage to allow VM’s to migrate across hosts, in a vMSC environment this will mean that storage must be shared or replicated across the geographical sites.  As you might expect, this kind of architecture comes with a number of gotchas and limitations, especially around the configuration of the storage arrays.  For this reason, storage vendors who support vMSC architectures have released best practices documentation specifically for designing vMSC’s with their storage products.

If you’re designing a vMSC architecture using HPE 3PAR storage arrays, HPE have released best practices documentation entitled Implementing vSphere Metro Storage Clustering using HPE 3PAR Peer Persistence which is available via VMware KB255904, or directly (at the time of writing) at this URL.

One of the requirements specific to vMSC architectures – which is not correctly stated by this documentation (or by KB255904) – is the following:

Storage I/O Control (SIOC), and the I/O Metric of Storage DRS, are not supported in vMSC configurations.

Misleadingly, the documentation not only does not specifically mention this requirement, but it suggests that the requirement should be violated by enabling the I/O metric of Storage DRS in the following quote from page 29 under the heading “Managing VMware Storage DRS Settings”:  Storage DRS should be “Manual Mode”. In this mode, Storage DRS will make recommendations when thresholds for space utilization or I/O latency are exceeded.  I/O latency will only be considered by Storage DRS if the I/O metric is enabled, which is how this quote violates the requirement.

I was designing a vMSC architecture around 12 months ago when I stumbled across a different VMware article KB2042596, which was the first document I’d ever seen stating that SIOC and the I/O metric of storage DRS are not supported in a vMSC environment.  I was quite confused by this, because I’d read through several VMware and vendor-specific documents on the subject and this was the first time I’d seen this requirement mentioned; in an obscure article I found by accident when looking for something else.  VMware’s original best practice document on the subject, entitled VMware vSphere Metro Storage Cluster Case Study from May 2012 (which is excellent documentation in all other respects), didn’t mention the requirement whatsoever.

In July 2015 I hoped to get some clarification on the subject by escalating within VMware through our VMware Technical Account Manager, and within HPE through internal channels, as it seemed documentation authors had either forgotten or were unaware of this requirement.  VMware said they would update the documentation as appropriate, and I’m now pleased to see that most recent version of VMware’s vMSC case study (now entitled VMware vSphere Metro Storage Cluster Best Practices,  but which is the same document updated for vSphere 6.0) prominently notes under the Storage DRS section on page 16 that neither SIOC or the I/O metric of storage DRS are supported.

Unfortunately, my efforts towards a correction of the 3PAR best practices were not as fruitful, and I’m disappointing to find that as of the January 2016 version of best practice documentation (linked earlier), the requirement is still not mentioned.  I will make another effort to get this documentation corrected.

Regardless, I hope that this post ensures at least some architects designing vMSC environments with HP 3PAR storage arrays will be aware of the correct and supported settings for Storage I/O Control and Storage DRS, if they weren’t already.

Resources:

VMware KB2055904 – Implementing vSphere Metro Storage Cluster (vMSC) using HP 3PAR Peer Persistence

HPE Technical Whitepaper – Implementing vSphere Metro Storage Cluster using HPE 3PAR Peer Persistence

VMware Technical whitepaper – VMware vSphere Metro Storage Cluster Recommended Practices

VMware KB2042596 – vSphere Storage DRS or vSphere Storage I/O Control support in a vSphere Metro Storage Cluster environment