DEV Community: Jānis Veinbergs

Using Dataverse Service Client to connect to OnPrem Dynamics 365 CRM (From .NET 6+)

Jānis Veinbergs — Wed, 30 Aug 2023 11:51:11 +0000

Can you use new Dataverse ServiceClient
(Microsoft.PowerPlatform.Dataverse.Client) with .NET 6+ for for your console app to connect to on-premises instance of Dynamics 365 CRM? Yes you can! Here is the connection string:

AuthType=OAuth;Url=https://org.crm.example.com/;Username=user@example.com;Password=<pw>;RedirectUri=http://localhost:54321;AppId=174697c7-4ec8-401a-88ce-e6af4d05b6dd;LoginPrompt=Auto

Well, after you appropriately configure ADFS and CRM, that is.

Intro and Supportability

There are 2 Client SDKs that abstract API calls:

Long time CRM Service Client SDK (Microsoft.CrmSdk.XrmTooling.CoreAssembly) windows-only, supports .NET Framework 4.6.2+, but not .NET Core world, thus not cross-platform. Still supported.
Dataverse ServiceClient (Microsoft.PowerPlatform.Dataverse.Client (DVSC)) - .NET 6, cross platform. Moving away from SOAP, but still uses it. No guidance from Microsoft to use for on-premises, so at the time of writing can be considered unsupported. Transition apps to Dataverse ServiceClient article specifically has a note for on-premises clients that says: > Leave your application projects and code as is. Continue using the Microsoft.CrmSdk.CoreAssemblies NuGet package and CrmServiceClientclass. However, plan to update your projects from using any custom service clients to instead use the CrmServiceClient or ServiceClient in the near future. See the planned timeline for 2011 SOAP endpoint shutdown below.

So are we stuck with .NET Framework for on-premises client applications?

DVSC source has bits and pieces, logic and variables like isOnPrem that indicates that maybe there is light at the end of the tunnel? And the note you just read actually says "... plan to update ... to instead use the CrmServiceClient or ServiceClient in the near future.". But maybe that "plan to update" means only plan to update the respective package, when they will get rid of all the SOAP requests in both and use WebAPI purely.

However it turns out, it is possible to connect to on-premises instance with DVSC if you are using ADFS2019. And in a different way, if you are using ADFS2016.

I may note that whatever is not officially documented may be considered an unsupported scenario. Things to consider:

For Online, Microsoft has so tightly integrated into Azure... I personally don't expect much changes for on-premises. And we really haven't seen much love there lately.
With these changes we are actually moving in a direction where Microsoft stands today: MSAL. So would be actually easier to move to online afterwards.
Nothing unsupported is being done for CRM/ADFS side configuration. Sans 1 thing to overcome current bug in DVSC that will hopefully be fixed.
Worst that could happen is some new version breaks something for on-premises and no one cares to fix it and we are stuck with older version of DVSC. It is a valid concern, of course, as we may be left stranded with some known vulnerabilities. But for me this is a low probability because of the plan to update note. So your decision whether to use .NET 6/DVSC or not in a production environment.

Configuring CRM/ADFS 2019

You must have: Windows Server 2019+/ADFS 2019+
KB4490481 must be installed. Required for MSAL support.
Configure the Microsoft Dynamics 365 Server for claims-based authentication and also for IFD. Don't forget to do the appropriate configuration on AD FS server for claims and IFD auth.

So I will assume the following:

ADFS is at https://adfs.example.com
CRM non-IFD URL for particular org is at https://crm.example.com/ORG
IFD URL for org is at: https://org.crm.example.com

See IFD configuration notes at the end of the article.

Regarding non-IFD, Claims-only configuration for internal access - I tested it with DVSC and as of writing, it didn't work. It did get the access token, but then failed to send requests to right URL - it didn't append the /org after the URL host part. There is an open issue 397: non-IFD URLs not handled correctly for on-Prem.

On ADFS Server you have to tell that you have to register your application. You will use ClientId in connection string. And you have to give permissions for this application to get access token for CRM IFD relying party.

RedirectUri must contain port and must use http protocol (for localhost). If port will not be specified, random will be used by MSAL when you connect via DVSC, but ADFS just won't accept it.

   # MSAL will listen on localhost this particular port to get answer from ADFS (authorization code), but that is only true for authorization code grant flow: https://learn.microsoft.com/en-us/windows-server/identity/ad-fs/overview/ad-fs-openid-connect-oauth-flows-scenarios#authorization-code-grant-flow
   # As for name, in real world, that would be something like: "Accounting Integration" or "Postman" or whatever that communicates the software or intent for the application that is connecting to CRM
   > Add-AdfsClient -ClientId ([guid]::NewGuid()) -Name "DVSC Client" -RedirectUri "http://localhost:54321" -Description "Dataverse Service Client will connect to CRM" -ClientType Public
   > $ClientId = (Get-AdfsClient -Name "DVSC Client").ClientId
   # If you don't grant permissions, you will get error along with an event id 1021 in AD FS event log: MSIS9605: The client is not allowed to access the requested resource. when trying to issue token at /adfs/oauth2/token.
   # For ServerRoleIdentifier, "external domain" URL must be used you chose when you configured IFD. You inputted 4 domains/urls, this would be the 4th one.
   # Another way to find out - go to ADFS, open Relying parties, open the IFD relying party (auth.crm.example.com), open Identities, and pick the first one: https://auth.crm.example.com/
   > Grant-AdfsApplicationPermission -ClientRoleIdentifier $ClientId -ServerRoleIdentifier "https://auth.crm.example.com/" -ScopeNames "openid"
   > $ClientId

   1260534b-00ca-4663-870f-d77b8d4ad6d3

What does openid has to do anything with this? It means that you allow your client application to use OpenID Connect (OIDC) authentication protocol.

OAuth must be enabled for CRM. Execute this on CRM server. Changes will be applied immediately:

   Add-PSSnapin Microsoft.Crm.PowerShell  
   $ClaimsSettings = Get-CrmSetting -SettingType OAuthClaimsSettings  
   $ClaimsSettings.Enabled = $true  
   Set-CrmSetting -Setting $ClaimsSettings

If you want to validate OAuth is enabled, you must open: https://org.crm.example.com/XRMServices/2011/Organization.svc/web and see with, say, Fiddler, whether you get WWW-Authenticate header that is like: Bearer redirect_uri=https://adfs.example.com/adfs/ls/

You may get multiple headers, like WWW-Authenticate: Negotiate and WWW-Authenticate: NTLM, but one with Bearer must be present. If you don't want to configure fiddler to catch TLS requests, you can check what headers that request returns like this:

   > Invoke-WebRequest -Uri "https://org.crm.exmple.com/XRMServices/2011/Organization.svc/web" -UseBasicParsing
   > $error[0].exception.response.headers["WWW-Authenticate"]

   Bearer authorization_uri=https://adfs.example.com/adfs/oauth2/authorize, resource_id=https://org.crm.example.com/

Don't worry, the Invoke-WebRequest returns 401 error - it is expected.

EDIT: There are actually some required steps documented after enabling OAuth. Particularly removing windows auth from 3 folders. Read Required steps after enabling OAuth for Dynamics 365 Server

The previous note:
As of writing, the DVSC will fail to connect if you get multiple WWW-Authenticate headers. It is due to a bug in the client itself and hopefully it gets fixed. isOnPrem not passed down correctly when invoking GetAuthorityFromTargetServiceAsync · Issue #396 · microsoft/PowerPlatform-DataverseServiceClient (github.com).

Thus, at the time of writing, we need additional configuration:

Open C:\windows\system32\inetsrv\config\applicationHost.config on CRM server.
Find this XML element:

       <location path="Microsoft Dynamics CRM/XRMServices/2011/Organization.svc">
        <system.webServer>
            <security>
                <authentication>
                    <digestAuthentication enabled="false" />
                    <basicAuthentication enabled="false" />
                    <anonymousAuthentication enabled="true" />
                    <windowsAuthentication enabled="true" />
                </authentication>
            </security>
        </system.webServer>
    </location>

Change windowsAuthentication to false. After save, changes are applied immediatelly, no IIS restart was needed.

       <location path="Microsoft Dynamics CRM/XRMServices/2011/Organization.svc">
        <system.webServer>
            <security>
                <authentication>
                    <digestAuthentication enabled="false" />
                    <basicAuthentication enabled="false" />
                    <anonymousAuthentication enabled="true" />
                    <windowsAuthentication enabled="false" />
                </authentication>
            </security>
        </system.webServer>
    </location>

Connect with DVSC

I actually uploaded various scenarios to GitHub: DataverseServiceClientOnPremSamples: Examples on how to use Dataverse Service Client to connect to Dynamics 365 CRM on-premises (github.com)

appsettings.json:

