XESmartTarget issue with dbatools SMO version

[davidacrook]

Steps to Reproduce

Start-DbaXESmartTarget -SqlInstance "SQL2014" -Session TestSession -Responder $response -NotAsJob

Expected Behavior

Start collecting extended event data. Immediately encounters dll version error.

WARNING: [09:57:23][Start-SmartFunction] Could not load file or assembly 'Microsoft.SqlServer.XE.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file
specified.

I've tried this on two different computers with the same error. It appears that the Microsoft.SqlServer.XE.Core dll shipped with dbatools is different than the one expected by XESmartTarget.

Environmental data

• PowerShell:

Name Value

---

PSVersion 5.1.14409.1018
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14409.1018
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

• • SQL Server:

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

[wsmelton]

This appears to be because we ship a newer version of SMO than what the XESmartTarget was compiled to handle.

https://github.com/spaghettidba/XESmartTarget/blob/a75354b409aeb655a7c4714ecdbc64813f3ba923/XESmartTarget/App.config#L8-L10

[wsmelton]

We won't directly be able to fix this until Gianluca updates XESmartTarget. Reverting the SMO file might be a workaround but it could cause adverse effects on other commands that could have become dependent upon the newer version (not sure we have that).

[spaghettidba]

Sorry about this. I suppose that updating the App.config file could work. Have you tried this?

[wsmelton]

We do not have the app.config in the module for these DLL files.

[spaghettidba]

Since there is a single app.config per application, you would have to merge my app.config into the one of the main application. But, in this case, it is not clear which app.config gets loaded for powershell.exe (if any). Makes it a difficult case.

I suppose that I could update smo in my project and release a new version and, a the same time, push an update to the DLL for dbatools. I am not sure this will work though.

[potatoqualitee]

im surprised that your app cares, is there a place where you set it, @spaghettidba ?

[wsmelton]

We don't include the app.config file, all we have is the dll file. The app.config @potatoqualitee would be required when it is compiled (C# think) which you have to hard code the version of SMO you are working against in order to stabilize the functionality. (SSMS does the same thing.)

My understanding of what I've looked at is we would need to fork his repository, load our version of SMO and set the version of it in the app.config for the project in VS. Then build the project to get a new DLL against the version of SMO we ship.

[potatoqualitee]

that is useful, thank you shawn

[spaghettidba]

That or we would have to keep our versions of SMO in sync. Would that work?

[potatoqualitee]

How can we fix this? Updating our SMO is a pain and done with each RTM of SQL Server. I'd like to get a resolution this week if possible, thank you for pinning @wsmelton

[spaghettidba]

OK, I did some research and I'd like to share the results with you. I am not a .NET guru, so please bear with me if I'm writing something that you already know.

First, XESmartTarget is already built using the latest and greatest version of SMO, but it is not picking up the correct version at runtime. Let's see why.

The latest version of the SMO nuget package is 140.17283.0, which contains the version 14.100.0.0 of the .dll files. At compile time, the compiler looks for the dll to build the project and finds it in the packages folder, so it builds using version 14.100.0.0 and writes the metadata to the output dll (XESmartTargetCore.dll).

At runtime, the XESmartTargetCore.dll gets loaded and when one of its dependencies get referenced, the runtime needs to locate it and load it. The runtime always tries to locate the exact version that was used to build, so, in this case 14.100.0.0. Unfortunately, that exact specific version will not always be available, so the only thnig you can do is override the behavior of the runtime by specifying which version to load and map to the one it is looking for. This is done with assembly mapping redirection, which can be done by adding some entries to the file app.config in the application folder.

This is an example:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.XE.Core" publicKeyToken="89845dcd8080cc91" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-14.100.0.0" newVersion="14.100.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.XEvent.Linq" publicKeyToken="89845dcd8080cc91" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-14.100.0.0" newVersion="14.100.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

This file tells the runtime to map version 14.100.0.0 and all previous versions to version 14.100.0.0. This seems pretty dumb and unneeded, but without this file, XESmartTarget cannot load the dlls listed in the file, while all other dlls in SMO get loaded without issues. Those two dlls are platform specific, so they don't get added to the GAC and don't get added to the project reference when you add a NuGet package. I have no idea why this is.

Long story short: without redirecting the runtime to the specific version of those two dlls, there is no way to make this work. The config file must go in the same folder as the executable and have the same name. Libraries cannot control how dlls are loaded, but it is up to the application to control this. In this case, it should be controlled by powershell.exe, which should have its own powershell.exe.config.

This article shows how to use alternative methods to do the mapping, but I have no idea how to try them or include them in dbatools.

I am sorry, but it seems like we don't have many options.

[wsmelton]

How was this working before then?

[spaghettidba]

That's an excellent question. Maybe the version of SMO shipped with dbatools has changed?

[spaghettidba]

I found this solution for assembly binding redirection in powershell: https://stackoverflow.com/questions/24181557/powershell-config-assembly-redirect

Maybe this will help?

[wsmelton]

We ship version 12 of SMO now (the version sequence changed so it no longer matches up to the version number used with SQL Server).

[potatoqualitee]

this seems to suggest that xesmarttarget will only work on a specific subset of computers with SMO version X on it right?

[potatoqualitee]

@spaghettidba - will you be trying that redirect? it looks cool

[spaghettidba]

I can try. I'll get back to you on this ASAP

[potatoqualitee]

yess ty!

[davidacrook]

Thanks. I pulled the changes and now I'm getting a different error referencing the XEStore dll:

Get-DbaXESession -SqlInstance "DESKTOP-88EOK30"

WARNING: [17:57:59][Get-DbaXESession] Error occured while establishing connection to DESKTOP-88EOK30 | Cannot find type [Microsoft.SqlServer.Management.XEvent.XEStore]: verify that the ass
embly containing this type is loaded.

[potatoqualitee]

yah i was able to repro on my sql2012 server, i closed my other PR, it'll be a bit before i can figure this out, likely 🤔

[potatoqualitee]

yo @sql-williamd - the pin is a repo wide pin, not personal so it removes it from our pin :/

@davidacrook - can you try with the latest version? i'm leaning 30% that it'll work? i just updated all the SMO

[potatoqualitee]

i was able to get this to work on two machines - one fully loaded with tons of sql stuff and one that is basically boot up, install dbatools, and test.

that other one doesnt haved a sql server it can access easily but i tried to do what the internal command does and it seems to work 👍

[davidacrook]

Thanks for all the work. Creating a smart target session now works: Start-DbaXESmartTarget -SqlInstance "." -Session TestXESession -NotAsJob

However, when trying to create a response session with New-DbaXESmartQueryExec, there is an error message: "WARNING: [18:33:34][New-DbaXESmartQueryExec] Could not load XESmartTarget.Core.dll | Cannot find path 'C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.811\XESmartTarget\XESmartTarg
et.Core.dll' because it does not exist."

It just looks like the "bin" folder was left out of the path name when loading the dll. Hopefully a simple fix.