{
  "ConnectionStrings": {
    "default": "AuthType=OAuth;Url=https://org.crm.example.com/;Username=user@example.com;Password=<pw>;AppId=174697c7-4ec8-401a-88ce-e6af4d05b6dd;LoginPrompt=Never"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Trace"
    }
  }
}

Program.cs:

using Microsoft.Crm.Sdk.Messages;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk.Query;

namespace ADFS2019ConnectionString
{
    class Program
    {
        IConfiguration Configuration { get; }
        static ILogger Logger { get; } = LoggerFactory.Create(builder => builder.AddConsole()).CreateLogger<Program>();

        Program()
        {
            var path = Environment.GetEnvironmentVariable("DATAVERSE_APPSETTINGS") ?? "appsettings.json";
            Configuration = new ConfigurationBuilder().AddJsonFile(path, optional: false).Build();
        }

        static void Main(string[] args)
        {
            Program app = new();
            var connectionString = app.Configuration.GetConnectionString("default");
            ServiceClient serviceClient = new(connectionString, logger: Logger);
            WhoAmIResponse resp = (WhoAmIResponse)serviceClient.Execute(new WhoAmIRequest());
            Console.WriteLine("User ID is {0}.", resp.UserId);
            var systemuser = serviceClient.Retrieve("systemuser", resp.UserId, new ColumnSet("fullname"));
            Console.WriteLine($"Hello, {systemuser["fullname"]}, where do you want to go today?");

            // Pause program execution before resource cleanup.
            Console.WriteLine("Press any key to continue.");
            Console.ReadKey();
            serviceClient.Dispose();
        }
    }
}

Packages:

Microsoft.Extensions.Configuration
Microsoft.Extensions.Configuration.Json
Microsoft.Extensions.Logging.Console
Microsoft.PowerPlatform.Dataverse.Client

Result:

User ID is e0f638f8-e3a0-44df-a242-8557ed7e0e33.
Hello, John Doe, where do you want to go today?

Lets look at 2 ways we can use the connection string:

Authorization code grant: AuthType=OAuth;Url=https://org.crm.example.com/;Username=user@example.com;Password=<pw>;RedirectUri=http://localhost:54321;AppId=174697c7-4ec8-401a-88ce-e6af4d05b6dd;LoginPrompt=Always

UI required (browser). Takes 2 requests to get access token. MSAL library takes care of listening to particular port specified in RedirectUri, so that when first request goes out to ADFS and you get authorization code, browser on your computer where the app runs can get the response, extract authorization code and issue another request to ADFS to finally get the access token.

Resource Owner Password Credentials grant: AuthType=OAuth;Url=https://org.crm.example.com/;Username=user@example.com;Password=<pw>;AppId=174697c7-4ec8-401a-88ce-e6af4d05b6dd;LoginPrompt=Never

You authenticate as particular user. No UI required. Takes 1 request to get access token.

This flow is considered for Desktop and Mobile application types, however I am going to use it for Service (Daemon) application as it requires no user interaction and I'm not so sure if Client credentials flow (which is officially meant for daemon applications) can be supported by on-premises instance.

Has drawbacks - no MFA support, passwords with leading/trailing whitespace not supported etc. See notes and consider what applies to on-premises.

ADFS 2016

Well that was for fancy ADFS 2019 which is in friends with MSAL. But there is another option to authenticate against ADFS 2016. Or in this case, we can use more OAuth authentication flows if we want.

Luckily, there is ServiceClient constructor available that takes tokenProviderFunction - we can just feed in access token that we got by any means we want.

public ServiceClient(Uri instanceUrl, Func<string, Task<string>> tokenProviderFunction, bool useUniqueInstance = true, ILogger logger = null)

Will use a plain HttpClient request to ADFS to get access_token. As MSAL doesn't support ADFS 2016, one can maybe use ADAL (Microsoft.IdentityModel.Clients.ActiveDirectory) - however it is deprecated.

Beware this is a quick and dirty example - no refresh tokens, no checking for slash suffixes (i.e for adfsUrl variable), no correct naming for TokenResponse class, bare exceptions, no error handling.

using Microsoft.Crm.Sdk.Messages;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk.Query;
using System.Net.Http.Json;

namespace ADFS2016
{
    class Program
    {
        IConfiguration Configuration { get; }
        static ILogger Logger { get; } = LoggerFactory.Create(builder => builder.AddConsole()).CreateLogger<Program>();

        Program()
        {
            var path = Environment.GetEnvironmentVariable("DATAVERSE_APPSETTINGS") ?? "appsettings.json";
            Configuration = new ConfigurationBuilder().AddJsonFile(path, optional: false).Build();
        }

        static void Main(string[] args)
        {
            Program app = new();
            var crmUrl = app.Configuration["CrmUrl"] ?? throw new ArgumentNullException("CrmUrl");
            ServiceClient serviceClient = new(new Uri(crmUrl), app.TokenProviderAdfs2016, logger: Logger);
            WhoAmIResponse resp = (WhoAmIResponse)serviceClient.Execute(new WhoAmIRequest());
            Console.WriteLine("User ID is {0}.", resp.UserId);
            var systemuser = serviceClient.Retrieve("systemuser", resp.UserId, new ColumnSet("fullname"));
            Console.WriteLine($"Hello, {systemuser["fullname"]}, where do you want to go today?");

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey();
            serviceClient.Dispose();
        }

        /// <summary>
        /// Cache access_token for subsequent requests
        /// </summary>
        string? AccessToken { get; set; }
        async Task<string> TokenProviderAdfs2016(string instanceUri)
        {
            if (AccessToken != null) return AccessToken;
            var resource = new Uri(Configuration["CrmUrl"] ?? throw new ArgumentNullException("CrmUrl")).GetLeftPart(UriPartial.Authority) + "/";
            ///Construct Resource owner password credentials grant request - username/password enought for auth
            HttpClient http = new HttpClient();
            var adfsUrl = Configuration["AdfsUrl"] ?? throw new ArgumentNullException("AdfsUrl");
            var request = new HttpRequestMessage(HttpMethod.Get, adfsUrl + "/oauth2/token");
            request.Headers.Add("Accept", "application/json");
            request.Content = new FormUrlEncodedContent(new Dictionary<string, string>() {
                { "grant_type", "password" },
                { "client_id", Configuration["AppId"] ?? throw new ArgumentNullException("AppId") },
                //If CrmUrl is https://crm.example.com/org, resource will be https://crm.example.com/. This must match with one of identifiers for relying party for CRM in ADFS when configured either claims auth or IFD.
                //ADFS 2016 requires resurce parameter. ADFS 2019 allows resource within scope parameter. MSAL will use scope parameter.
                { "resource", resource },
                { "scope", "openid" },
                { "username", Configuration["Username"] ?? throw new ArgumentNullException("Username") },
                { "password", Configuration["Password"] ?? throw new ArgumentNullException("Password") },
            });

            var response = await http.SendAsync(request);
            var token = await response.Content.ReadFromJsonAsync<TokenResponse>();
            AccessToken = token?.access_token ?? throw new Exception("Couldn't get access_token");
            return AccessToken;
        }

        public record TokenResponse
        {
            public string access_token { get; set; }
            public string refresh_token { get; set; }
        }
    }
}

I already commited ADFS2019DeviceFlow project example which would implement device code flow. Ever seen this message for some tools? To sign in, use a web browser to open the page https://adfs.example.com/adfs/oauth2/deviceauth and enter the code PLSSCZGKY to authenticate - you got covered.

IFD Configuration notes

When I configured IFD, I did use slightly different URLs than what is at the documentation. Enabling IFD means you will get URL for each organization:

org1.<web application server domain> (org1.contoso.com)
org2.<web application server domain> (org2.contoso.com)

If you choose contoso.com, or example.com and if it is the same as your AD domain... well, lets say I'm not a fan of "polluting" DNS. I like to separate things. If any of your org names happen to have a same name as any of your hosts in AD domain or more correctly - if DNS already has such entry that doesn't point to CRM server - well, that URL wont work and no DVSC for you.

Another reason would be if you have SharePoint and it also uses ADFS, you may get additional issues like MSIS7065: There are no registered protocol handlers on path /adfs/ls/ to process the incoming request.: Passive federation request fails when accessing an application using AD FS and Forms Authentication after previously connecting to Microsoft Dynamics CRM also using AD FS - Microsoft Support

So you may do yourself a favor and choose crm.example.com as web application server domain. In my case I did the following configuration:

Web Application Server Domain: crm.example.com (You will get org.crm.example.com URLs for organizations)
Organization Web Service Domain: crm.example.com
Discovery Web Service Domain (Err, hostname): dev.crm.example.com
(On the next page) Enter the external domain where your internet-facing servers are located: auth.crm.example.com (This URL will be used later when giving ADFS permissions)

Regarding certificates, you have 2 options. The DNS is on two levels, but wildcard can only cover one domain level. So you have 2 options now:

You now need additional wildcard cert for *.crm.example.com
You need all-in-one cert with these SANs:
- org.crm.example.com
- <any other org names>.crm.example.com
- auth.crm.example.com
- dev.crm.example.com
- crm.example.com (non-IFD URL, maybe it is even your CRM hostname - wildcard cert doesn't cover this cert so it must be added to SAN anyways)

If you go with the first option by already having a cert for crm.example.com and would like to add just another certificate for *.crm.example.com:

Add it to local computer certificate store.
Tell your webserver to use this cert when accessing *crm.example.com:443. If you execute following command with PowerShell, escape curly braces {} with backticks: `{ and `}

   netsh http add sslcert appid={4dc3e181-e14b-4a21-b022-59fc669b0914} hostnameport=*.crm.example.com:443 certstorename=MY certhash=FF9688D828EEFEA33696CEB61153947623EBF2E1

I copied the appid from netsh http show sslcert ipport=0.0.0.0:443 - but it can be whatever, even [guid]::NewGuid() if you will.

Also make sure in IIS, Microsoft Dynamics CRM app bindings have empty "Host Name" for https

About OAuth

Aren't you confused about different grants/flows/terminology? What to use when? How it works? How can I construct those requests myselff, etc. I was/am. Things start to make sense when I dig deeper. Not so much when I want to just get past authentication. These things helped me in a way:

Luckily the first thing that helped me orient is which flows are suitable for which case: Authentication flow support in the Microsoft Authentication Library (MSAL) - Microsoft Entra | Microsoft Learn - there you can click links and follow how exactly they work and how the request must be constructed.
Now the ClientId or apps or relying parties which I see in ADFS. Think of a ClientId like identifier for your app (doh). A thing that enables you to use OAuth. And there you can give access to some resources (relying parties or applications) - if permission is granted, the application you are trying to access will be satisfied with your token. Otherwise not. Remember Grant-AdfsApplicationPermission

💡 Aah, that's what registering an app in Azure AD is about - it is to enable OAuth! Ahh, this is what New-AdfsClient does, kind of the same!

Why is Microsoft doing this to us?

Why don't we get WS-Trust or windows integrated authentication? If we consider the following, all of this kind of makes sense:

Microsoft.CrmSdk.XrmTooling.CoreAssembly being windows only has access to underlying windows security libs and ADFS via .NET Framework to construct Kerberos / WS-Trust token formats.
ADAL is deprecated without backward support for ADFS 2016 which is still supported.
Dynamics 365 CRM on-premises uses WS-Trust protocol which is deprecated by Microsoft, due to various vulnerabilities.
All in all, DVSC being designed to be cross platform, has lost many authentication scenarios.

And then ADFS 2016 lacking necessary support for MSAL, because:

Still relies on resource parameter (whereas these libraries only use scope)
Doesn't support Proof Key for Code Exchange (PKCE)

So on-premises along with number of authentication requirements and support for ADFS 2016 is supported with Microsoft.CrmSdk.XrmTooling.CoreAssembly package. And Microsoft.PowerPlatform.Dataverse.Client supporting modern authentication requirements for cloud environment.

Helpful literature:

But the on-premise dinosaurs 🦖 can be modern too, right?

Other libraries

You may also want to check out Data8 .NET Core Client SDK for On-Premise Dynamics 365/CRM - Haven't tried it, but from what I read it supports WS-Trust and Windows Integrated Authentication.

P.S. 🦖 use the legacy vocabulary. Please don't be bothered too much when I call CRM what is in one way or another a subset/superset for Dataverse, PowerPlatform, Dynamics 365 Customer Engagement/Sales/Marketing, Common Data Service, XRM. 3 characters are the shortest way to reference CRM.

Dynamics CRM / Any ASP.NET App - Improve C# dynamic compilation times

Jānis Veinbergs — Wed, 19 Apr 2023 08:14:41 +0000

The Pain

Since I installed Dynamics 365 (CRM) v9.1 On-Premises, I get REALLY slow compile times. The csc.exe (C# Compiler) is so slow to compile whenever I make unique request, I could wait for many seconds or minutes for compilation to finish. So I was searching for a way to improve things. It doesn't do parallel compilation if I make multiple unique requests from multiple browser tabs and it doesn't utilize CPU that much. A "legacy junk" indeed.

The ASP.net does dynamic compilation on first request. You can read more about it at Understanding ASP.NET Dynamic Compilation.. But from Task Manager it looks something like this:

"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe" /noconfig /fullpaths @"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\0345f868\a571ab7d\mejffpag.cmdline"

The Painkillers?

As Dynamics 365 is a 3rd party app and I thought I could at least precompile the aspx pages and such. So I did just that:

. C:\windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_compiler.exe -m "/LM/W3SVC/2/ROOT"

As it did take a while to complete, I certainly had good feelings about this. Just to find out csc.exe still doing dynamic compilation when I make requests and the performance for my dev environment hasn't improved at all (just a gut feeling, didn't do measurements). That means I have to take first request hit all of my own for any request. :(

But it did something, as it generated tons of files within Temporary ASP.NET Files folder - probably just not enought.

The Painkillers!

After a while I stumbled upon something interesting: Enabling the .NET Compiler Platform (“Roslyn”) in ASP.NET applications.

Eager to try out and do some not-so-much-supported modifications within 3rd party app web.config that will certainly be overwritten when I apply next patch - but it is a dev env, no real risk of breaking it:

# Got nuget.exe and within arbitrary directory:
nuget.exe install Microsoft.CodeDom.Providers.DotNetCompilerPlatform -Version 3.6.0 -OutputDirectory .\packages\

# note that Dynamics 365 (CRM) 9.1 Is .NET Framework 4.6.2 app.
cp -Recurse ".\packages\Microsoft.CodeDom.Providers.DotNetCompilerPlatform.3.6.0\tools\Roslyn46\*" "C:\Program Files\Dynamics 365\CRMWeb\bin\roslyn"
cp ".\packages\Microsoft.CodeDom.Providers.DotNetCompilerPlatform.3.6.0\lib\net45\*" "C:\Program Files\Dynamics 365\CRMWeb\bin\"

Then edited "C:\Program Files\Dynamics 365\CRMWeb\web.config" by adding <system.codedom> node:

<configuration>
  ...
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs"
        type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=3.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        warningLevel="4" compilerOptions="/langversion:7.3 /nowarn:1659;1699;1701"/>
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
        type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=3.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        warningLevel="4" compilerOptions="/nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+"/>
    </compilers>
  </system.codedom>
</configuration>

I did validate that it was using the new compiler: csc.exe was from bin/roslyn directory and I saw a VBCSCompiler.exe process too. That verifies that web.config changes are correct and assemblies are in correct places.

Restart-Service W3SVC wasn't needed, as changes to web.config are detected and app pool gets recycled.

Performance improvements

So I had to measure performance improvement. I opted for UIforETW to initiate hassle free etw trace and Windows Performance Analyzer (WPA) to see the results. I wanted more scientific results than a wall clock :)

I did a run with roslyn compiler and other run with builtin compiler.
I did open root Dynamics CRM page (a dashboard). I still use Legacy interface, but it doesn't prevent us making a point on csc.exe performance.
Compilation resulted in generating 440 files.
I did purge ASP.NET Temporary Files before each run.

Legacy csc.exe

Purge files before run

gci -Recurse "C:\windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\0345f868\a571ab7d" | ? lastwritetime -ge '2023-04-18' | ? PsIsContainer -eq $false | Remove-Item -Force

Refresh page
Look at trace with WPA - 6 times csc.exe process was called. Time since first spawn and last one ending 199sec = 3m19sec.

Roslynized csc.exe

Purge files before run
Refresh Page
116sec = 1m56sec

Results

Okay, so that was kind of total refresh for a single page that makes many calls - normally things would be somewhat cached. I feel that navigating to other pages feels faster, but still I wouldn't say that it is fast.

I'll leave roslyn compiler on as any performance improvement will help - I feel that normally navigating through pages is faster. It helps against doing context switching to other tasks while waiting on compilation.

Precompiling ASP.NET Framework app

We covered dynamic compilation - a process where compilation for relevant files happens when making request and compiling appropriate files - and that request is taking the performance impact.

For ASP.NET apps you develop, you can precompile your website, so that dynamic compilation never takes place and your users don't have to wait.

I also have access to a ASP.NET Framework 4.8 application and I did want to improve build times there too. It usually took 34 minutes for aspnet_compiler.exe to do its precompilation job. All I had to do was to install Microsoft.CodeDom.Providers.DotNetCompilerPlatform package (latest 4.1.0 version), it did the web.config changes and copied required files to bin directory - the result was immediatelly visible - instead of 34 minutes, now it did precompilation in ~13 minutes. Solid 20 minutes shaved off!

As for precompilation, it wasn't immediatelly obvjous that it is using roslyn csc.exe - because I didn't see neither VBCSCompiler.exe or csc.exe, just aspnet_compiler.exe doing its job. But the results speak of themselves.

This topic was very helpful in getting up and running with roslyn-ized csc.exe compiler: How to speed up aspnet_compiler.exe?

SQL Server Query Optimizer. And how it can hang your business.

Jānis Veinbergs — Wed, 03 Aug 2022 09:55:00 +0000

Dynamics AX works well until it comes to a grinding halt. Without any software or hardware configuration change. How come?

Enter SQL Server Query Optimizer. And how to tame it.

So occasionally some part of functionality looses performance (i.e you can never finish your operation, it just hangs). Sometimes it impacts so many areas that various real life operations are severely impacted. Having experience administering this particular system meant I should look at SQL Server that causes most of performance issues.

Finding the issue

The first thing is to look at queries which take most of the time in particular timeframe. So someone complains something is going bad - I look at last 2 hours of data collected with SQL Sentry (Now SentryOne)*
A-ha, some very suspicious query - average execution time is ~25s. Moreover it does many physical reads (reads from disk, data not cached into RAM). This was very easy to spot, but sometimes you get bad queries with 100ms execution time.
```
(@P1 nvarchar(5),@P2 nvarchar(21),@P3 datetime)SELECT 
<list_of_224_columns> FROM CUSTINVOICEJOUR A WHERE 
((DATAAREAID=@P1) AND ((LEDGERVOUCHER=@P2) AND (INVOICEDATE=@P3)))
```
A super simple query if you ask me, albeit pretty wide one.
Lets look at the query plan. On Query History page I can right click and press Open Plan. At first I see a Clustered Index Seek and nothing else - looks perfect.

Until I put my mouse over that index to see what columns it seeks over. I look at Seek Predicates (columns which utilize index) and Predicate (columns which could utilize index, but does not). And we see only 1 column DATAAREAID at Seek Predicates, which is a very rough data categorization column with only a few distinct values in there.

So it is more like data scanning most of the table rows each time this query executes. At previous screenshot we see it did that 180 times over period of ~2hrs.
What we ideally want to see there is many Seek Predicate columns and possibly few Predicate ones (but it depends on the query whether it is possible). So we are dealing with Queries that have parameter sensitive plan (PSP) problems.

To get a sense of how indexes are used, what does clustered index mean, what is key lookup (or bookmarked lookup) look at Clustered Index article.
So culprit is identified (diagnostics is usually most of the work). Isn't there a better index for this query to use? Let's check. Execute sp_helpindex 'dbo.CUSTINVOICEJOUR' - or better yet sp_sqlskills_helpindex 'dbo.CUSTINVOICEJOUR' (sp_sqlskills_helpindex)

From query plan we saw it uses INVOICENUMIDX which is clustered index and it can only use first column for seek. However there is much better index: LEDGERVOUCHERIDX which has all 3 key columns that could be used for seeking: DATAAREAID, LEDGERVOUCHER and INVOICEDATE.

Why are we facing this issue?

Why did SQL Server choose obviously much more worse index? My theory is as follows: The Query plan estimated (out of STATISTICS) that there will be only 1 row. Why? If you look at query plan XML, you can see on what query parameters (Actual values for @P1, @P2, @P3) were present when SQL decided to generate & cache this particular query plan:

                <ParameterList>
                  <ColumnReference Column="@P3" ParameterCompiledValue="'2022-07-26 00:00:00.000'" />
                  <ColumnReference Column="@P2" ParameterCompiledValue="N'<something>'" />
                  <ColumnReference Column="@P1" ParameterCompiledValue="N'DMT'" />
                </ParameterList>

@P1 corresponds to DATAAREAID. Lets look at statistics - how much rows does SQL server expect to find:

DBCC SHOW_STATISTICS ('dbo.CUSTINVOICEJOUR', 'DATAAREAID')
Could not locate statistics 'DATAAREAID' in the system catalogs.

Alright, no statistics for particular column - we have to look at index statistics then:

DBCC SHOW_STATISTICS ('dbo.CUSTINVOICEJOUR', 'I_062LEDGERVOUCHERIDX')

Sorry I'm not showing you actual values, however none of the rows say DMT. Basically, when SQL server updates statistics (it looks at each 5th row or whatever percentage of rows you ask SQL to look at) - and it didn't see even one where there would be DATAAREAID = DMT. Thus SQL Server estimates there is 1 row (it never estimates 0). However SQL thinks: well, if I'll get 1 row from index LEDGERVOUCHERIDX using only first key column DATAAREAID, I'll have to lookup all those selected columns within clustered index INVOICENUMIDX - why not just directly get that row from INVOICENUMIDX which also begins with DATAAREAID and spare a key lookup (as all the data will already be within the page)? (Understanding Key Lookups)

For these particular parameters SQL Server has really found the best way to return the data, no questions about that.

However, the problem (or a feature) is that SQL Server caches this query plan. Suppose DATAAREAID now would be something that statistics shows us we have 352 million rows - and SQL will now get each of this row, filter out linearly INVOCIEDATE and LEDGERVOUCHER (because chosen index doesn't have data arranged in a way that index can be utilized to not read that data in the first place).

Solutions?

How about preventing SQL server cache this particular query plan at all? It is possible to do with plan guides, but it is a bad idea: if query is set to execute multiple hundreds of times per second, then query plan compilation adds >10ms of time to execute. Ideally this query would execute in ~1ms time (working few years on the same system kind of tells you how long that simple query should take), so we don't want at LEAST 10x overhead to query execution. Bad idea in this case. But could work for queries that get executed few times OR queries that by itself takes 50ms or more to run.
Previously mentioned Queries that have parameter sensitive plan (PSP) lists other hints we could use, but I'll go over and use my favorite, most stable option.
Tell SQL Server to use particular index using plan guides. That's what we are going to do.

What are plan guides? As documentation puts it:

Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server. Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected.

Perfect:

I cannot directly change the query
I can influence query optimization
Subset of queries from third-party vendor query is not performing well.

Creating plan guide

EXEC sp_create_plan_guide
    @name = N'[QNW-319-445612-custinvoicejour-perf]',
    @stmt = 'SELECT <list_of_224_columns> FROM CUSTINVOICEJOUR A WHERE ((DATAAREAID=@P1) AND ((LEDGERVOUCHER=@P2) AND (INVOICEDATE=@P3)))', 
    @type = N'SQL',
    @params = N'@P1 nvarchar(5),@P2 nvarchar(21),@P3 datetime',
    @hints = N'OPTION(TABLE HINT(A, INDEX(I_062LEDGERVOUCHERIDX)))'
GO

When plan guide is created, it immediately invalidates the existing plan and when such query comes in, generates new query plan taking into account plan guide hints.

Now we must validate:

Whether new query plan uses this plan guide (i.e we provided correct @stmt and @params vars for sp_create_plan_guide)
What is the performance of the query - better or worse?

To validate the first point, we must get query from plan cache and find PlanGuideName attribute within XML. Here is a query that, among some statistical stuff, has uses_plan_guide column and actually provides a way to open query plane and validate that plan guide is in use:

select TOP 100
    execution_count,
    sql_handle, qs.query_hash, plan_handle, qs.query_plan_hash, qt.text,
    [query_plan] = CAST (qp.query_plan AS XML),
    creation_time,
    last_execution_time,
     qs.total_logical_reads,
     qs.last_logical_reads,
     qs.min_logical_reads,
     qs.max_logical_reads,
     qs.last_logical_writes,
     qs.min_logical_writes,
     qs.max_logical_writes,
     [avg_logical_reads] = qs.total_logical_reads / 1.0 / qs.execution_count,
     [total_elapsed_time_ms] = qs.total_elapsed_time / 1000.0,
     [last_elapsed_time_ms] = qs.last_elapsed_time / 1000.0,
     [min_elapsed_time_ms] = qs.min_elapsed_time / 1000.0,
     [max_elapsed_time_ms] = qs.max_elapsed_time / 1000.0,
     [avg_elapsed_time_ms] = qs.total_elapsed_time / qs.execution_count / 1000.0,
     [min_worker_time_ms] = qs.min_worker_time / 1000.0,
     [max_worker_time_ms] = qs.max_worker_time / 1000.0,
     [avg_worker_time_ms] = qs.total_worker_time / qs.execution_count / 1000.0,
     [force_index] = CASE CHARINDEX(N' INDEX(', qt.text) WHEN 0 THEN 0 ELSE 1 END,
     [uses_plan_guide] = CASE CHARINDEX(N' PlanGuideName=', qp.query_plan) WHEN 0 THEN 0 ELSE 1 END,
     [create_plan_guide] = 'sp_create_plan_guide_from_handle @name = N''[provide_name]'', @plan_handle = ' + CONVERT(NVARCHAR(MAX), qs.plan_handle, 1) + ', @statement_start_offset = ' + CAST(qs.statement_start_offset as NVARCHAR(100)) -- you can use this statement to force this query use currently cached query plan
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
   CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE
    qt.text LIKE N'%<list_of_224_columns> FROM CUSTINVOICEJOUR A WHERE ((DATAAREAID=@P1) AND ((LEDGERVOUCHER=@P2) AND (INVOICEDATE=@P3)))'
ORDER BY qs.total_worker_time DESC

And this query actually gives answer to the second question too - avg_elapsed_time_ms is of intereset. For me it reads 0,32ms. I can't ask for more! Moreover, within 41 seconds it has execution_count of 515 times. Compare that to 249 executions in 186 minutes...

In fact, the job completed so fast, it is no more to be seen on SQL Sentry query stats which has configured 5 seconds of total duration for it to be included. So unless it executes 15k+ times within a minute, I'm not seeing it there.

By the way, if you ever delete/rename index, that doesn't mean SQL Server will error out executing that query - it will disregard the plan guide when compiling query plan. There are performance counter to monitor to know when you have stale plan guides: Misguided Plan Executions/sec

Microsoft is on it

This is certainly a flaw of SQL Server being dumb. However since SQL Server 2017 and onwards, Microsoft is tackling performance issues with some intelligent query processing which includes various feedback mechanisms that SQL Server keeps an eye on whether the particular query plan strategy at hand is good enough and adjusts accordingly. These look promising:

If only the application supported latest-and-greatest SQL Server... so I haven't been able to test out those features.

Anyways, I'm happy that the results are delivered and the system can continue working until the next query is to be found misbehaving...

* Note that for newer SQL Servers there are tools that may assist you with finding bad performing queries, like Query Store. For more advanced DB Users Data Collection could be used or just querying plan cache and sorting by qs.total_elapsed_time if the query plan is still there. System uses SQL Server 2008R2 that doesn't have Query Store, so using SQL Sentry is just doing the job faster.

Achieving single command Infrastructure deployment using PowerShell DSC.

Jānis Veinbergs — Fri, 08 Apr 2022 11:33:09 +0000

So, previously I wrote about deployment process to IIS servers. However wouldn't it be nice to keep that IIS configuration in sync, moreover between production and staging environment?

Configuration deployment world.

One way is to create the initial installation and configuration script. Nice and fast to setup additional server, a great first step. But:

The configuration has to be maintained and your script has to account for that (if this already exists then do nothing else configure).
You need to have a mechanism to run the script. Scheduler?
Your systems may drift if some processes or users do some configuration.
You do not know the diff between the current system configuration and the script.

There are many tools that may be used to provide uniform server configuration, like Group Policy, System Center Configuration Manager/Microsoft Endpoint Configuration Manager, PowerShell DSC (Desired State Configuration), Docker and various other software configuration management tools etc. All of those could be used to help achieve completely or partially uniform configuration across multiple hosts. Some of them are not free, some of them depends on Active Directory, some of them have GUI and so on.

Enter PowerShell DSC

Lets just explore PowerShell DSC, that you don't have to license and if you, like me, enjoy using PowerShell, this may be a tool for you.

Things I immediately found satisfying with PowerShell DSC:

It's PowerShell (with a little bit of additional syntax to learn) and no additional tools to install (apart from PowerShell modules).
It works over WinRM
It helps you define Infrastructure-as-Code (https://en.wikipedia.org/wiki/Infrastructure_as_code)
You can either make report configuration drifts, check them manually or make your host to automatically re-apply whatever was instructed in DSC script.

In an ideal world, once our server is joined to domain, I would like a one-click install for servers, be it staging or production environment. PowerShell DSC achieves quite a lot, but not necessarily a single click deployment. We will see why and how to overcome it.

Why did I go out to try PS DSC?

Didn't want to write configuration documentation - let there be text that can't be outdated.
IaC - wanted to have valid configuration in source control
I heavily use PowerShell and this would be a "natural extension".
This has come as a bonus: On deployment, check if IIS webserver configuration is as written.

Writing DSC script

I will be using PowerShell 5.1 with DSC 1.1. Within v2, DSC has been split out from powershell package and v3 will provide cross-platform capabilities.

What I'm going to concentrate on is overcoming hurdles when writing DSC and not the actual configuration itself. You can drill down the concepts and how-to documentation to learn DSC. My requirements were:

I want ONE or maybe two simple commands I can use to configure IIS servers
I want to be able target all or subset of servers
I want to target different environments (prod, staging)

Let's look at a simple dsc.ps1 script.

configuration SimpleConfiguration {
    # DSC provides only simple tasks. Usually modules will be imported to do additional tasks.
    Import-DscResource -ModuleName 'ComputerManagementDsc' -Name 'PendingReboot', 'TimeZone', 'SystemLocale'
    # One can evaluate expressions to get the node list E.g: $AllNodes.NodeName - AllNodes can be passed when calling function.
    node ("Node1","Node2","Node3")
    {
        # Call Resource Provider E.g: WindowsFeature, File
        WindowsFeature WebServerRole { Ensure = "Present"; Name = "Web-Server" }
        SystemLocale EnglishLocale { SystemLocale = "en-US" ; IsSingleInstance = "Yes" }
        File site1Folder { DestinationPath = "$env:SystemDrive\inetpub\sites\site1"; Type = 'Directory' }
    }
}

Understanding resources

What does SystemLocale EnglishLocale { SystemLocale = "en-US" ; IsSingleInstance = "Yes" } mean?

It means I'm calling SystemLocale resource, giving it whatever friendly name EnglishLocale and passing hashtable with properties resource accepts: SystemLocale, IsSingleInstance. You can get available properties like that:

Get-DscResource SystemLocale -Syntax
SystemLocale [String] #ResourceName
{
    IsSingleInstance = [string]{ Yes }
    SystemLocale = [string]
    [DependsOn = [string[]]]
    [PsDscRunAsCredential = [PSCredential]]
}

Why can't it be a simple install script?

It can, but in real world - without the word "simple". It's written in Desired State Configuration Overview for Engineers. In short: you focus on WHAT to do not on HOW. DSC provides idempotent (repeatable) deployments. Along with goodies like Continue configuration after reboot, Credential encryption and other goodies.

Why this silly syntax?

Well, because this resource under the hood actually implements 3 functions: Get, Set, Test

Get is a representation of state when, for example, running Test-DscConfiguration -Detailed or Get-DscConfiguration
Set - Applies configuration
Test - Determines whether configuration has to be applied. If not, Set is NOT run. That is, if system is already compliant with this resource, it won't re-run.

When Custom resources gets written, these functions get implemented. For example, using built-in Script resource, you also have to implement all of them. Take for example a configuration, that ensures BUILTIN\Users have no access to particular folder:

Script site1FolderPermissions {
  GetScript = { @{Result="Path = $env:SystemDrive\inetpub\sites\site1; Acl = $(Get-NTFSAccess "$env:SystemDrive\inetpub\sites\site1" | %{ $_.ToString() })" } }
  TestScript = {
     (Get-NTFSAccess "$env:SystemDrive\inetpub\sites\site1" | ? Account -eq 'BUILTIN\Users' | measure | select -ExpandProperty Count) -eq 0
  }
  SetScript = {
    Disable-NTFSAccessInheritance -Path "$env:SystemDrive\inetpub\sites\site1"
    $acl = Get-Acl "$env:SystemDrive\inetpub\sites\site1"
    $acl.Access | ?{ $_.IsInherited -eq $false -and $_.IdentityReference -eq 'BUILTIN\Users' } | % {$acl.RemoveAccessRule($_)}
    Set-Acl -AclObject $acl "$env:SystemDrive\inetpub\sites\site1"
  }
  DependsOn = "[File]site1Folder"
}

Drawbacks

Modules

Drawbacks or pain-points or maybe stuff that hasn't been provided by DSC out-of-the-box: Distributing modules. And this is the reason I can't have a single command to bootstrap my servers. The modules have to be installed on the host that are building the configuration AND the host that gets configuration deployed. Otherwise, when encountering Import-DscResource -Module CertificateDsc -ModuleVersion 5.1.0, it will tell you:

Could not find the module '<CertificateDsc, 5.1.0>'.

However moving Import-DscResource command just will result in error:

Import-DscResource cannot be specified inside of Node context

So the takeaway: we can't install a module and use its resources in a single configuration. These modules are required on the host that are building configuration and on target nodes. Modules are essential to any configuration, except the most trivial ones.

Moreover, the PackageManagement resource you see in a screenshot is a module itself that must be installed from PowerShell gallery before I can actually use it to install other modules 🤦‍♂️

Remember me telling how DSC enables writing more simple configuration comparing to install scripts? Turns out with DSC comes complexity anyways if I want automatic module provisioning. 🤷‍♂️ I don't want anyone (and my future self) running the deployment having to think about what modules must be installed on source & target machines.

So I will be using another configuration to install modules, but without any dependencies on modules. I'll run this configuration on hosts that require these modules and only then apply main configuration. First, I apply InstallPSModules configuration and then the SampleConfig configuration. There is a gotcha: I need modules on localhost too. But running Start-DscConfiguration .\InstallPSModules\ -ComputerName localhost gives me error:

The WinRM client sent a request to the remote WS-Management service and was notified that the request size exceeded the configured MaxEnvelopeSize quota

It may be misleading. If I pass -Credential parameter, specifying which user I want to use to connect to localhost (admin), then it works. This is due to the way users/computers/winrm are configured in my environment.

The other thing is that it may override some other DSC configuration applied to this computer. Anyways, I had to choose a tradeoff. I have a build task (a function) that installs modules on local computer and another for deploying modules to target nodes. But then I have to remember adding any dependencies in 2 places. Maybe I'm trying too hard for striving to reach the goal of "1 simple command to perform deployment"...

What I'm doing is installing from PowerShell gallery. You could set-up so that you have modules available on some share/local machine and copy them to target machine C:\Windows\system32\WindowsPowerShell\v1.0\Modules folder. Or perhaps any of these folders:

PS C:\Repos\psdsctest> C:\Tools\PSTools\PsExec64.exe -s powershell.exe -Command "`$env:PSModulePath -split ';'"

C:\Windows\system32\config\systemprofile\Documents\WindowsPowerShell\Modules
C:\Program Files\WindowsPowerShell\Modules
C:\Windows\system32\WindowsPowerShell\v1.0\Modules

That's PSModulePath for SYSTEM account. The configuration itself is being applied in the context of SYSTEM account on target nodes. Important to keep in mind when writing configurations. If you need to apply some stuff within context of another user, read about Credential Options in Configuration Data.

Here is an example build task for some configuration I wrote that copies files to particular computers before doing deployment (err, read "Hooking it all up" to see that I'm using a build tool, thus another "weird" syntax):

task copyRequiredFilesToNodes {
    exec {
        Write-Host "Copying $BuildRoot\winlogbeat to target nodes"
        $nodeSessions = New-PSSession -ComputerName $Nodes
        Invoke-Command -Session $nodeSessions { Remove-Item -Recurse -Path "C:\Windows\Temp\winlogbeat" -Verbose -ErrorAction SilentlyContinue}
        $nodeSessions | % { Copy-Item -Recurse -Path "$BuildRoot\winlogbeat" -Destination "C:\Windows\Temp\" -ToSession $_ -Force -Verbose }
    }
}

DSC Engine configuration for enabling reboot

Moreover, DSC Engine itself must be configured to, for example, allow reboots with PendingReboot resource. DSC Engine configuration is applied with Set-DscLocalConfigurationManager command and uses different configuration:

[DSCLocalConfigurationManager()]
configuration DSCConfig
{
    Node $AllNodes.NodeName
    {
        Settings
        {
            #allow reboot with PendingReboot resource from ComputerManagementDsc module
            RebootNodeIfNeeded = $true
        }
    }
}

Applying configuration:

Set-DscLocalConfigurationManager .\DSCConfig\ -ComputerName localhost -Verbose

So, multiple gotchas here. Lets hook it all up within a single build script we can call.

Targeting other environments

Configuration scripts are powershell scripts. You may choose to pass parameter or detect which env you are in any other way:

$environment = if ($env:USERDOMAIN -ieq "prod.example.com") {"production"} else {"staging"}

Then you can use if statements or override variables based on environment.

if ($environment -eq "production") {
    #Call some resources
} else {
    #Call other resources
}

Hooking it all up

So I need to take a step back and Start-DscConfiguration won't be my entry point to deployment. I need a .ps1 script that installs modules. Or, bear with me, I'll be using a PowerShell build tool: Invoke-Build.

nightroman / Invoke-Build

Build Automation in PowerShell

Invoke-Build is a build and test automation tool which invokes tasks defined in PowerShell v2.0+ scripts. It is similar to psake but arguably easier to use and more powerful. It is complete, bug free, well covered by tests.

In addition to basic task processing the engine supports

Incremental tasks with effectively processed inputs and outputs.
Persistent builds which can be resumed after interruptions.
Parallel builds in separate workspaces with common stats.
Batch invocation of tests composed as tasks.
Ability to define new classes of tasks.

Invoke-Build v3.0.1+ is cross-platform with PowerShell Core.

Invoke-Build can be effectively used in VSCode and ISE.

Several PowerShell Team projects use Invoke-Build.

The package

The package includes the engine, helpers, and help:

Invoke-Build.ps1 - invokes build scripts, this is the build engine
Build-Checkpoint.ps1 - invokes persistent builds using the engine
Build-Parallel.ps1 - invokes parallel builds using the engine
Resolve-MSBuild.ps1 - finds…

View on GitHub

You may use other tools too: psake, make, cake, fake or any other *ake you are familiar with. I look at them as a tools that make build tasks behind simple commands and help me answer: How did I run that code again?

So, we still must install Invoke-Build? Well, conveniently, there is a template for invoke-build file that detects if Invoke-Build is installed or not and installs it and we'll use it.

The result ended up using 4 scripts. However it can be used as an example/bootstrap project to speed up your implementation, if you choose to go down the same path:

build.ps1 - Invoke-Build script. Entry point
DSCConfig.ps1 - DSC Engine configuration. Allows reboots
InstallPSModules.ps1 - Module deployment
SampleConfig.ps1 - Where I would put my configuration.

In the end, I can checkout the code and run:

.\build.ps1 deploy -Nodes localhost
# or
.\build.ps1 deploy -Nodes server1, server2, server3 -Credential (Get-Credential)
# By the way, whatever credential you pass to the build script is only for connecting to target node and starting DSC configuration. Remember that resources itself are applied within SYSTEM context.

I'v put up the scripts at psdsctest repository:

janis-veinbergs / psdsctest

PowerShell DSC - Infrastructure as Code. Work-around pain points.

Show how to initiate PowerShell DSC configuration with one-single command. Includes configuration of DSC Engine and module dependency installation.

Available tasks:

./build.ps1 ?
    Name                   Jobs                                                                 Synopsis
    ----                   ----                                                                 --------
    deployDSCConfig        {buildDSCConfig, {}}                                                 Apply DSC engine configuration to allow reboots. Kind of a special case: https://docs.mi... 
    installModules         {}                                                                   Install required powershell modules for current host for build to work.
    buildDSCConfig         {}                                                                   Generate .mof files configuring local DSC settings
    buildInstallPSModules  {}                                                                   Generate .mof files for PowerShell module installation for passed nodes (prerequisite fo...
    buildConfig            {}                                                                   Generates sample config
    build                  {installModules, buildDSCConfig, buildInstallPSModules, buildConfig} Build project. Build will call all those other taskkks
    deploy                 {clean, build, deployDSCConfig, deployInstallPSModules...}           Deploy will push configuration to nodes. Will call build before.
    deployInstallPSModules {buildInstallPSModules, {}}                                          Deploy only module installation. Will push configuration to nodes.

…

View on GitHub

Tying in Azure DevOps

In the end, when deploying code to IIS servers, I included a call to Test-DscConfiguration -Detailed to inform me whether host configuration has drifted away.

$testdsc = Test-DscConfiguration -Detailed
if (-not $testdsc.InDesiredState) {
  Write-Warning "Host configuration has drifted. Test-DscConfiguration returned False"
  Write-Output $testdsc.ResourcesNotInDesiredState
}

I'm not using any automatic corrections or not yet using DevOps to automatically apply configuration when DSC configuration changes have been committed. But it's possible to do that. However there comes additional complexity - perhaps I wouldn't like to reboot all servers at once if reboot is required. Or implement rolling deployment - apply configuration to some servers, see how they behave and then others.

Conclusion

I really wish PowerShell DSC would have provided OOTB way of installing modules and requiring them after installation. Anyway, I'll have the "frame-work" ready for the next DSC project.

Otherwise it's a nice tool for configuration deployment, if you can find the modules. But there are plenty.

Moreover, I do recommend looking at Building a Continuous Integration and Continuous Deployment pipeline with DSC article - it even shows how to run tests to validate your configuration.

Looking forward to any comments on what could have been done better.

Bringing down ASP.NET deployment interruptions from minutes to blip of a time with Azure DevOps

Jānis Veinbergs — Fri, 01 Apr 2022 08:07:32 +0000

This is going to be about improving deployment process from classic file copy to something more effective, automated and low friction for developers and bringing no interruptions to users. Maximizing Developer Effectiveness is actually competitive advantage after all.

Pain points

Our customer, a national school management platform was experiencing inconvenience when deploying a fairly big classic .NET Framework application to 22 Windows IIS servers:

Deployments would be taken at night
If a hotfix had to be issued at day with high load - it could take up to 30 minutes of complete system halt as IIS server farm compiles resources while pile of requests waits in a queue.
The deployment is not atomic - that is, while files are being copied between v1 and v2, we would get v1.314 - while some files are updated, the others not.
If we ever wanted a rollback - occasionally files would be locked, exacerbating already bad situation.
The website itself consists of ~220MB of data across ~2500 number of files. And small file copy is SLOW.

The root cause of this is that .NET resources (.aspx pages, razor views) get compiled to assemblies on first request whenever they change. When IIS is constantly bombarded with requests to all possible resources, it all must be compiled at the same time on every IIS server. On the other hand, when deployed at night, delay was minor as not many requests came in, and those that did, compiling was handled fairly quickly.

ASP.NET Requests Queued is a good performance counter to monitor to identify when user browsers are just waiting for response and how quickly situation resolves after an update. A healthy value is 0 or something close to that. Here you see cumulative request queue for all IIS servers in evening, when load actually is not high. 4 minutes of interruptions:

We thought about using robocopy to copy changed files based on timestamp, but that wouldn't work for us - as there could be multiple persons that would deploy the code. When Visual Studio Publishes the project, files between machines have different timestamps. Rsync would probably help greatly, but then again - some challenge to get that on developers windows machines and windows share server. And if different hosts build files, little differences in build tools may end up with different checksums. So we didn't go down this path.

The webroot (and IIS Shared Configuration) is actually served from a single Windows share, which at least provided a low effort way to conveniently copy files to a single location and keep them, along with the IIS configuration, in sync.

That's how we lived for years and it was clear that something had to change.

Deployment strategy

We came to a decision we would like to stop depending on a file share. We already started using Azure DevOps for some other stuff. So we got an idea 💡

Keep files on IIS local disk (Duh)
Azure DevOps Agent would be the one compiling code.
We would precompile views in advance. Locking wouldn't be an issue as we will copy within a new folder. 💎
Orchestrate deployment to all IIS servers via Azure DevOps Release Pipelines
Run all .dll files through ngen. That would eliminate additional delay when assembly is first loaded. As C# code compiled is actually MSIL - an intermediate language that CPU doesn't understand any of that. JIT compiler is the one that translates that to bytecode for the processor architecture at hand. And it does so usually Just-In-Time or On-The-Fly. Or, in our case, ngen (Native Image Generator)
A deployment task would have to copy files within a new folder and then we would change IIS Physical path from where to serve site files. 💎

So there are some gems there that are enough to solve our pain points. Now let's see some practical ways on achieving all of this!

Implementing build

Build is the process of generating files we must copy onto IIS servers.

Before we talk about build & deployment, let's get this straight: We are using Azure DevOps online offering. However this could as well be On-Premises installation or any other CI/CD platform of your choice. We are, however, using self-hosted agents that perform the build tasks.

We are talking about solution, which consists of multiple .NET projects. Our main interest is deploying ....Web.Application and ..Web.Application.Payments. The others are mostly dependencies which must also be built. So, the developers used to build app within Visual Studio. When deploying web application, you had to right click on particular project and choose publish. From there, some settings could be configured like whether we want precompilation or not, whether we deploy straight to IIS or filesystem. In our case, it was deployed to filesystem and then copied to production. More about Publishing an ASP.NET web app.

When writing Azure DevOps pipeline, I have to figure out what kind of MSBuild properties I have to use to invoke the "Publish" process. So, after searching the web, reading MSBuild diagnostic output logs, reading MSBuild .target files, I'v come up with properties I need.

By the way, here is a quick tip on how to find relevant .target files by some keyword:

> gci -Path "${env:ProgramFiles(x86)}\Microsoft Visual Studio\" -Recurse -Filter "*.targets" | sls "MvcBuildViews" -SimpleMatch -List
C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\Microsoft\VisualStudio\v15.0\Web\Microsoft.Web.Publishing.targets:849:  <Target Name="CleanupForBuildMvcViews" Condition=" '$(_EnableCleanOnBuildForMvcViews)'=='true' and '$(MVCBuildViews)'=='true' " BeforeTargets="MvcBuildViews">
C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\MSBuild\Microsoft\VisualStudio\v16.0\Web\Microsoft.Web.Publishing.targets:847:  <Target Name="CleanupForBuildMvcViews" Condition=" '$(_EnableCleanOnBuildForMvcViews)'=='true' and '$(MVCBuildViews)'=='true' " BeforeTargets="MvcBuildViews">

Or better yet, use Project System Tools extension with MSBuild Binary and Structured Log Viewer to sneak peek into what MSBuild is doing - you won't regret it.

So, the relevant "Publish" command within Azure Devops build pipeline:

- task: VSBuild@1
  displayName: Publish Web.Application  
  inputs:
    solution: 'Web.Application'
    msbuildArgs: >
      /t:Build,GatherAllFilesToPublish
      /p:PublishProfileName="$(publishProfileName)"
      /p:WebPublishMethod=FileSystem
      /p:DeleteExistingFiles=true
      /p:DeployOnBuild=true
      /p:MvcBuildViews="${{ parameters.Precompile }}"
      /p:PrecompileBeforePublish="${{ parameters.Precompile }}"
      /p:WDPMergeOption="MergeAllOutputsToASingleAssembly"
      /p:SingleAssemblyName="Web.Application.Precompiled"
      /p:UseMerge="true"
      /p:DebugSymbols="True"
      /p:EnableUpdateable="False"
      /p:PublishUrl="$(Build.BinariesDirectory)\my"
      /p:WPPAllFilesInSingleFolder="$(Build.BinariesDirectory)\my"
      /p:RunNpmScripts="$(runNpmScripts)"
      /p:AutoParameterizationWebConfigConnectionStrings="false"
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
    msbuildArchitecture: x64
    logFileVerbosity: detailed

Note that specifying publishProfileName, I use the actual publish profile used by Visual Studio Publish process and then override some properties by passing /p msbuild arguments.
The WebPublishMethod ensures build files are put within PublishUrl folder.
RunNpmScripts is our own custom build property used within .pubxml to run some npm build process.
MvcBuildViews, PrecompileBeforePublish, SingleAssemblyName, UseMerge, EnableUpdateable all relate to precompilation. Before build, I can choose to disable precompilation if I want the build to happen much faster.
AutoParameterizationWebConfigConnectionStrings - required for copy/paste if publish profile contains connection string replacements. Without this, there will be a placeholder value that must be replaced afterwards. More on StackOverflow.

The full build can be seen here:

# ASP.NET
# Build and test ASP.NET projects.
# Add steps that publish symbols, save build artifacts, deploy, and more:
# https://docs.microsoft.com/azure/devops/pipelines/apps/aspnet/build-aspnet-4
trigger: none
name: $(Build.SourceBranchName) $(Date:yyyyMMdd)$(Rev:.r)

pool:
  name: win-dev-pool

parameters:
  - name: Precompile
    default: false
    type: boolean
    displayName: Precompile

variables:
  webApplicationProject: 'Web.Application'
  solution: 'solution.sln'
  buildPlatform: 'x64'
  buildConfiguration: 'Release'
  releaseArchiveFilename: 'webapp.7z'
  releasePaymentsArchiveFilename: 'payments.7z'
  # which .pubxml file to use. Don't append .pubxml
  publishProfileName: STAGING
  runNpmScripts: true
  artifactShareName: \\MY-APP-AGENT2\agentpublishedfiles

steps:
- task: NuGetToolInstaller@1
- task: NuGetCommand@2
  inputs:
    command: 'restore'
    restoreSolution: '$(solution)'
    feedsToUse: 'config'
    nugetConfigPath: 'NuGet.Config'

# npm script prerequisites
- task: NodeTool@0
  inputs:
    versionSpec: '14.x'

# https://docs.microsoft.com/en-us/azure/devops/pipelines/release/caching?view=azure-devops#nodejsnpm
- task: Cache@2
  displayName: Cache npm
  inputs:
    key: 'v2 | npm | "$(Agent.OS)" | $(webApplicationProject)/package.json'
    path: '$(webApplicationProject)/node_modules'
    restoreKeys: 'v2 | npm | "$(Agent.OS)"'
    cacheHitVar: NPM_CACHE_RESTORED
  condition: and(succeeded(), variables.runNpmScripts)

- task: npmAuthenticate@0
  inputs:
    workingFile: '$(webApplicationProject)/.npmrc'
- task: Npm@1
  displayName: 'npm install'
  inputs:
    command: 'install'
    workingDir: '$(webApplicationProject)/'
  condition: and(succeeded(), variables.runNpmScripts, ne(variables.NPM_CACHE_RESTORED, 'true'))

# Workaround for AspNetPrecompile to skip scanning node_modules directory and finding .c,.cpp,.h files... https://stackoverflow.com/a/20963170/50173
- task: CmdLine@2
  displayName: Hide node_modules.
  inputs:
    script: 'attrib +H $(webApplicationProject)/node_modules'

# Specificually pass PublishProfileName as empty. Because otherwise in consequent runs, this task will run npm run-script that is specified as BeforeBuild target within publish profile.
# We already run publish actions further.
- task: VSBuild@1
  displayName: Build $(solution)
  inputs:
    solution: '$(solution)'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
    msbuildArchitecture: x64
    logFileVerbosity: detailed
    msbuildArgs: >
      /p:PublishProfileName=""

- task: VSTest@2
  displayName: Test $(solution)
  inputs:
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
  condition: ne(variables.NoTests, true)

# Target also Build, otherwise BeforeBuild target won't execute. BeforeBuild specifies npm run-script commands. So, this project gets built twice, but project build by itself is fast.
# PublishUrl actually unused as we won't use msdeploy to deploy stuff, just simple copy.
# Precompilation decision matrix: https://docs.microsoft.com/en-us/previous-versions/aspnet/bb398860(v=vs.100)

- task: VSBuild@1
  displayName: Publish Web.Application  
  inputs:
    solution: 'Web.Application'
    msbuildArgs: >
      /t:Build,GatherAllFilesToPublish
      /p:PublishProfileName="$(publishProfileName)"
      /p:WebPublishMethod=FileSystem
      /p:DeleteExistingFiles=true
      /p:DeployOnBuild=true
      /p:MvcBuildViews="${{ parameters.Precompile }}"
      /p:PrecompileBeforePublish="${{ parameters.Precompile }}"
      /p:WDPMergeOption="MergeAllOutputsToASingleAssembly"
      /p:SingleAssemblyName="Web.Application.Precompiled"
      /p:UseMerge="true"
      /p:DebugSymbols="True"
      /p:EnableUpdateable="False"
      /p:PublishUrl="$(Build.BinariesDirectory)\my"
      /p:WPPAllFilesInSingleFolder="$(Build.BinariesDirectory)\my"
      /p:RunNpmScripts="$(runNpmScripts)"
      /p:AutoParameterizationWebConfigConnectionStrings="false"
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
    msbuildArchitecture: x64
    logFileVerbosity: detailed


- task: VSBuild@1
  displayName: Publish Web.Application.Payments  
  inputs:
    solution: 'Web.Application.Payments'
    msbuildArgs: >
      /t:Build,GatherAllFilesToPublish
      /p:PublishProfileName="$(publishProfileName)"
      /p:WebPublishMethod=FileSystem
      /p:DeleteExistingFiles=true
      /p:DeployOnBuild=true
      /p:MvcBuildViews="${{ parameters.Precompile }}"
      /p:PrecompileBeforePublish="${{ parameters.Precompile }}"
      /p:WDPMergeOption="MergeAllOutputsToASingleAssembly"
      /p:SingleAssemblyName="Web.Application.Payments.Precompiled"
      /p:UseMerge="true"
      /p:DebugSymbols="True"
      /p:EnableUpdateable="False"
      /p:PublishUrl="$(Build.BinariesDirectory)\payments"
      /p:WPPAllFilesInSingleFolder="$(Build.BinariesDirectory)\payments"
      /p:AutoParameterizationWebConfigConnectionStrings="false"
    platform: 'AnyCPU'
    configuration: '$(buildConfiguration)'
    msbuildArchitecture: x64
    logFileVerbosity: detailed

# Artifacts
- task: ArchiveFiles@2
  displayName: Archive $(releaseArchiveFilename)
  inputs:
    rootFolderOrFile: '$(Build.BinariesDirectory)\my'
    includeRootFolder: false
    archiveType: '7z'
    sevenZipCompression: 'fastest'
    archiveFile: '$(Build.ArtifactStagingDirectory)/$(releaseArchiveFilename)'
    replaceExistingArchive: true
    verbose: true
- task: ArchiveFiles@2
  displayName: Archive $(releasePaymentsArchiveFilename)
  inputs:
    rootFolderOrFile: '$(Build.BinariesDirectory)\payments'
    includeRootFolder: false
    archiveType: '7z'
    sevenZipCompression: 'fastest'
    archiveFile: '$(Build.ArtifactStagingDirectory)/$(releasePaymentsArchiveFilename)'
    replaceExistingArchive: true
    verbose: true

- task: PublishBuildArtifacts@1
  displayName: Publish website deployment Artifacts
  inputs:
    PathtoPublish: '$(Build.ArtifactStagingDirectory)'
    ArtifactName: 'drop'
    publishLocation: 'FilePath'
    TargetPath: '$(artifactShareName)\my'

What this does, is builds solution (dependency .dlls)
Builds JavaScript SPA (Single Page App). Commands are buried within MSBuild project.
Publishes 2 applications (Just generates files on disk)
7zips those files
Publish artifact (Azure Devops thingie - makes them available for release pipeline). In this case, they are copied to a share.

Implementing deployment

Once we have the artifacts ready (archive of website files), we are ready to implement Release. The release must copy given files to some directory and instruct IIS to change base path from where it will server files. We had 2 options to choose from:

Make Build agent connect to IIS servers and perform the deployment on each IIS server. In this case, we have to write some script that will copy files onto each server and issue some commands to IIS. Moreover, we must control/see whether deployment is successful or not. And the build agent could actually be in another domain, with no direct access to production.
Install Deployment agent on all target IIS servers. Every host then receives deployment job and does whatever it is instructed to do. We don't have to bother about any other remote communication channel other than Deployment Agent with Azure DevOps server over 443/TCP. Plus we get nice UI of seeing whether deployment succeeded, partially succeeded (if partially, which hosts failed) and on which step it failed. Neat.

We went for the second option. Deployment agent is actually almost same as Build agent, just carries on deployment tasks.

When creating release pipeline, you get to play with the UI which is nice.

As you see, I'v split some operations into multiple steps.

Root App Pool - ensures appropriate application pool is created on IIS. It is actually a one-time step and may have been created beforehand.
Copy my files - PowerShell script to extract 7z files:

Expand-7Zip -ArchiveFileName "$(System.DefaultWorkingDirectory)\_Web.Application\drop\webapp.7z" -TargetPath "$(DeployTargetFolder)"

Ngen my - runs ngen.exe on all .dll files found within deployment folder. Also using custom condition so this step which may take a little more than a minute, could be turned off: and(succeeded(), eq(variables.Ngen, 'true'))

Set-Alias ngen -Value (Get-ChildItem -Recurse $env:windir\Microsoft.NET\Framework64\ -Filter ngen.exe | ? Length -gt 0 | select -first 1).Fullname

Get-ChildItem $(DeployTargetFolder) -Recurse -Filter *.dll | % { ngen install $_.Fullname /nologo /verbose }

Deploy my - Switchers virtual directory, issues some IIS configuration commands. When this stage is completed, IIS servers new code.

The great thing is that if any of steps fail for ANY IIS server before Deploy step, deploy won't run for ANY IIS server and they will all be still consistent.

In case of a rollback, we can open appropriate release and for "Deploy my" stage, press Redeploy. IIS will immediately switch back to appropriate folder.

Drawbacks

Using cloud hosted solution, if issues do happen, we won't be able to run our deployment pipeline. And they do happen. However, this may impact us on the rare case of rushing some kind of hotfix out. There are 2 steps we can take in this case:

Change IIS path manually to previous deployment folder (rollback)
Build via developer Visual Studio and copy appropriate DLL manually. Hotfix usually doesn't involve much code changes and is probably contained within a single or few files.

Otherwise we just wait for while DevOps issue gets resolved.

End result

Everything is actually configured that, when production branch gets new code, build is run automatically. After build, deployment is run automatically but stops for an approval. With Azure Pipelines Slack app we just get a message within channel where upon Approve button press, code goes into production.

This doesn't include database updates, which still must be performed manually, but eventually DACPAC deployment can be incorporated within a pipeline too. But luckily, database updates are more rare than backend code/frontend updates.

Judging from the Request Queue size, guess where did deployment happen?

Yeah, that little spike just before 09:00. Except, vertical axis shows max 30 instead of 3k and horizontally, just a blip of a time.

For a fair picture I should share another deployment request queue graphic:

Queue size spiked to almost 300. However it happens on some IIS servers, in this case 3 out of 10. After 60-90 seconds, queue size dropped, so fairly short period of time compared to what we experienced before. But it's not like those IIS completely stopped processing GET/POST requests - actually only minority of requests queued up. Currently I don't know the cause. Maybe if you have any thoughts on what may cause it, leave down in the comments.

In the end, the results leave everyone happy!

On an upcoming post, I'd like to share how these IIS server/Windows OS settings can be installed, managed and kept in sync with PowerShell DSC. And you don't have to keep a separate documentation file somewhere that may drift and be outdated in time. Stay tuned! 👋