DotNet and AI Materials

How do you handle retries, failures, duplicate messages, and dead-letter queues in Service Bus?

2026-11-07T19:30:00.000-08:00

Retries, Failures, Duplicates, and Dead-Lettering in Service Bus

Service Bus gives you at-least-once delivery, so your design has to assume that messages can fail, be retried, and arrive more than once. Four mechanisms work together:

Client retries for transient errors when talking to Service Bus
Message redelivery (peek-lock and delivery count) when processing fails
Dead-lettering for messages that can't be processed
Duplicate handling, through broker-side detection and idempotent consumers

1. Two kinds of retry

Client-side retries cover transient failures when sending or receiving (network blips, throttling, server busy). The Azure SDK retries automatically, and you can tune it:

var client = new ServiceBusClient(
    "mynamespace.servicebus.windows.net",
    new DefaultAzureCredential(),
    new ServiceBusClientOptions
    {
        RetryOptions = new ServiceBusRetryOptions
        {
            Mode = ServiceBusRetryMode.Exponential,
            MaxRetries = 5,
            Delay = TimeSpan.FromSeconds(1),
            MaxDelay = TimeSpan.FromSeconds(30)
        }
    });

Message redelivery covers failures in your processing logic. This is controlled by peek-lock settlement, covered next.

2. Peek-lock settlement and redelivery

In peek-lock mode, a received message is locked (default lock duration 30 seconds, max 5 minutes). You then settle it:

Action	Effect
Complete	Message is removed. Success.
Abandon	Lock is released; the message becomes available again and delivery count increments.
Defer	Message stays in the queue but is only retrievable by its sequence number. Useful for out-of-order processing.
Dead-letter	Message moves to the dead-letter queue immediately.
(do nothing / crash / lock expires)	Message becomes available again and delivery count increments.

Use ServiceBusProcessor, which handles concurrency, lock renewal, and settlement for you:

var processor = client.CreateProcessor("orders", new ServiceBusProcessorOptions
{
    MaxConcurrentCalls = 10,
    AutoCompleteMessages = false,          // settle explicitly
    MaxAutoLockRenewalDuration = TimeSpan.FromMinutes(10),
    PrefetchCount = 20
});

processor.ProcessMessageAsync += async args =>
{
    try
    {
        await HandleAsync(args.Message, args.CancellationToken);
        await args.CompleteMessageAsync(args.Message);
    }
    catch (TransientException)
    {
        // Retry: becomes visible again, delivery count + 1
        await args.AbandonMessageAsync(args.Message);
    }
    catch (PermanentException ex)
    {
        // Retrying won't help: dead-letter right away
        await args.DeadLetterMessageAsync(args.Message,
            deadLetterReason: "ValidationFailed",
            deadLetterErrorDescription: ex.Message);
    }
};

processor.ProcessErrorAsync += args =>
{
    logger.LogError(args.Exception,
        "Service Bus error from {Source} on {Entity}", args.ErrorSource, args.EntityPath);
    return Task.CompletedTask;
};

await processor.StartProcessingAsync();

Key point: classify failures. Transient failures (timeouts, 503s from a dependency, deadlocks) should be abandoned or retried. Permanent failures (malformed payload, validation errors, missing required data) should be dead-lettered immediately, since retrying them just burns delivery attempts.

Lock duration and long processing

If processing takes longer than the lock duration, the lock expires, the message is redelivered to another consumer, and your original Complete call fails with MessageLockLost. Avoid this by:

Setting MaxAutoLockRenewalDuration so the processor renews the lock automatically
Setting an appropriate lock duration on the entity
Moving very long work into a durable workflow and keeping message handlers short

Retry timing: Service Bus has no built-in backoff on redelivery

An abandoned message is available immediately, which can cause a tight retry loop that hammers a failing dependency. Common patterns:

In-process retry first: retry transient calls with exponential backoff (Polly or Microsoft.Extensions.Http.Resilience) within the handler before abandoning.
Scheduled re-enqueue: complete the original and send a copy scheduled for later, which gives you true delayed retries:

var retry = new ServiceBusMessage(args.Message)
{
    ScheduledEnqueueTime = DateTimeOffset.UtcNow.AddSeconds(Math.Pow(2, attempt) * 10)
};
retry.ApplicationProperties["attempt"] = attempt + 1;

await sender.SendMessageAsync(retry);
await args.CompleteMessageAsync(args.Message);

If you do this, do the send and complete in a transaction so you don't lose or duplicate the message (see below), and track the attempt count yourself.

Circuit breaker: if a dependency is down, stop receiving for a while instead of cycling messages through delivery attempts.

3. Dead-letter queues (DLQ)

Every queue and every topic subscription has a dead-letter sub-queue, addressed as <entity>/$deadletterqueue. Messages end up there when:

MaxDeliveryCount is exceeded (default 10). This is the "poison message" safety net.
TTL expires and dead-lettering on expiration is enabled (DeadLetteringOnMessageExpiration).
Your code dead-letters explicitly with a reason and description.
A subscription filter evaluation fails (when configured to dead-letter on filter evaluation exceptions).
Session-related issues, in some cases.

The DLQ is not automatically processed and has no TTL of its own, so messages stay until you deal with them. It also counts against the entity's size quota, so an ignored DLQ can eventually fill the queue.

Reading dead-lettered messages:

var dlqReceiver = client.CreateReceiver("orders", new ServiceBusReceiverOptions
{
    SubQueue = SubQueue.DeadLetter
});

var messages = await dlqReceiver.ReceiveMessagesAsync(maxMessages: 20, TimeSpan.FromSeconds(5));
foreach (var m in messages)
{
    Console.WriteLine($"{m.MessageId}: {m.DeadLetterReason} - {m.DeadLetterErrorDescription}");
}

For system dead-lettering, the reason is set for you (for example MaxDeliveryCountExceeded or TTLExpiredException). For your own dead-lettering, always set deadLetterReason and deadLetterErrorDescription so triage is easy.

What to do with dead-lettered messages

Alert when the DLQ message count is above zero (Azure Monitor metric DeadletteredMessages, filtered by entity). Unmonitored DLQs are where messages go to be forgotten.
Inspect them (Service Bus Explorer in the portal or in Visual Studio, or code like the above) and read the reason.
Fix the cause: a bug, a bad payload, or a downstream outage that has since recovered.
Resubmit after the fix: receive from the DLQ, send a clone to the original entity, then complete the DLQ copy. The Azure portal's Service Bus Explorer also supports resending dead-lettered messages.
Or discard/archive messages that are genuinely invalid, recording where they went.

await foreach (var dead in dlqReceiver.ReceiveMessagesAsync())
{
    var clone = new ServiceBusMessage(dead);   // copies body and properties
    await sender.SendMessageAsync(clone);
    await dlqReceiver.CompleteMessageAsync(dead);
}

Automate DLQ handling with an Azure Function triggered from the DLQ path (orders/$deadletterqueue) if you want logging, notification, or auto-resubmission with a retry cap.

Tuning `MaxDeliveryCount`

The default of 10 is often too high for failures that will never succeed and too low for slow-recovering dependencies.
Pair it with how long a failure typically lasts. If a dependency outage can last 10 minutes and messages retry instantly, 10 attempts can be exhausted in seconds, sending healthy messages to the DLQ. This is another reason to add backoff.

4. Duplicate messages

Duplicates arrive from two directions:

Producer-side duplicates. A sender times out after the broker accepted the message and retries, so the broker gets two copies.

Consumer-side duplicates. A message is delivered more than once because the lock expired, the consumer crashed after processing but before completing, or you abandoned it. This is inherent in at-least-once delivery.

Broker-side duplicate detection (producer duplicates)

Enable it when creating the queue or topic and set a detection window (default 10 minutes, max 7 days). Service Bus then discards any message whose MessageId matches one already received within the window.

await adminClient.CreateQueueAsync(new CreateQueueOptions("orders")
{
    RequiresDuplicateDetection = true,
    DuplicateDetectionHistoryTimeWindow = TimeSpan.FromMinutes(30)
});

// Set a deterministic MessageId derived from the business key
var message = new ServiceBusMessage(body) { MessageId = $"order-{order.Id}" };

Important details:

It only works if you set a stable, meaningful MessageId. Random GUIDs defeat it.
It can only be enabled at entity creation time. You can't turn it on later.
It only covers duplicates sent within the window. It does not help with redelivery to consumers.
It isn't supported in the same way on partitioned or some Premium configurations. Check the current docs for your tier.

Idempotent consumers (the real solution)

Because at-least-once delivery can't be fully eliminated, make processing idempotent, so handling a message twice has the same effect as handling it once.

Common approaches:

Natural idempotency: use upserts (MERGE, INSERT ... ON CONFLICT DO NOTHING) and "set status = X" rather than "increment by 1."
Processed-message table (inbox pattern): record MessageId (or a business key) in the same database transaction as the business change. If the insert fails on a unique key, the message was already handled, so skip it and complete it.

// In one DB transaction:
//   INSERT INTO ProcessedMessages (MessageId) VALUES (@id)   -- unique constraint
//   ... apply business change ...
// If the insert violates the constraint: already processed, so just complete the message.

Idempotency keys on downstream calls: pass a stable key to payment providers and similar APIs so they dedupe too.
Check delivery count (message.DeliveryCount > 1) as a hint that a message may have been processed before, then verify against state.

5. Atomic operations with transactions

A common failure mode: you send a follow-up message and then crash before completing the original, producing duplicates or lost work. Service Bus transactions group operations on the same connection so they succeed or fail together:

var client = new ServiceBusClient(fqdn, credential,
    new ServiceBusClientOptions { EnableCrossEntityTransactions = true });

var receiver = client.CreateReceiver("orders");
var sender = client.CreateSender("payments");

var message = await receiver.ReceiveMessageAsync();

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    await sender.SendMessageAsync(new ServiceBusMessage($"charge-{message.MessageId}"));
    await receiver.CompleteMessageAsync(message);
    scope.Complete();
}

Transactions span only Service Bus operations. They do not include your database. For database plus message consistency, use the outbox pattern: write the outgoing message to an outbox table in the same DB transaction as your business change, and have a separate relay publish it to Service Bus (which, combined with consumer idempotency, gives effectively-once results).

6. Ordering and sessions

If order matters, use sessions. They give FIFO per SessionId and exclusive processing by one consumer at a time. Be aware that a failing message in a session can block the rest of that session until it's dead-lettered or completed, so set a sensible MaxDeliveryCount and handle poison messages.

7. Azure Functions behavior

With the Service Bus trigger:

The host calls Complete if your function succeeds and Abandon if it throws (when autoCompleteMessages is true, the default).
Delivery count and DLQ rules are the same as above, so repeated exceptions end in the DLQ after MaxDeliveryCount.
Tune concurrency in host.json:

{
  "extensions": {
    "serviceBus": {
      "maxConcurrentCalls": 16,
      "maxAutoLockRenewalDuration": "00:10:00",
      "autoCompleteMessages": true
    }
  }
}

Use ServiceBusMessageActions with autoCompleteMessages: false if you want explicit control (dead-letter with a reason, defer, and so on).
Setting MaxDeliveryCount too high combined with a flaky dependency can multiply load. Use Functions' built-in retry policies only for non-Service-Bus triggers; for Service Bus, rely on the broker's redelivery and delivery count.

8. Monitoring checklist

Alert on:

Dead-lettered message count > 0
Active message count / queue depth growing steadily (consumers falling behind or failing)
Oldest message age rising
Throttled requests and server errors on the namespace
Lock-lost and processing exceptions in Application Insights

Log MessageId, DeliveryCount, CorrelationId, and the entity name in every handler so a single message can be traced across retries.

What are Durable Functions, and when would you use them?

2026-11-06T19:30:00.000-08:00

Durable Functions

Durable Functions is an extension of Azure Functions that lets you write stateful, long-running workflows as code in a serverless environment. Normal functions are stateless and short-lived. Durable Functions adds orchestration on top: you write a function that calls other functions in sequence or in parallel, waits for events or timers, and survives restarts, scale-out, and crashes without losing its place.

Function types

Client function: starts, queries, signals, or terminates orchestration instances (usually an HTTP, queue, or timer trigger).
Orchestrator function: defines the workflow. It calls activities, awaits results, and decides what happens next.
Activity function: a unit of work (call an API, write to a DB). This is where the actual I/O happens.
Entity function (Durable Entities): a small piece of named, addressable state (a counter, a shopping cart) with operations you can signal or call.

How it works

Durable Functions uses event sourcing and replay. Each step the orchestrator takes is recorded in a history table in the configured storage backend. When an orchestrator awaits something (an activity result, a timer, an external event), it is unloaded from memory, so you pay for nothing while it waits. When the awaited thing completes, the orchestrator is reloaded and replays from the start, using the recorded history to skip work already done, until it reaches the next new step.

This is why orchestrator code has strict rules (see below).

Storage backends: Azure Storage (default), the Netherite backend (high throughput, uses Event Hubs and page blobs), and the Microsoft SQL backend (state in SQL Server or Azure SQL). The Durable Task Scheduler is a newer managed backend; check the current docs for availability and status.

Example (C# isolated worker): function chaining

public static class OrderWorkflow
{
    [Function("OrderOrchestrator")]
    public static async Task<string> RunOrchestrator(
        [OrchestrationTrigger] TaskOrchestrationContext context)
    {
        var order = context.GetInput<Order>();

        await context.CallActivityAsync("ReserveInventory", order);
        await context.CallActivityAsync("ChargePayment", order);
        await context.CallActivityAsync("SendConfirmation", order);

        return $"Order {order.Id} completed";
    }

    [Function("ReserveInventory")]
    public static Task ReserveInventory([ActivityTrigger] Order order, FunctionContext ctx)
    {
        // call inventory service
        return Task.CompletedTask;
    }

    // ChargePayment and SendConfirmation follow the same pattern
}

public static class Starter
{
    [Function("StartOrder")]
    public static async Task<HttpResponseData> Start(
        [HttpTrigger(AuthorizationLevel.Function, "post")] HttpRequestData req,
        [DurableClient] DurableTaskClient client)
    {
        var order = await req.ReadFromJsonAsync<Order>();
        string instanceId = await client.ScheduleNewOrchestrationInstanceAsync(
            "OrderOrchestrator", order);

        return await client.CreateCheckStatusResponseAsync(req, instanceId);
    }
}

The starter returns an HTTP 202 with status-query URLs, so callers can poll for progress.

Common patterns

1. Function chaining: run steps in order, passing output to the next (above).

2. Fan-out/fan-in: run many activities in parallel and aggregate the results.

var tasks = files.Select(f => context.CallActivityAsync<int>("ProcessFile", f));
int[] results = await Task.WhenAll(tasks);
int total = results.Sum();

3. Async HTTP API: start a long operation and return a status endpoint instead of holding a connection open.

4. Monitor: poll something repeatedly until a condition is met, with flexible intervals (a durable timer loop instead of a fragile timer trigger).

5. Human interaction / approval: wait for an external event with a timeout.

using var cts = new CancellationTokenSource();
var approval = context.WaitForExternalEvent<bool>("Approval");
var timeout = context.CreateTimer(context.CurrentUtcDateTime.AddDays(3), cts.Token);

if (await Task.WhenAny(approval, timeout) == approval)
{
    cts.Cancel();   // cancel the timer so the orchestration can complete
    // approved or rejected
}
else
{
    // escalate
}

6. Aggregator (stateful entities): accumulate state from many sources into one addressable entity.

7. Saga / compensation: if a later step fails, run compensating activities (refund payment, release inventory) in a catch block.

Built-in reliability features

Automatic retries for activities with configurable backoff.
Durable timers that can wait minutes, days, or longer at no compute cost.
Sub-orchestrations to compose workflows from smaller ones.
Checkpointing: if the host crashes mid-workflow, it resumes from the last completed step.
Instance management APIs: query, suspend, resume, terminate, rewind, and purge instances.

Orchestrator code constraints

Because the orchestrator is replayed, its code must be deterministic:

Don't use DateTime.UtcNow; use context.CurrentUtcDateTime.
Don't use Guid.NewGuid(); use context.NewGuid().
Don't do I/O (HTTP, DB, file access) or read changing config in the orchestrator; do that in activities.
Don't use Task.Delay or Thread.Sleep; use context.CreateTimer.
Don't use threads or non-durable async constructs; only await durable tasks.
Use context.CreateReplaySafeLogger(...) so logs aren't duplicated on every replay.
Keep inputs, outputs, and history small. Large payloads bloat storage; pass references (blob URLs, IDs) instead.
Version carefully. Changing orchestrator logic while instances are in flight can break replay, so use versioning strategies or drain running instances first.

When to use Durable Functions

Good fits:

Multi-step business processes that must complete reliably (order fulfillment, onboarding, provisioning)
Long-running workflows lasting minutes to days or longer
Human-in-the-loop approvals with timeouts and escalation
Parallel batch processing with aggregation (process thousands of files, then summarize)
Saga-style transactions across services that need compensation
Polling and monitoring loops
Replacing hand-rolled state machines built from queues and tables

Consider something else when:

The work is a single short operation. A plain function is simpler.
You need very high-throughput, ultra-low-latency processing. Orchestration adds storage round-trips, so look at plain queue triggers, Event Hubs, or the Netherite backend.
You want a low-code, visual designer with many connectors. Logic Apps fits better.
The workflow is complex enough to need a dedicated engine with rich versioning, search, and visibility. Temporal or a similar engine may fit, though it brings more operational work.
You need strict message ordering, sessions, and broker features. Service Bus is the better primitive, possibly alongside Durable Functions.

Durable Functions vs. related options

	Durable Functions	Logic Apps	Service Bus + Functions	Plain Functions
Style	Code-first workflows	Visual/low-code designer	Messaging between steps	Single stateless step
State	Managed for you	Managed for you	You manage it	None
Long waits, timers, approvals	Built in	Built in	Manual	Manual
Complex logic and loops	Natural, it's just code	Possible, harder to maintain	Scattered across handlers	N/A
Best for	Developers coding complex workflows	Integration with many SaaS connectors	Simple decoupled processing	Event handlers

They combine well. For instance, a Service Bus-triggered function can start an orchestration, and an orchestration can call out to Logic Apps or other services.

Operations and cost

Monitoring: Application Insights captures orchestration history and traces. Use the instance management APIs, Durable Functions Monitor, or the Durable Task Scheduler dashboard (where available) to inspect state.
Cost: you pay for function execution plus storage transactions for state. Chatty orchestrations with many tiny activities can drive up storage costs, so batch work sensibly.
Hosting: works on Consumption, Flex Consumption, Premium, and Dedicated plans. Check current docs for backend support on each plan.
Housekeeping: purge completed instance history periodically so storage doesn't grow without bound.

Quick rule of thumb

If your workflow is "do A, then B, then C, maybe wait for something, and don't lose my place if something crashes", use Durable Functions.
If it is "react to one event and do one thing", use a normal function.
If you want to click together connectors, use Logic Apps.

What is Azure API Management (APIM), and why do we use it?

2026-11-05T19:30:00.000-08:00

Azure API Management (APIM)

Azure API Management is a managed service that sits in front of your APIs as a gateway. Clients call APIM, and APIM applies policies (authentication, rate limiting, transformation, caching) before forwarding requests to your backends. The backends can be App Service, Functions, AKS, Container Apps, on-premises systems, or even other clouds.

The three components

Gateway: the runtime proxy that receives API calls, enforces policies, and routes to backends. This is the data plane.
Management plane: the Azure portal, CLI, Bicep, or REST API, where you define APIs, products, policies, and users.
Developer portal: an auto-generated, customizable website where API consumers browse docs, try calls, and get subscription keys.

Self-hosted gateway: you can also run the gateway in containers on-premises or in other clouds, managed from the same APIM instance (Developer and Premium tiers).

Core concepts

API: a set of operations (e.g., GET /orders/{id}), imported from OpenAPI, WSDL, an App Service, a Function App, or created manually.
Product: a bundle of APIs with its own terms, quotas, and visibility (e.g., "Free" vs. "Premium" plans).
Subscription: a key a consumer uses to access a product or API (sent in the Ocp-Apim-Subscription-Key header by default).
Policy: XML statements that run on the request or response pipeline at global, product, API, or operation scope.
Named values: configuration and secrets used inside policies (can be backed by Key Vault).
Backend: a reusable definition of a downstream service, with URL, credentials, and circuit breaker settings.

Why use it

1. Security at the edge

Validate JWTs from Entra ID before requests reach your backend
Enforce subscription keys, client certificates, and IP filtering
Authenticate to backends with managed identity, so backends never expose public credentials
Centralize security policies instead of re-implementing them in every service

2. Traffic control

Rate limiting and quotas per client, per key, or per IP
Protection against abusive callers and backend overload
Response caching to reduce backend load and latency
Retry and circuit breaker behavior for flaky backends

3. A single, consistent front door

One public URL and domain across many backend services
Backends can move or be replaced without breaking consumers
Consistent URL structure, headers, and error formats

4. Versioning and lifecycle

Run multiple versions (/v1, /v2) side by side
Revisions let you make non-breaking changes and test them before making them current

5. Transformation and mediation

Rewrite URLs, add or strip headers, convert XML to JSON, mask fields in responses
Expose a clean API over a messy legacy backend without changing it

6. Observability

Built-in analytics, plus diagnostic logs and traces to Application Insights and Azure Monitor
Correlation IDs, per-API and per-consumer usage and latency

7. Developer experience and monetization

Self-service portal with documentation and a test console
Products and subscriptions let you onboard partners, issue keys, and apply different limits per tier

Example policies

Validate an Entra ID token, rate limit, and forward using managed identity:

<policies>
  <inbound>
    <base />
    <validate-jwt header-name="Authorization" failed-validation-httpcode="401"
                  failed-validation-error-message="Unauthorized">
      <openid-config url="https://login.microsoftonline.com/<tenant-id>/v2.0/.well-known/openid-configuration" />
      <audiences>
        <audience>api://<api-client-id></audience>
      </audiences>
      <required-claims>
        <claim name="roles" match="any">
          <value>Orders.Read</value>
        </claim>
      </required-claims>
    </validate-jwt>

    <rate-limit-by-key calls="100" renewal-period="60"
                       counter-key="@(context.Request.IpAddress)" />

    <authentication-managed-identity resource="api://<backend-client-id>" />
  </inbound>
  <backend>
    <base />
  </backend>
  <outbound>
    <base />
    <set-header name="X-Powered-By" exists-action="delete" />
  </outbound>
  <on-error>
    <base />
  </on-error>
</policies>

Response caching:

<inbound>
  <base />
  <cache-lookup vary-by-developer="false" vary-by-developer-groups="false" />
</inbound>
<outbound>
  <base />
  <cache-store duration="60" />
</outbound>

Tiers

Tier	Typical use
Consumption	Serverless, pay per call, scale to zero; good for low or spiky traffic (no VNet injection, limited features)
Developer	Non-production, for evaluation and testing (no SLA)
Basic / Standard	Small to mid-sized production workloads (classic tiers)
Premium	Multi-region, availability zones, VNet injection, self-hosted gateway, highest scale
v2 tiers (Basic v2, Standard v2, Premium v2)	Newer generation with faster provisioning and scaling, and simpler VNet integration

Tier names and feature sets change over time, so check the current pricing and feature comparison before choosing.

Typical architecture

Client ─► Front Door / App Gateway (WAF) ─► APIM ─► Backends
                                              │       ├─ App Service (Orders API)
                                              │       ├─ Functions (Webhooks)
                                              │       └─ AKS / Container Apps (Inventory)
                                              └─► Application Insights

Lock backends down so they only accept traffic from APIM (access restrictions, private endpoints, or a shared header or token check). Otherwise callers can bypass your policies by hitting the backend directly.

When APIM is a good fit

You expose APIs to external partners or the public and need keys, quotas, and a developer portal
You have multiple backend services and want one consistent entry point
You need centralized auth, throttling, and logging without changing each service
You're modernizing legacy services and want to hide their quirks behind a clean facade
You need to publish APIs across regions or hybrid environments

When it may be overkill

A single internal API with one consumer, where app-level auth and Easy Auth suffice
Very low-budget or prototype projects (the non-Consumption tiers carry a meaningful fixed monthly cost)
Scenarios needing only WAF and routing, where Front Door or Application Gateway is simpler
Latency-critical hot paths, where every extra hop matters (APIM adds some overhead, usually small)

APIM vs. related services

	APIM	Front Door / App Gateway	Easy Auth
Focus	API lifecycle, policy, consumer management	Global routing, WAF, load balancing	Simple built-in authentication
Understands API operations and products	Yes	No	No
Rate limiting per consumer/key	Yes	Basic (WAF rate rules)	No
Developer portal	Yes	No	No

They're complementary: Front Door provides global entry and WAF, and APIM provides API governance behind it.

Good practices:

Define APIM as code (Bicep or Terraform, with policies in versioned files) and deploy through CI/CD. Use the APIOps approach to promote API definitions between environments.
Use managed identity for backend authentication, and Key Vault-backed named values for secrets.
Validate tokens in APIM and also in the backend (defense in depth), and restrict backends to APIM traffic only.
Keep policies simple and reusable with fragments, because complex policy logic is hard to test and debug.
Send diagnostics to Application Insights and watch for gateway versus backend latency and failures.
Use revisions and versions deliberately: revisions for non-breaking changes, versions for breaking ones.
Plan capacity and region placement: choose a tier that supports the VNet and availability needs you actually have, because some of these can't be changed later without redeploying.

What deployment options are available for a Web API in Azure—App Service or AKS?

2026-11-04T19:30:00.000-08:00

Deployment Options for a Web API in Azure

App Service and AKS sit at opposite ends of a spectrum: App Service is a managed platform where you deploy code or a container and Azure runs everything else, while AKS is managed Kubernetes where you get full control of the orchestration layer and take on the operational work that comes with it. There are also middle-ground options, covered below.

App Service

A PaaS for web apps and APIs. You deploy code (zip, Git, CI/CD) or a single container, and Azure handles the OS, patching, load balancing, TLS, and scaling.

Strengths:

Fastest path to production, with very little operational overhead
Built-in deployment slots (Standard tier and above) for zero-downtime swaps and instant rollback
Autoscale, custom domains, managed certificates, Easy Auth, VNet integration, and managed identity
Native .NET support, with no Dockerfile required
A predictable, per-plan price

Limitations:

Less control over networking, scheduling, and the runtime environment
Multi-container setups are limited. It's one app per container, with no real orchestration
Not suited to complex microservice meshes, sidecars, or custom operators
Scale is bounded by plan limits (though it's plenty for most APIs)

Deploying:

az webapp up --name my-api --resource-group my-rg --runtime "dotnet:8"
# or, in CI/CD: azure/webapps-deploy@v3 with a staging slot, then swap

AKS (Azure Kubernetes Service)

Managed Kubernetes. Azure runs the control plane, and you manage the workloads, node pools, networking, ingress, and upgrades. AKS Automatic is a newer mode that manages more for you (node provisioning, scaling, and defaults), which narrows the gap with simpler platforms.

Strengths:

Full Kubernetes ecosystem: Helm, operators, service meshes, GitOps, custom controllers
Fine-grained control over scheduling, networking, resource limits, and node types (including GPU or spot nodes)
Good for many services, sidecars, and portability across clouds
Efficient bin-packing of many workloads onto shared nodes

Limitations:

Real operational burden: cluster upgrades, node patching, ingress, certificates, observability, and security hardening
Needs Kubernetes expertise on the team
You must containerize everything
Costs include the nodes (paid whether busy or idle) plus the effort to run the platform

Deploying:

az acr build --registry myacr --image my-api:1.0.0 .
az aks get-credentials --name my-aks --resource-group my-rg
kubectl apply -f deployment.yaml

apiVersion: apps/v1
kind: Deployment
metadata: { name: my-api }
spec:
  replicas: 3
  selector: { matchLabels: { app: my-api } }
  template:
    metadata: { labels: { app: my-api } }
    spec:
      containers:
      - name: my-api
        image: myacr.azurecr.io/my-api:1.0.0
        ports: [{ containerPort: 8080 }]
        readinessProbe: { httpGet: { path: /health, port: 8080 } }
        resources:
          requests: { cpu: 250m, memory: 256Mi }
          limits: { memory: 512Mi }

You'd also add a Service and an Ingress (for example, Application Gateway for Containers or NGINX), use workload identity for Azure access, and roll out with Helm or Flux/Argo CD.

Side-by-side

	App Service	AKS
Model	PaaS, you deploy an app	Managed Kubernetes, you run workloads
Packaging	Code or container	Container (required)
Operational effort	Low	High
Learning curve	Gentle	Steep
Scaling	Autoscale rules, per plan	Pod autoscaling (HPA/KEDA) plus node autoscaling
Zero-downtime release	Deployment slots	Rolling updates, canary, blue/green via ingress or mesh
Networking control	Moderate (VNet integration, private endpoints)	Very high (CNI options, network policies, service mesh)
Multi-service / microservices	Possible, one plan hosting many apps	Native fit
Cost model	Pay per plan	Pay for nodes, plus platform effort
Best for	Typical APIs and web apps	Large, complex, multi-service platforms

Other options worth considering

Azure Container Apps: serverless containers built on Kubernetes (with KEDA and Dapr) but without cluster management. Supports scale-to-zero, revisions with traffic splitting, and event-driven scaling. For many teams it's the best middle ground between App Service and AKS.
Azure Functions: for small, event-driven, or bursty APIs where per-execution billing and scale-to-zero make sense.
App Service Environment (ASE v3): single-tenant, VNet-injected App Service for strict isolation and compliance needs.
Azure Container Instances: simple, short-lived containers. Rarely the right choice for a production API on its own.
Virtual Machines: only if you need OS-level control or have legacy requirements.

How to choose

A single API or a handful, a small or mid-sized team, and you want to ship quickly → App Service
Containerized microservices, but you don't want to run Kubernetes → Container Apps
Spiky or event-driven workloads, minimal API surface → Functions
Dozens of services, a platform team, a need for service mesh, operators, custom scheduling, or multi-cloud portability → AKS
Strict network isolation or compliance requirements on a PaaS model → App Service Environment

A practical rule: start with App Service (or Container Apps) and move to AKS only when you hit a concrete limit. Teams often adopt AKS for the flexibility they imagine they'll need, then spend a lot of effort on cluster operations that App Service would have handled. Because both can run the same container image, moving later is less painful if you containerize from the start.

Cross-cutting concerns (the same on all of them)

Use managed identity and Key Vault instead of stored credentials.
Put APIM, Front Door, or Application Gateway in front for routing, WAF, and rate limiting.
Send telemetry to Application Insights and define health checks and alerts.
Deploy through CI/CD with infrastructure as code (Bicep or Terraform).

How do you implement CI/CD for a .NET application in Azure?

2026-11-03T19:30:00.000-08:00

CI/CD for a .NET Application in Azure

CI (continuous integration) builds and tests every change automatically. CD (continuous delivery/deployment) ships the tested build through environments to production in a repeatable, low-risk way. The two common tool choices are GitHub Actions and Azure Pipelines (Azure DevOps). The structure is the same in both.

The pipeline shape

Commit/PR ─► Build ─► Test ─► Publish artifact ─► Deploy Dev ─► Deploy Staging ─► (approval) ─► Production
                                  (build once)                    (slot / test)                  (swap)

Principles:

Build once, deploy the same artifact everywhere. Don't rebuild per environment. Differences come from configuration, not code.
Run tests on every PR and block merges when they fail (branch protection).
No stored credentials. Authenticate to Azure with OIDC (workload identity federation).
Gate production with approvals and automated checks.

Option 1: GitHub Actions

One-time setup: passwordless Azure login

Create an app registration (or user-assigned managed identity), add a federated credential trusting your repo, and grant it a role on the target scope.

# App registration + service principal
az ad app create --display-name gh-deploy-my-app
az ad sp create --id <appId>

# Trust GitHub Actions for the main branch
az ad app federated-credential create --id <appId> --parameters '{
  "name": "gh-main",
  "issuer": "https://token.actions.githubusercontent.com",
  "subject": "repo:my-org/my-repo:environment:production",
  "audiences": ["api://AzureADTokenExchange"]
}'

# Least-privilege role at resource group scope
az role assignment create --assignee <appId> --role "Website Contributor" \
  --scope /subscriptions/<sub-id>/resourceGroups/my-rg

The subject must match how the workflow runs. For jobs using a GitHub environment, use environment:<name>. For branch-based runs, use ref:refs/heads/main. You need one federated credential per subject.

Store AZURE_CLIENT_ID, AZURE_TENANT_ID, and AZURE_SUBSCRIPTION_ID as repository secrets or variables. They're identifiers, not credentials.

Workflow

name: ci-cd

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

permissions:
  id-token: write   # needed for OIDC
  contents: read

jobs:
  build-test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-dotnet@v4
        with:
          dotnet-version: '8.0.x'

      - run: dotnet restore
      - run: dotnet build --no-restore -c Release
      - run: dotnet test --no-build -c Release --logger trx --collect:"XPlat Code Coverage"

      - run: dotnet publish src/MyApi/MyApi.csproj -c Release -o ./publish --no-build

      - uses: actions/upload-artifact@v4
        with:
          name: api
          path: ./publish

  deploy-staging:
    needs: build-test
    if: github.ref == 'refs/heads/main'   # PRs build and test only
    runs-on: ubuntu-latest
    environment: staging
    steps:
      - uses: actions/download-artifact@v4
        with: { name: api, path: ./publish }

      - uses: azure/login@v2
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

      - uses: azure/webapps-deploy@v3
        with:
          app-name: my-api
          slot-name: staging
          package: ./publish

      - name: Smoke test
        run: curl --fail --retry 5 --retry-delay 5 https://my-api-staging.azurewebsites.net/health

  deploy-production:
    needs: deploy-staging
    runs-on: ubuntu-latest
    environment: production        # configure required reviewers on this environment
    steps:
      - uses: azure/login@v2
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

      - name: Swap staging into production
        run: |
          az webapp deployment slot swap \
            --name my-api --resource-group my-rg \
            --slot staging --target-slot production

Notes:

GitHub Environments (staging, production) give you required reviewers, wait timers, and environment-scoped secrets.
Deployment slots require Standard tier or above. Slot swaps warm up the staging instance first, so production sees no cold start.
To roll back, swap again. The previous version is still sitting in the staging slot.

Option 2: Azure Pipelines (YAML)

Create a service connection to Azure using workload identity federation (the recommended option over secret-based connections).

trigger:
  branches:
    include: [main]

pool:
  vmImage: ubuntu-latest

variables:
  buildConfiguration: Release

stages:
- stage: Build
  jobs:
  - job: BuildTest
    steps:
    - task: UseDotNet@2
      inputs:
        packageType: sdk
        version: 8.0.x

    - script: dotnet restore
    - script: dotnet build --no-restore -c $(buildConfiguration)

    - task: DotNetCoreCLI@2
      displayName: Test
      inputs:
        command: test
        projects: '**/*Tests.csproj'
        arguments: '-c $(buildConfiguration) --no-build'

    - task: DotNetCoreCLI@2
      displayName: Publish
      inputs:
        command: publish
        publishWebProjects: true
        arguments: '-c $(buildConfiguration) -o $(Build.ArtifactStagingDirectory)'
        zipAfterPublish: true

    - publish: $(Build.ArtifactStagingDirectory)
      artifact: drop

- stage: Staging
  dependsOn: Build
  condition: and(succeeded(), eq(variables['Build.SourceBranch'], 'refs/heads/main'))
  jobs:
  - deployment: DeployStaging
    environment: staging
    strategy:
      runOnce:
        deploy:
          steps:
          - task: AzureWebApp@1
            inputs:
              azureSubscription: my-service-connection
              appType: webAppLinux
              appName: my-api
              deployToSlotOrASE: true
              resourceGroupName: my-rg
              slotName: staging
              package: $(Pipeline.Workspace)/drop/**/*.zip

- stage: Production
  dependsOn: Staging
  jobs:
  - deployment: DeployProd
    environment: production      # add Approvals and checks on this environment
    strategy:
      runOnce:
        deploy:
          steps:
          - task: AzureAppServiceManage@0
            inputs:
              azureSubscription: my-service-connection
              action: Swap Slots
              webAppName: my-api
              resourceGroupName: my-rg
              sourceSlot: staging
              targetSlot: production

Azure DevOps Environments provide approvals, checks, and deployment history, the same role that GitHub Environments play.

Infrastructure as Code

Don't create Azure resources by hand. Define them in Bicep (or Terraform) and deploy them through the pipeline, so environments stay consistent and reviewable.

- uses: azure/arm-deploy@v2
  with:
    scope: resourcegroup
    resourceGroupName: my-rg
    template: ./infra/main.bicep
    parameters: ./infra/prod.bicepparam
    failOnStdErr: false

A common pattern is to deploy infrastructure first and the app second. Use what-if on pull requests to preview infrastructure changes before merging.

Database migrations

Don't let the app run migrations on startup in production. Prefer an explicit pipeline step:

# Idempotent SQL script, reviewable and safe to re-run
dotnet ef migrations script --idempotent -o migrate.sql \
  --project src/MyApi --startup-project src/MyApi

# Or a self-contained migration bundle
dotnet ef migrations bundle -o efbundle --project src/MyApi
./efbundle --connection "<connection-string>"

Authenticate to SQL with the pipeline's Entra identity rather than a password. Write migrations in an expand/contract style (add new columns and tables first, remove old ones later) so the previous version keeps working during a slot swap or rollback.

Configuration and secrets per environment

Keep non-secret settings in app settings, applied per slot or environment. Mark settings that must not move during a swap as slot settings (sticky).
Store secrets in Key Vault and use Key Vault references. Give the app a managed identity to read them.
Never put secrets in the repo or the build artifact. Use environment-scoped secrets only for pipeline identifiers.
Use Azure App Configuration with feature flags if you want to release code dark and enable features separately.

Containerized apps

If you deploy containers (Container Apps, AKS, App Service for Containers):

- run: |
    az acr login --name myacr
    docker build -t myacr.azurecr.io/my-api:${{ github.sha }} .
    docker push myacr.azurecr.io/my-api:${{ github.sha }}

- run: |
    az containerapp update --name my-api --resource-group my-rg \
      --image myacr.azurecr.io/my-api:${{ github.sha }}

Tag images with the commit SHA (never rely on latest), and give the app a managed identity with the AcrPull role. Container Apps revisions let you shift traffic gradually (blue/green or canary). For AKS, use Helm or GitOps (Flux/Argo CD).

Quality and safety gates

Add these as the pipeline matures:

Static analysis and security: Roslyn analyzers, dotnet list package --vulnerable, Dependabot or Renovate, CodeQL, secret scanning, and container image scanning (Defender for Cloud).
Integration tests against ephemeral resources (Testcontainers or a dedicated test environment).
Post-deploy smoke tests and health checks, which block the swap if they fail.
Load or performance checks on staging for critical paths.
Automated rollback if post-release metrics or alerts degrade.
Branch protection: required reviews, required status checks, and no direct pushes to main.

Good practices

Keep pipelines in the repo (YAML), versioned and reviewed like code.
Cache NuGet packages and use --no-restore and --no-build flags to speed up runs.
Use reusable workflows or templates to share logic across services.
Separate subscriptions or resource groups per environment, and use separate identities with least privilege for each.
Version your builds (for example, with MinVer or the commit SHA) and surface the version in a /health or /version endpoint, so you can confirm what's running.
Mark deployments in Application Insights (release annotations) so you can correlate regressions with releases.
Prefer small, frequent releases over large batches. They're easier to test and roll back.

Minimal starting point:

Create a workflow that restores, builds, and tests on every PR.
Add OIDC-based Azure login and deploy to a staging slot on merge to main.
Add a smoke test and a manual approval on a production environment, then swap slots.
Move infrastructure into Bicep and deploy it from the pipeline.
Add security scanning, migrations as a controlled step, and alerts tied to releases.

Find employees who have the same salary as their manager.

2026-11-03T19:30:00.000-08:00

To find employees who earn the exact same salary as their manager, you need to perform a Self-Join. This means you join the employees table to itself—treating one instance as the "Employee table" and the second instance as the "Manager table."

The Query

The join links the employee's manager_id to the manager's employee_id, and the WHERE clause filters for matching salary values:

sql

SELECT 
    e.employee_id AS emp_id,
    e.employee_name AS emp_name,
    e.salary AS emp_salary,
    m.employee_name AS manager_name,
    m.salary AS manager_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary = m.salary;

Use code with caution.

How It Works

employees e: Represents the table context looking down at the regular employees.
employees m: Represents the table context looking up at the managers.
ON e.manager_id = m.employee_id: Establishes the relationship path between the employee and their boss.
WHERE e.salary = m.salary: Isolates the records where both parties make the exact same amount of money.

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the logic live:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- 2. INSERT SAMPLE DATA
-- Top levels (No managers)
INSERT INTO employees (employee_id, employee_name, salary, manager_id) VALUES
(1, 'Fiona Gallagher', 120000.00, NULL), -- CEO
(2, 'Alice Smith', 95000.00, NULL);     -- VP

-- Reports
INSERT INTO employees (employee_id, employee_name, salary, manager_id) VALUES
(3, 'Bob Jones', 75000.00, 1),      -- Reports to Fiona (Diff Salary)
(4, 'Charlie Brown', 95000.00, 2),  -- Reports to Alice (SAME Salary - Matches!)
(5, 'Diana Prince', 95000.00, 2),   -- Reports to Alice (SAME Salary - Matches!)
(6, 'Evan Wright', 60000.00, 3);    -- Reports to Bob (Diff Salary)

-- 3. EXECUTE THE SELF-JOIN QUERY
SELECT 
    e.employee_name AS employee_name,
    e.salary AS employee_salary,
    m.employee_name AS manager_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary = m.salary;

Use code with caution.

Expected Query Output

employee_name	employee_salary	manager_name
Charlie Brown	95000.00	Alice Smith
Diana Prince	95000.00	Alice Smith

How do you troubleshoot a production API issue in Azure?

2026-11-02T19:30:00.000-08:00

Troubleshooting a Production API Issue in Azure

The goal is to stabilize first, diagnose second, fix third. Work from the outside in: is it the platform, the network, your code, or a dependency?

1. Triage (first 5 minutes)

Establish the facts before touching anything:

What is the symptom? Errors (5xx/4xx), slowness, timeouts, or total outage?
Scope: all users or some? All endpoints or one? One region or instance, or everywhere?
When did it start? Line it up against deployments, config changes, certificate expiries, and traffic spikes. Most incidents trace back to a recent change.
Is it Azure? Check Azure Service Health and Resource Health for your subscription and the specific resource. Platform incidents and maintenance show up here.

If a recent deployment is the likely cause, roll back first and investigate later. With deployment slots, swap back. That's the fastest mitigation you have.

2. Check the golden signals in Application Insights

Open Application Insights and go through these in order:

Failures blade: which operations and exception types are spiking, with sample failed requests.
Performance blade: which operations got slower, and whether the time is in your code or in a dependency.
Application Map: which node is red (slow or failing). This shows quickly whether the API or something it calls is at fault.
Live Metrics: real-time view while you test or mitigate.
End-to-end transaction details: follow a single failed request through all services using its operation ID.

Useful KQL:

// Failure trend by result code
requests
| where timestamp > ago(2h)
| summarize total = count(), failed = countif(success == false) by bin(timestamp, 5m)
| extend failureRate = todouble(failed) / total
| render timechart

// Is it a dependency?
dependencies
| where timestamp > ago(2h) and success == false
| summarize count() by target, type, resultCode
| order by count_ desc

// Latency percentiles by operation
requests
| where timestamp > ago(2h)
| summarize p50 = percentile(duration, 50), p95 = percentile(duration, 95), count() by name
| order by p95 desc

// Top exceptions
exceptions
| where timestamp > ago(2h)
| summarize count() by type, outerMessage
| order by count_ desc

Compare the bad period against the same time yesterday or last week to separate real regressions from normal patterns.

3. Narrow down by symptom

Symptom	Likely causes	Where to look
500s after a deploy	Bug, missing config, bad migration	Exceptions, deployment history, diff of app settings
500s / timeouts, no deploy	Failing dependency (SQL, Redis, downstream API), expired secret or cert	Dependencies table, Application Map
401/403	Expired token or secret, changed app role or RBAC, clock/audience mismatch, rotated key	Entra sign-in logs, role assignments, Key Vault access logs
429	Throttling by a downstream service (Cosmos DB RU limits, Key Vault, Storage) or by APIM	Response headers, service metrics
502/503/504	App crashing or restarting, instances unhealthy, gateway timeout (App Gateway/Front Door ~230s limit on App Service)	Health check status, restarts, Diagnose and solve problems
Slow responses	Slow SQL queries, cold starts, thread pool starvation, CPU/memory pressure, chatty calls	Performance blade, dependency durations, App Service metrics
Intermittent failures	One bad instance, SNAT port exhaustion, transient faults without retries, DNS issues	Per-instance breakdown (`cloud_RoleInstance`), connection metrics
Works in one place, not another	NSG/firewall/private endpoint/DNS change	Network diagnostics (below)

4. Platform-level diagnostics

App Service / Functions:

Diagnose and solve problems blade: guided detectors for HTTP 5xx, high CPU, memory, slow requests, SNAT exhaustion, and app crashes. Start here when the cause isn't obvious.
Metrics: CPU, memory working set, requests, response time, HTTP 5xx, and instance count. Look for saturation or a scale-out that didn't happen.
Log stream for live output: az webapp log tail --name my-app --resource-group my-rg
Kudu (SCM) console for process explorer, environment variables, and crash dumps.
Health check: verify the path is configured and which instances are being removed from rotation.

Containers / AKS / Container Apps:

kubectl get pods, kubectl describe pod, kubectl logs --previous for restart loops (CrashLoopBackOff, OOMKilled).
Check readiness and liveness probes, resource limits, and node health.
For Container Apps, use the Log stream and ContainerAppConsoleLogs / ContainerAppSystemLogs tables.

APIM / Front Door / Application Gateway (if in front):

Check whether failures originate at the gateway or the backend. Gateway logs show backend response codes, backend time vs. total time, and WAF blocks.
A WAF rule or rate limit blocking legitimate traffic often looks like an app problem.

5. Check dependencies

Most production API incidents originate in something the API calls:

Azure SQL: Query Performance Insight, blocking and deadlocks, DTU/vCore saturation, connection limits, failover events. Check whether a new query plan or missing index is the cause.
Cosmos DB: 429s and RU consumption, hot partitions.
Service Bus / Event Hubs: growing queue depth, dead-letter counts, lock-expired errors, throttling.
Key Vault: throttling, or access denied after a role or network change.
Redis: memory pressure, evictions, connection limits.
Storage: throttling and latency metrics.
Third-party APIs: check their status pages and your outgoing call durations.

6. Network and connectivity

If the app can't reach something or can't be reached:

Network Watcher: Connection troubleshoot, IP flow verify, and NSG flow logs show whether a rule is dropping traffic.
DNS: private endpoints need correct private DNS zone links. From Kudu, run nameresolver <host> and tcpping <host:port> (on Windows App Service).
Firewall and access restrictions: recent changes to storage, SQL, or Key Vault firewalls often cause sudden 403s or timeouts.
SNAT port exhaustion: many outbound connections to the same endpoint can exhaust ports. Reuse HttpClient through IHttpClientFactory, use connection pooling, or add a NAT Gateway.
TLS and certificates: expired or rotated certificates cause sudden handshake failures.

7. Compare against change history

Ask "what changed?" and verify it from records, not memory:

Activity Log: shows control-plane changes (config updates, scaling, role assignments, deployments, key rotations). Filter by time window around the incident start.
Deployment history / CI-CD pipeline runs and any app setting changes.
Entra audit logs: app registration, secret, or permission changes.
Resource Graph or Change Analysis (in Diagnose and solve problems) to see configuration diffs.

8. Go deeper when needed

Memory or CPU issues: capture a memory dump or profiler trace through Diagnose and solve problems (Collect Memory Dump, Profiler) or dotnet-dump/dotnet-trace in containers.
Thread pool starvation (common with sync-over-async): look for rising response times with low CPU, and check ThreadPool metrics in Application Insights or dotnet-counters.
Reproduce in a lower environment using the same request payload and the operation ID details from the failure.
Increase logging temporarily for the affected component, then revert it. Sampling can hide rare failures, so check whether it's dropping the data you need.

9. Mitigate while you investigate

Roll back or swap slots.
Scale up/out if it's resource saturation (buys time, doesn't fix the cause).
Restart the app or a single bad instance as a last resort. Note the time and capture evidence first, because a restart destroys state.
Disable the faulty feature with a feature flag, or route traffic away (Front Door / Traffic Manager) from an unhealthy region.
Add retries with backoff and circuit breakers (Polly, or Microsoft.Extensions.Http.Resilience) if a dependency is flaky.
Communicate: post status updates early, even if the cause is unknown.

10. Afterwards

Write a blameless post-incident review: timeline, root cause, detection gap, and action items.
Add the missing alert that would have caught it sooner (failure rate, p95 latency, dependency failures, queue depth, certificate and secret expiry).
Add health checks, synthetic availability tests (Standard tests), and dashboards for the signals you had to hunt for.
Make the fix safer to ship: deployment slots, canary releases, automated rollback, and load testing for the failure mode.

Quick checklist:

Check Service Health and recent changes (deploys, config, secrets, certs).
Open Application Insights: Failures, Performance, Application Map.
Decide: my code, a dependency, or the platform/network?
Pull logs and traces for a single failing operation ID.
Mitigate (rollback, scale, failover) before the deep dive if users are affected.
Confirm recovery from the metrics, then run the post-incident review.

How do you implement logging and monitoring using Application Insights?

2026-11-02T19:30:00.000-08:00

Logging and Monitoring with Application Insights

Application Insights is the application performance monitoring (APM) feature of Azure Monitor. It collects requests, dependencies (SQL, HTTP, Service Bus), exceptions, traces, custom metrics, and custom events, and gives you tools to query, visualize, and alert on them.

Current Application Insights resources are workspace-based: the data is stored in a Log Analytics workspace, so you can query it alongside other Azure logs with KQL. Classic (non-workspace) resources are retired, so create new ones as workspace-based.

1. Create the resource

az monitor log-analytics workspace create \
  --resource-group my-rg --workspace-name my-logs --location centralindia

az monitor app-insights component create \
  --app my-app-insights --resource-group my-rg --location centralindia \
  --workspace my-logs --kind web

Get the connection string (use this, not the old instrumentation key alone):

az monitor app-insights component show --app my-app-insights \
  --resource-group my-rg --query connectionString -o tsv

2. Instrument a .NET app

There are two options. Microsoft's recommended path for new ASP.NET Core apps is the Azure Monitor OpenTelemetry Distro, which replaces the older Application Insights SDK as the main approach. The classic SDK still works and is fine for existing apps.

Option A: Azure Monitor OpenTelemetry (recommended)

dotnet add package Azure.Monitor.OpenTelemetry.AspNetCore

using Azure.Monitor.OpenTelemetry.AspNetCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddOpenTelemetry().UseAzureMonitor();

var app = builder.Build();

Set the connection string via configuration (not in code):

APPLICATIONINSIGHTS_CONNECTION_STRING=InstrumentationKey=...;IngestionEndpoint=https://...

This automatically collects incoming requests, outgoing HTTP calls, SQL calls, exceptions, ILogger output, and performance counters.

Option B: Classic Application Insights SDK

dotnet add package Microsoft.ApplicationInsights.AspNetCore

builder.Services.AddApplicationInsightsTelemetry();

Again, provide APPLICATIONINSIGHTS_CONNECTION_STRING through app settings or environment variables.

Azure Functions (isolated worker)

dotnet add package Microsoft.Azure.Functions.Worker.ApplicationInsights

var host = new HostBuilder()
    .ConfigureFunctionsWebApplication()
    .ConfigureServices(services =>
    {
        services.AddApplicationInsightsTelemetryWorkerService();
        services.ConfigureFunctionsApplicationInsights();
    })
    .Build();

Note that the isolated worker applies a default logging filter that can hide Information logs from your code. If your logs are missing, remove it in ConfigureLogging.

App Service with no code changes

For App Service, you can enable Application Insights under the app's Monitoring blade (auto-instrumentation, "codeless"). It's the quickest start, but code-based instrumentation gives you more control for custom telemetry.

3. Logging with `ILogger`

Use the standard .NET ILogger<T>. Application Insights captures it automatically, and your structured properties become searchable custom dimensions.

public class OrdersController : ControllerBase
{
    private readonly ILogger<OrdersController> _logger;

    public OrdersController(ILogger<OrdersController> logger) => _logger = logger;

    [HttpPost]
    public IActionResult Create(Order order)
    {
        _logger.LogInformation("Creating order {OrderId} for customer {CustomerId}",
            order.Id, order.CustomerId);
        try
        {
            // ...
            return Ok();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to create order {OrderId}", order.Id);
            throw;
        }
    }
}

Use message templates with named placeholders, not string interpolation. Templates keep the properties as structured fields you can filter on.

Set log levels in appsettings.json. Note the provider-specific section so you control what is sent to Application Insights:

{
  "Logging": {
    "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" },
    "ApplicationInsights": { "LogLevel": { "Default": "Information" } }
  }
}

4. Custom telemetry

Events and metrics (with the OpenTelemetry distro, use System.Diagnostics.Metrics; with the classic SDK, use TelemetryClient):

// Custom metric with OpenTelemetry / .NET Meter
static readonly Meter Meter = new("MyApp.Orders");
static readonly Counter<long> OrdersCreated = Meter.CreateCounter<long>("orders_created");

OrdersCreated.Add(1, new KeyValuePair<string, object?>("region", "EU"));

builder.Services.AddOpenTelemetry()
    .UseAzureMonitor()
    .WithMetrics(m => m.AddMeter("MyApp.Orders"));

// Classic SDK
_telemetryClient.TrackEvent("OrderPlaced",
    new Dictionary<string, string> { ["region"] = "EU" });
_telemetryClient.GetMetric("OrderValue").TrackValue(order.Total);

Custom spans (distributed tracing) with ActivitySource:

static readonly ActivitySource Source = new("MyApp.Orders");

using var activity = Source.StartActivity("ProcessPayment");
activity?.SetTag("order.id", order.Id);

5. Distributed tracing across services

Application Insights correlates calls across services using the W3C traceparent header. Requests, dependencies, logs, and exceptions that belong to one operation share an operation ID, so you can follow a single user action across APIs, queues, and functions. Use Application Map and the End-to-end transaction details view to see this visually. Service Bus and HTTP calls propagate context automatically with the standard Azure SDKs.

Set a meaningful cloud role name so each service appears as its own node on the map:

builder.Services.AddOpenTelemetry()
    .ConfigureResource(r => r.AddService("orders-api"))
    .UseAzureMonitor();

6. Querying with KQL

In Application Insights → Logs (or the Log Analytics workspace):

// Failed requests in the last hour
requests
| where timestamp > ago(1h) and success == false
| summarize count() by resultCode, name
| order by count_ catch desc

Correction: catch isn't valid KQL there. The correct form is:

requests
| where timestamp > ago(1h) and success == false
| summarize failures = count() by resultCode, name
| order by failures desc

// Slowest operations (p95)
requests
| where timestamp > ago(24h)
| summarize p95 = percentile(duration, 95) by name
| order by p95 desc

// Exceptions with context
exceptions
| where timestamp > ago(24h)
| project timestamp, type, outerMessage, operation_Id, cloud_RoleName

// All telemetry for one operation
union requests, dependencies, traces, exceptions
| where operation_Id == "<id>"
| order by timestamp asc

// Slow or failing dependencies (SQL, HTTP, Service Bus)
dependencies
| where timestamp > ago(24h) and success == false
| summarize count() by target, type

Note: in a workspace-based resource queried from the Log Analytics workspace, the table names are AppRequests, AppDependencies, AppExceptions, AppTraces, and so on, with different column names (TimeGenerated, DurationMs). Queries from the Application Insights Logs blade use the classic names shown above.

7. Monitoring features worth using

Live Metrics: near-real-time view of requests, failures, and CPU while you deploy or debug.
Application Map: dependency graph with failure and latency per link.
Failures and Performance blades: group exceptions and slow operations, then drill into samples.
Availability tests: synthetic checks from multiple regions (URL ping is being replaced by Standard tests; use those).
Smart detection: automatic anomaly alerts for failure-rate and latency changes.
Workbooks and dashboards: shared visual reports built from KQL.
Profiler and Snapshot Debugger: deeper diagnostics for slow code and exceptions (primarily classic SDK and App Service scenarios).

8. Alerts

Create alert rules on metrics or on KQL queries, and route them to an action group (email, SMS, Teams, webhook, Logic App).

az monitor metrics alert create \
  --name high-failed-requests --resource-group my-rg \
  --scopes <app-insights-resource-id> \
  --condition "count requests/failed > 10" \
  --window-size 5m --evaluation-frequency 1m \
  --action <action-group-id>

Typical alerts: failed request spike, p95 latency above a threshold, exceptions rate, availability test failures, and dependency failures. Prefer log-based alerts for business conditions (e.g., no orders in 30 minutes).

9. Cost and data control

Application Insights is billed by data ingested (GB) to the workspace, so control volume deliberately:

Sampling: adaptive sampling is on by default in the classic SDK. With OpenTelemetry, set SamplingRatio (or use rate-limited sampling) in AzureMonitorOptions. Sampled telemetry keeps correlated items together.
Log levels: don't send Debug or Trace in production, and raise noisy framework categories to Warning.
Daily cap: set a cap on the workspace as a safety net, knowing that data stops being collected when it's hit.
Retention: default is 90 days for Application Insights tables (30 days for Log Analytics generally); extend or archive only what you need.
Filter noise: drop health-check requests and static files with a processor or filter.

10. Security and good practice

Never log secrets or PII (tokens, passwords, card numbers, full emails). Sanitize or omit them before logging.
Use Entra authentication for ingestion (DefaultAzureCredential with the Monitoring Metrics Publisher role) and disable local (key-based) auth, so a leaked connection string can't be used to inject fake telemetry.
Use RBAC to control who can read logs (Log Analytics Reader, Monitoring Reader).
Use private link (Azure Monitor Private Link Scope) if telemetry must stay off the public internet.
Use consistent correlation: include IDs such as OrderId in log templates so you can search by business key.
One resource per environment (or at least distinct cloud role names and an environment dimension) so dev noise doesn't pollute production.

Enabling Entra ingestion with the OpenTelemetry distro:

builder.Services.AddOpenTelemetry().UseAzureMonitor(o =>
{
    o.Credential = new DefaultAzureCredential();
});

Minimal setup checklist

Create a workspace-based Application Insights resource.
Add Azure.Monitor.OpenTelemetry.AspNetCore and call UseAzureMonitor(), with the connection string in app settings.
Log through ILogger with structured templates.
Set the service name so Application Map shows each service.
Add alerts for failures, latency, and availability.
Configure sampling, log levels, and a daily cap to control cost.

How do you implement authentication using Entra ID, OAuth, and JWT?

2026-11-01T19:30:00.000-08:00

Authentication with Entra ID, OAuth 2.0, and JWT

These three pieces fit together as follows:

Microsoft Entra ID is the identity provider (IdP). It authenticates users and apps and issues tokens.
OAuth 2.0 is the authorization framework: how a client gets permission to call an API on behalf of a user or as itself.
OpenID Connect (OIDC) is a layer on OAuth 2.0 for sign-in. It adds an ID token that tells your app who the user is.
JWT is the token format. Entra issues access tokens and ID tokens as signed JSON Web Tokens.

Access tokens are for APIs ("this caller may do X"). ID tokens are for the client app ("this user signed in"). Never send an ID token to an API.

1. Choose the flow

Scenario	Flow
SPA or mobile app, user signs in, calls your API	Authorization code + PKCE
Server-side web app with user sign-in	Authorization code (with a client secret or certificate)
Service-to-service, no user (daemon, background job)	Client credentials
API calls another API as the signed-in user	On-behalf-of (OBO)

Avoid the implicit flow and the password (ROPC) flow. Both are legacy and discouraged.

2. Register the apps in Entra ID

You typically register two apps: one for the API and one for the client.

API registration:

Go to Entra ID → App registrations → New registration.
Under Expose an API, set the Application ID URI (e.g. api://<api-client-id>) and add a scope such as access_as_user for delegated (user) permissions.
Under App roles, add roles such as Orders.Read for app-only callers (and for user roles).
In the manifest, make sure requestedAccessTokenVersion is 2 so you get v2.0 tokens.

Client registration:

Add a redirect URI. Use the SPA platform for SPAs, which enables PKCE, or Web for server apps.
Under API permissions, add your API's scope or role and grant admin consent if needed.
For confidential clients, add a certificate (preferred) or client secret under Certificates & secrets.

3. Protect an ASP.NET Core API (validate JWTs)

dotnet add package Microsoft.Identity.Web

appsettings.json:

{
  "AzureAd": {
    "Instance": "https://login.microsoftonline.com/",
    "TenantId": "<tenant-id>",
    "ClientId": "<api-client-id>",
    "Audience": "api://<api-client-id>"
  }
}

Program.cs:

using Microsoft.Identity.Web;

var builder = WebApplication.CreateBuilder(args);

builder.Services
    .AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
    .AddMicrosoftIdentityWebApi(builder.Configuration.GetSection("AzureAd"));

builder.Services.AddAuthorization(options =>
{
    options.AddPolicy("OrdersRead", p => p.RequireRole("Orders.Read"));
});

var app = builder.Build();

app.UseAuthentication();
app.UseAuthorization();

app.MapControllers();
app.Run();

Controller:

[Authorize]
[ApiController]
[Route("api/orders")]
public class OrdersController : ControllerBase
{
    // Delegated (user) token: check the scope claim
    [HttpGet]
    [RequiredScope("access_as_user")]
    public IActionResult GetMine() => Ok(new { user = User.Identity?.Name });

    // App-only token (client credentials): check the roles claim
    [HttpGet("all")]
    [Authorize(Policy = "OrdersRead")]
    public IActionResult GetAll() => Ok();
}

User tokens carry scopes in the scp claim, and app-only tokens carry app roles in the roles claim. Check the right one for the flow you expect.

4. Sign users in to a web app (OIDC)

builder.Services
    .AddAuthentication(OpenIdConnectDefaults.AuthenticationScheme)
    .AddMicrosoftIdentityWebApp(builder.Configuration.GetSection("AzureAd"))
    .EnableTokenAcquisitionToCallDownstreamApi(new[] { "api://<api-client-id>/access_as_user" })
    .AddInMemoryTokenCaches();

Inject ITokenAcquisition to get an access token for the signed-in user and call your API:

var token = await _tokenAcquisition.GetAccessTokenForUserAsync(
    new[] { "api://<api-client-id>/access_as_user" });

_http.DefaultRequestHeaders.Authorization =
    new AuthenticationHeaderValue("Bearer", token);

For a SPA, use MSAL.js (@azure/msal-browser, or @azure/msal-react / @azure/msal-angular). It handles the code + PKCE flow, token caching, and silent refresh:

const msal = new PublicClientApplication({
  auth: { clientId: "<spa-client-id>", authority: "https://login.microsoftonline.com/<tenant-id>" }
});

const result = await msal.acquireTokenPopup({
  scopes: ["api://<api-client-id>/access_as_user"]
});

fetch("https://my-api/api/orders", {
  headers: { Authorization: `Bearer ${result.accessToken}` }
});

5. Service-to-service (client credentials)

When there's no user, the caller authenticates as itself. Always request the .default scope, which returns the app roles granted to that client:

var credential = new DefaultAzureCredential(); // managed identity in Azure

var token = await credential.GetTokenAsync(
    new TokenRequestContext(new[] { "api://<api-client-id>/.default" }));

Prefer a managed identity over a client secret. Assign the API's app role to the managed identity's service principal (via Graph, PowerShell, or Azure CLI). Outside Azure, use ConfidentialClientApplication from Microsoft.Identity.Client with a certificate.

6. What your API must validate in a JWT

A JWT has three base64url parts, header.payload.signature. AddMicrosoftIdentityWebApi does the following for you, but you should know what's being checked:

Signature: verified using Entra's public signing keys, fetched from the OIDC metadata endpoint (.../.well-known/openid-configuration → jwks_uri) and cached and rotated automatically.
iss (issuer): must be your tenant, e.g. https://login.microsoftonline.com/<tenant-id>/v2.0.
aud (audience): must be your API (its client ID or Application ID URI). A token meant for another API must be rejected.
exp / nbf: token is within its validity window (access tokens last roughly 60 to 90 minutes).
scp or roles: the caller has the permission required for this operation.
tid: for multi-tenant APIs, check that the tenant is one you allow.

Key claims you'll use: oid (user or service principal object ID), sub, tid, scp, roles, preferred_username, appid/azp (the calling client).

If you ever need manual configuration without Microsoft.Identity.Web:

.AddJwtBearer(o =>
{
    o.Authority = "https://login.microsoftonline.com/<tenant-id>/v2.0";
    o.TokenValidationParameters = new()
    {
        ValidAudience = "api://<api-client-id>",
        ValidateIssuer = true,
        ValidateAudience = true,
        ValidateLifetime = true
    };
});

7. Common pitfalls

Mixing up token types: APIs accept access tokens only, never ID tokens.
Wrong audience or token version: a v1 token has a different iss and aud format than v2, so set the API's accepted token version to match your validation.
Checking only authentication: a valid token isn't enough. Always check scopes or roles, otherwise any app in your tenant that can get a token for your API is authorized.
Storing tokens in localStorage: this exposes them to XSS. Let MSAL manage caching, or use a backend-for-frontend (BFF) with HttpOnly cookies.
Client secrets in code or SPAs: public clients (SPAs, mobile) can't keep secrets, which is why they use PKCE.
Decoding without validating: reading claims from a JWT without verifying its signature proves nothing.
Forgetting admin consent for application permissions, which causes 403s.
Rolling your own JWT handling: use MSAL and Microsoft.Identity.Web rather than hand-parsing tokens and keys.

8. Alternative: let Azure handle it (Easy Auth)

On App Service or Azure Functions, built-in authentication validates the token before your code runs and passes claims to your app via headers (X-MS-CLIENT-PRINCIPAL). It's the least-code option, though you get less control over fine-grained authorization than with middleware in your own code.

Summary

Register an API (with scopes and app roles) and a client in Entra ID.
Use auth code + PKCE for users and client credentials (ideally with a managed identity) for services.
The client acquires an access token via MSAL and sends it as Authorization: Bearer <token>.
The API validates the JWT (signature, issuer, audience, expiry) and enforces scopes/roles.

Write a query to find employees with salary greater than average salary in the entire company, ordered by salary descending.

2026-11-01T19:30:00.000-08:00

To find employees whose salary is greater than the company-wide average, you cannot simply place WHERE salary > AVG(salary) in a standard query because aggregate functions are not allowed in the WHERE clause without grouping.

Instead, you must use a Subquery or a Window Function.

Here are the two best ways to write this query, followed by a complete playground script.

Method 1: Using a Subquery (Standard & Highly Compatible)

This method calculates the company-wide average first in an isolated subquery, then uses that single numeric value to filter the main table.

sql

SELECT 
    employee_id,
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Use code with caution.

Method 2: Using a Common Table Expression (CTE) & Window Function

This approach calculates the average alongside your other data columns using AVG() OVER(), allowing you to see exactly how much each employee exceeds the average.

sql

WITH SalaryAnalysis AS (
    SELECT 
        employee_id,
        employee_name,
        department,
        salary,
        AVG(salary) OVER() AS company_average
    FROM employees
)
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    ROUND(company_average, 2) AS company_average
FROM SalaryAnalysis
WHERE salary > company_average
ORDER BY salary DESC;

Use code with caution.

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the logic:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- 2. INSERT SAMPLE DATA
-- Total Salary = 430,000 | Average Salary = 86,000
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),  -- Above Average
(2, 'Bob Jones', 'Sales', 75000.00),            -- Below Average
(3, 'Diana Prince', 'Marketing', 82000.00),      -- Below Average
(4, 'Fiona Gallagher', 'Engineering', 110000.00),-- Above Average
(5, 'Hannah Abbott', 'Marketing', 68000.00);    -- Below Average

-- 3. EXECUTE FILTERED QUERY
SELECT 
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Use code with caution.

Expected Query Output

With a company average of 86,000.00, the query filters out the lower salaries and yields:

employee_name	department	salary
Fiona Gallagher	Engineering	110000.00
Alice Smith	Engineering	95000.00

Write a query to calculate the difference between current row and previous row's salary (lag function).

2026-10-31T20:30:00.000-07:00

To calculate the difference between the current row's salary and the previous row's salary, you use the LAG() window function. LAG() allows you to look backward at a previous row's data within your result set without executing a complex self-join.

The Query

Here is the standard SQL query. It orders the employees by salary (highest to lowest) and computes the exact structural difference between adjacent positions:

sql

SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    -- Step 1: Fetch the previous row's salary
    LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS previous_salary,
    
    -- Step 2: Subtract the previous row's salary from the current row's salary
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_difference
FROM employees;

Use code with caution.

Syntax Deep Dive: LAG(column, offset, default)

salary: The target column you want to retrieve a historical value from.
1 (Offset): How many rows to look backward. A value of 1 targets the immediate previous row.
0 or salary (Default Value): What to return if there is no previous row (e.g., the very first row in the dataset).
- Passing 0 or omitting it helps show a blank baseline.
- Passing the column itself (salary) ensures that subtracting it from the current row results in a clean 0 instead of a breaking NULL value on row one.

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the calculations live:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- 2. INSERT SAMPLE DATA
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),
(2, 'Bob Jones', 'Sales', 75000.00),
(3, 'Diana Prince', 'Marketing', 82000.00),
(4, 'Fiona Gallagher', 'Engineering', 110000.00),
(5, 'Hannah Abbott', 'Marketing', 68000.00);

-- 3. EXECUTE LAG QUERY
SELECT 
    employee_name,
    department,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS previous_employee_salary,
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_diff_from_previous
FROM employees;

Use code with caution.

Expected Query Output

employee_name	department	salary	previous_employee_salary	salary_diff_from_previous
Fiona Gallagher	Engineering	110000.00	NULL	0.00
Alice Smith	Engineering	95000.00	110000.00	-15000.00
Diana Prince	Marketing	82000.00	95000.00	-13000.00
Bob Jones	Sales	75000.00	82000.00	-7000.00
Hannah Abbott	Marketing	68000.00	75000.00	-7000.00

(Note: Fiona displays 0.00 difference because she is at the top of the chain; there is no previous record to subtract from her total.)

What is the difference between a Service Bus Queue and a Topic?

2026-10-31T20:30:00.000-07:00

Service Bus Queue vs. Topic

The core difference is the delivery model: a queue delivers each message to exactly one consumer, while a topic delivers a copy of each message to every interested subscriber.

Queue: point-to-point

Senders put messages on the queue, and receivers pull from it. If several receivers listen to the same queue, they are competing consumers: each message goes to only one of them, so adding receivers spreads the load.

Sender(s) ──► [ Queue ] ──► Receiver A  (or B, or C; one gets each message)

Topic: publish/subscribe

Senders publish to a topic, but receivers don't read from the topic itself. They read from subscriptions, each of which acts like its own virtual queue. Every subscription receives its own copy of each message that matches its filter.

                      ┌─► [ Subscription: billing ]   ──► Billing service
Sender ──► [ Topic ] ─┼─► [ Subscription: shipping ]  ──► Shipping service
                      └─► [ Subscription: analytics ] ──► Analytics service

Within a single subscription, multiple consumers still compete, so you can scale out each subscriber independently.

Side-by-side

	Queue	Topic + subscriptions
Pattern	Point-to-point	Publish/subscribe (one-to-many)
Copies per message	One, consumed once	One per matching subscription
Receivers read from	The queue	A subscription (not the topic)
Sender knows the receivers?	Effectively one logical consumer	No, the sender is unaware of subscribers
Filtering	None	Rules and filters per subscription (SQL or correlation filters, with optional actions)
Adding a new consumer	Competes with existing ones for messages	Create a new subscription; existing ones are unaffected
Dead-letter queue	One DLQ	One DLQ per subscription
Tiers	Standard and Premium	Standard and Premium

Filters on subscriptions

Topics get much of their power from subscription rules:

// Only receive orders from the EU
await adminClient.CreateSubscriptionAsync(
    new CreateSubscriptionOptions("orders", "eu-orders"),
    new CreateRuleOptions("EuOnly", new SqlRuleFilter("region = 'EU'")));

A subscription with no rule gets everything by default. Note that a subscription's default rule is "match all", so if you add your own filter, remove the default rule or it will still match every message.

Sending and receiving in code

The code is nearly identical. The sender doesn't care which it targets, and only the receiver changes:

// Send (queue name or topic name)
var sender = client.CreateSender("orders");
await sender.SendMessageAsync(new ServiceBusMessage("Order #1234 placed"));

// Receive from a queue
var queueReceiver = client.CreateReceiver("orders");

// Receive from a topic subscription
var subReceiver = client.CreateReceiver("orders", "billing");

Because sending code is the same, you can start with a queue and move to a topic later without changing producers (you'd recreate the entity, though, as a queue can't be converted into a topic).

When to use which

Use a queue when:

One service should process each message (work distribution, task processing).
You want load leveling or competing consumers for throughput.
The sender and receiver are tightly paired, e.g. "process this payment."

Use a topic when:

Multiple independent systems must react to the same event (an "order placed" event consumed by billing, shipping, and notifications).
Different consumers want different subsets of messages (via filters).
You expect to add new consumers over time without touching the publisher.

Shared behavior

Queues and subscriptions share the same features: peek-lock, dead-lettering, sessions, scheduled messages, duplicate detection (configured at the topic level for topics), and TTL. A subscription is, in effect, a queue fed by a topic.

Rule of thumb

Command ("do this work") → queue
Event ("this happened") → topic

If you have a single consumer today but may add more, a topic with one subscription is a cheap way to keep your options open.

Conventional vs. attribute routing?

2026-10-30T20:30:00.000-07:00

With conventional routing, you map out all your URL patterns in one central route table. Attribute routing, on the other hand, lets you drop those rules directly onto the controller and action itself. The examples here use older tech like ASP.NET MVC 5 and Web API 2, but don't worry—the exact same concept applies to ASP.NET Core.

Conventional routing:-

Patterns are registered once at startup file and apply to many controllers by naming convention.

public static void RegisterRoutes(RouteCollection routes)
{
    routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

    routes.MapRoute(
        name: "Default",
        url: "{controller}/{action}/{id}",
        defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional });
}

/Products/Details/5 goes to ProductsController.Details(5). Add a new controller and its actions follow the same pattern with no extra routing code.

Attribute routing:-

The URL is declared next to the code that handles it.

// RouteConfig: enable it BEFORE the conventional routes
routes.MapMvcAttributeRoutes();
// Web API 2: config.MapHttpAttributeRoutes();

[RoutePrefix("orders")]
public class OrdersController : Controller
{
    [Route("")]                              // /orders
    public ActionResult Index() { return View(); }

    [Route("{id:int}")]                      // /orders/42
    public ActionResult Details(int id) { return View(); }

    [Route("{id:int}/items/{itemId:int}")]   // /orders/42/items/7
    public ActionResult Item(int id, int itemId) { return View(); }
}

Differences:-

	Conventional	Attribute
Where defined	Central route table (`RouteConfig`)	On controllers and actions
Style	One pattern serves many controllers	One URL per action, explicit
URL flexibility	Best for uniform shapes like `{controller}/{action}/{id}`	Handles irregular, nested, or custom URLs easily
Readability	You must check the route table to know a URL	The URL is visible right on the action
Maintenance	Change one rule to affect many actions	Edit each action, but changes stay local
Refactoring risk	Renaming a controller or action changes its URL	URLs stay stable when you rename methods
Constraints	Defined in the route's `constraints` object	Inline, such as `{id:int}`, `{name:alpha}`
Order	First match in the table wins, so order matters	Rarely order-sensitive, but ambiguity still errors
Best for	Classic MVC sites with page-like URLs	REST APIs, nested resources, SEO-friendly URLs

Why attribute routing is often preferred for APIs

Nested resources such as /customers/{customerId}/orders/{orderId} are awkward to express with a global pattern.
Stable public URLs. Renaming GetOrder to FetchOrder does not change /orders/{id}.
Optional parameters and defaults can be written inline: [Route("archive/{year:int?}")].
Route names can be set for URL generation: [Route("{id}", Name = "OrderById")].
Multiple URLs for one action are possible by stacking attributes.

When conventional routing still fits

A traditional content site where nearly every page follows {controller}/{action}/{id}.
Many controllers that should behave uniformly, with no need to repeat a route on each one.
A default homepage and catch-all fallbacks in a single place.

You can mix both

A common setup uses attribute routes for specific or API-style URLs and a conventional default route for everything else. Register attribute routes first so they take priority:

routes.MapMvcAttributeRoutes();   // specific routes first

routes.MapRoute(                  // general fallback last
    name: "Default",
    url: "{controller}/{action}/{id}",
    defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional });

Common mistakes

Forgetting MapMvcAttributeRoutes(). The [Route] attributes are silently ignored.
Calling it after the conventional route. The general route can catch requests first.
Two actions matching the same URL and verb. This throws an "ambiguous match" error. Use constraints or different templates.
Missing [RoutePrefix] or typos in prefixes. URLs end up different from what you intended.
Mixing with Web API. Web API has its own route table, so MapHttpAttributeRoutes() is separate from MapMvcAttributeRoutes().

In ASP.NET Core

The same two ideas exist. Conventional routing uses MapControllerRoute, and attribute routing uses [Route], [HttpGet("...")], and similar attributes. One key difference is that in Core, an action that has route attributes is not reachable through conventional routes. Each action uses one style or the other.

Which one to choose?

Building an API? Use attribute routing.
Classic MVC site with uniform URLs? Conventional routing is fine and less repetitive.
Need both? Use attribute routes for the special cases and a conventional default as the fallback.

Write a query to perform a conditional aggregation (count males and females in each department).

2026-10-30T20:30:00.000-07:00

To perform conditional aggregation in SQL, you combine the SUM() or COUNT() aggregate functions with a CASE WHEN statement. This allows you to inspect row attributes individually and increment separate totals within the same GROUP BY execution.

The Query

Assuming your table is named employees and contains columns for department and gender, here is the standard, optimized SQL query:

sql

SELECT 
    department,
    -- Method 1: SUM with CASE WHEN (Standard across all SQL flavors)
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count,
    
    -- Method 2: COUNT with CASE WHEN (Alternative syntax)
    COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS alt_male_count,
    COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS alt_female_count
FROM employees
GROUP BY department
ORDER BY department;

Use code with caution.

How It Works

The CASE Evaluator: For every row processed within a department, the CASE statement inspects the value of the gender column.
SUM(CASE ... THEN 1 ELSE 0): If the condition matches, it outputs a 1. If it doesn't match, it outputs a 0. The SUM() function then tallies up these numbers, effectively calculation a count.
COUNT(CASE ... THEN 1 END): The COUNT() function ignores NULL values. If the condition matches, it returns a 1. If it doesn't match, it implicitly returns NULL (since there is no ELSE clause), so it isn't counted.

Complete Playground Script (Schema + Test Data)

You can use this complete script to build the schema, load data, and instantly verify the execution logic:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    gender VARCHAR(10)
);

-- 2. INSERT SAMPLE DATA
INSERT INTO employees (employee_id, employee_name, department, gender) VALUES
(1, 'Alice', 'Engineering', 'Female'),
(2, 'Bob', 'Sales', 'Male'),
(3, 'Charlie', 'Engineering', 'Male'),
(4, 'Diana', 'Engineering', 'Female'),
(5, 'Evan', 'Sales', 'Male'),
(6, 'Fiona', 'Marketing', 'Female'),
(7, 'George', 'Sales', 'Male'),
(8, 'Hannah', 'Marketing', 'Female');

-- 3. EXECUTE CONDITIONAL AGGREGATION
SELECT 
    department,
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department
ORDER BY department;

Use code with caution.

Expected Query Output

department	male_count	female_count
Engineering	1	2
Marketing	0	2
Sales	3	0

What is Managed Identity, and why do we use it?

2026-10-30T20:30:00.000-07:00

Managed Identity

A managed identity is an identity in Microsoft Entra ID that Azure creates and manages for an Azure resource (an App Service, Function App, VM, Container App, AKS workload, and so on). The resource uses it to authenticate to other Azure services without any credentials in your code or config. Azure handles the credential behind the scenes: it creates it, stores it, and rotates it automatically, and you never see or handle the secret.

The problem it solves

Normally, to call something like Key Vault, SQL, or Storage, your app needs a credential: a connection string, API key, or client secret. That creates a chain of problems:

The credential has to be stored somewhere (config, CI variables, a vault).
It can leak through source control, logs, or screenshots.
It expires and needs rotation, which often causes outages when someone forgets.
To read the secret from Key Vault, you need another credential (the "secret zero" problem).

Managed identity removes the credential from the picture entirely. The identity is the resource, and access is granted through RBAC.

How it works

You enable a managed identity on the resource.
Azure registers a service principal for it in Entra ID.
You grant that identity a role on the target resource (e.g., Key Vault Secrets User, Storage Blob Data Reader).
At runtime, your code asks a local token endpoint on the host (the Instance Metadata Service, or the App Service identity endpoint) for an access token for the target resource.
Azure issues a short-lived token, and your code presents it to the target service, which checks the RBAC assignment.

The token request is only possible from code running on that resource, which is why no secret is needed. The Azure SDKs do all of this for you:

var credential = new DefaultAzureCredential();

var blobs = new BlobServiceClient(
    new Uri("https://mystorage.blob.core.windows.net"), credential);

DefaultAzureCredential uses the managed identity when running in Azure, and falls back to your az login or Visual Studio sign-in locally, so the same code works in both places.

Two types

	System-assigned	User-assigned
Lifecycle	Tied to the resource; deleted with it	Standalone resource with its own lifecycle
Sharing	One resource only	Can be attached to many resources
Best for	Simple, single-resource scenarios	Sharing one identity across apps, pre-provisioning access before deployment, or keeping access stable when you recreate resources

When using a user-assigned identity, tell the SDK which one to use, since a resource can have several:

var credential = new DefaultAzureCredential(
    new DefaultAzureCredentialOptions { ManagedIdentityClientId = "<client-id>" });

Setting it up

# System-assigned
az webapp identity assign --name my-app --resource-group my-rg

# User-assigned
az identity create --name my-identity --resource-group my-rg
az webapp identity assign --name my-app --resource-group my-rg \
  --identities <identity-resource-id>

# Grant access
az role assignment create \
  --assignee <principalId> \
  --role "Storage Blob Data Reader" \
  --scope <storage-account-resource-id>

Why use it

No secrets to manage: nothing to store, leak, or rotate by hand.
Better security posture: short-lived tokens, and access governed by RBAC with least privilege and a clear audit trail in Entra ID and Azure Monitor.
Less operational risk: no expired-credential outages.
Simpler code and pipelines: no secret plumbing between environments.
Free: there's no extra charge for managed identities.

Limitations to know about

It only works for Azure resources (and Azure Arc-enabled servers). Code running on-premises or on a laptop can't use one directly; use DefaultAzureCredential fallbacks, workload identity federation, or service principals there.
The target service must support Entra authentication. Most Azure services do (Key Vault, Storage, Service Bus, SQL, Cosmos DB, Event Hubs), but some third-party services or older features still need keys.
Role assignments take a few minutes to propagate, and tokens are cached, so changes aren't always instant.
It authenticates the resource, not an end user. If you need per-user access, you need user sign-in (OAuth/OIDC) instead.
For Azure SQL, you also have to create a database user for the identity (CREATE USER [my-app] FROM EXTERNAL PROVIDER) and grant it database roles.

Related: workload identity

For apps outside Azure (GitHub Actions, Kubernetes on other clouds), workload identity federation gives a similar credential-free experience by trusting tokens from an external identity provider. It's what the azure/login@v2 OIDC setup in a GitHub Actions workflow uses.

What is Routing?

2026-10-30T08:35:00.000-07:00

Routing is the process of deciding where to send something should go to next, based on where it is trying to reach. A router (or routing system) looks at the destination, checks a set of rules, and forwards the item along the best matching path.

A simple analogy

Think of a postal sorting office. A letter arrives with an address, and the clerk checks the rules ("anything for this postcode goes to that truck") and sends it on. No single clerk needs to know about the whole journey, only the next step. Routing in computing works the same way.

The two main kinds

1. Network routing: moving data packets between the networks.

Each packet carries a destination IP address.
A router checks its routing table and forwards these packet to the next hop, such as another router or the final machine.
This is how the data crosses your home network, your ISP, and the internet to reach a server.
If no specific rule matches, a default route sends the packet to a general gateway.

2. Application (URL) routing: moving web requests to the right code.

The destination is a URL such as '/orders/42'.
A routing system matches that URL pattern to a specific handler, such as a function, controller, or page.
This is how the one web application serves many different pages and API endpoints.

Request: GET /orders/42
Rule:    /orders/{id}  ->  OrdersController.Details(id)
Result:  Details(42) runs

The core parts

Part	What it is
Destination	The address, IP or URL, the item is heading to
Rules / table	The list of patterns and where each one leads
Matching	Picking the best rule, usually the most specific one
Next hop / handler	Where the item is sent, a router or a piece of code
Default	A fallback for anything with no matching rule

Why we need it

Scale: The senders don't need to know every path to every destination.
Flexibility: you can change where the traffic goes by editing a rule, not every sender.
Control and security: you can force traffic through a firewall or block certain paths.
Clean design: web URLs stay readable and independent of how the code is organized.
Efficiency: traffic takes the best available path.

How do you secure an Azure Function?

2026-10-30T07:43:00.000-07:00

Securing an Azure Function

Securing a function is layered: control who can call it, what it can reach, and where its secrets live. Start with the first three layers, then add the rest as your requirements grow.

1. Authenticate and authorize callers (inbound)

Function keys (the default for HTTP triggers). AuthorizationLevel.Function requires a key in the x-functions-key header (or ?code=). Keys are simple but they're shared secrets with no identity attached, so treat them as a minimum, not a full solution. Avoid AuthorizationLevel.Anonymous unless something else (such as API Management or Easy Auth) is enforcing access in front of it. Note that Admin level uses the master key, which grants access to everything, so never share it.

App Service Authentication ("Easy Auth") with Microsoft Entra ID. This is the recommended approach for user- or service-facing APIs. The platform validates tokens before your code runs. Enable it under Settings → Authentication and set unauthenticated requests to return 401:

az webapp auth update --name my-func-app --resource-group my-rg \
  --enabled true --action Return401

You then add an Entra ID identity provider (via the portal or az webapp auth microsoft update) with your app registration. For finer control, check roles or scopes in code from the validated claims.

API Management in front. Put APIM in front of the function to get JWT validation, rate limiting, IP filtering, and subscription keys, and then restrict the function so it only accepts traffic from APIM.

Webhooks from third parties (Stripe, GitHub): verify the provider's signature header inside your function rather than relying only on a key.

2. Restrict the network

Access restrictions: allow only specific IP ranges, service tags, or VNets (e.g., only your APIM or Front Door).
Private endpoints: make the function reachable only from inside your VNet (Premium, Flex Consumption, or Dedicated plans).
Front Door or Application Gateway with WAF: adds DDoS and OWASP protection, and you can lock the function to accept traffic only from them.
VNet integration (outbound): lets the function reach private resources like databases and Key Vault over the VNet, so those can have their public access turned off.

3. Use managed identity instead of connection strings (outbound)

Give the function a managed identity and grant it RBAC roles on the resources it needs. This removes stored credentials entirely.

az functionapp identity assign --name my-func-app --resource-group my-rg

// In the isolated worker, no keys required
var client = new BlobServiceClient(
    new Uri("https://mystorage.blob.core.windows.net"),
    new DefaultAzureCredential());

Triggers and bindings can use identity-based connections too. For example, with Service Bus you set ServiceBusConnection__fullyQualifiedNamespace instead of a connection string, and grant the identity the Azure Service Bus Data Receiver role. The same pattern works for the function's own host storage account (AzureWebJobsStorage__accountName), which is worth doing so you can disable shared key access on that account.

4. Protect secrets

Store remaining secrets in Key Vault and use Key Vault references in app settings: @Microsoft.KeyVault(VaultName=my-vault;SecretName=MySecret).
Never commit secrets to source control or put them in local.settings.json files that get checked in (that file is for local development only).
Rotate function keys periodically, and use per-client keys or host keys rather than distributing the default key broadly.

5. Harden the transport and the platform

HTTPS only: az functionapp update --set httpsOnly=true
Minimum TLS version 1.2, and disable FTP/FTPS deployment where you can (--ftps-state Disabled).
Disable SCM basic auth and deploy using Entra identities or federated credentials from CI/CD.
CORS: list only the exact origins that need browser access, and never use * for authenticated APIs.
Keep the runtime current: use a supported .NET and Functions runtime version, and update dependencies.

6. Code-level hygiene

Validate and sanitize all inputs, including queue and blob payloads, not just HTTP.
Don't return stack traces or internal details in error responses.
Apply least privilege. A function that only reads a queue shouldn't have write access to the storage account.
Avoid logging sensitive data (tokens, PII, secrets).

7. Monitor and detect

Enable Application Insights and diagnostic logs to Log Analytics.
Turn on Microsoft Defender for Cloud (Defender for App Service covers Function Apps) for threat detection and recommendations.
Alert on spikes in 401/403 responses, unusual invocation counts, and failed Key Vault access.

A sensible baseline

For a typical HTTP-triggered API:

Enable Easy Auth with Entra ID (return 401 for unauthenticated calls).
Use a managed identity with least-privilege RBAC for everything the function touches.
Put APIM or Front Door in front and restrict inbound access to it.
Store leftover secrets in Key Vault, enforce HTTPS and TLS 1.2+, and disable FTP and basic auth.
Send logs to Application Insights and enable Defender for Cloud.

Find customers who have not made any purchase.

2026-10-29T20:30:00.000-07:00

To find customers who have never made a purchase, you need to identify rows in your customers table that have no corresponding matching records in your orders (or purchases) table.

Here are the three most efficient SQL methods to achieve this, along with a complete schema script to test it yourself.

Method 1: Using LEFT JOIN (Recommended & Most Common)

This method joins both tables and filters out any rows where the order details come back empty (NULL).

sql

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Use code with caution.

Method 2: Using NOT EXISTS (Highly Efficient for Large Datasets)

This method checks every customer record and returns it only if a subquery searching the orders table yields zero results.

sql

SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

Use code with caution.

Method 3: Using NOT IN

A simple approach that reads like plain English. (Warning: This method will fail to return results if the orders.customer_id column contains any NULL values).

sql

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id 
    FROM orders 
    WHERE customer_id IS NOT NULL
);

Use code with caution.

Complete Playground Script (Schema + Test Data)

Copy and execute this script to see the queries in action:

sql

-- 1. Create Tables
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 2. Insert Sample Data
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'John Doe'),      -- Has placed an order
(2, 'Jane Smith'),    -- Has NOT placed an order
(3, 'Bob Johnson'),   -- Has placed an order
(4, 'Alice Williams');-- Has NOT placed an order

INSERT INTO orders (order_id, customer_id, order_date) VALUES
(101, 1, '2026-10-25'),
(102, 3, '2026-10-26'),
(103, 1, '2026-10-27');

-- 3. Run the selection (Will return Jane Smith and Alice Williams)
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Use code with caution.

Expected Query Output

customer_id	customer_name
2	Jane Smith
4	Alice Williams

What is Azure Key Vault, and how do you use it from a .NET application?

2026-10-28T20:30:00.000-07:00

Azure Key Vault

Azure Key Vault is a managed service for storing and controlling access to sensitive material, so secrets don't end up in source code, config files, or CI variables. It handles three kinds of objects:

Secrets: arbitrary small values such as connection strings, API keys, and passwords.
Keys: cryptographic keys (RSA, EC) used for signing, encryption, and wrapping. Operations run inside the vault, so private key material never leaves it (with HSM-backed keys, never leaves the HSM).
Certificates: TLS/SSL certificates with lifecycle management, including auto-renewal with supported CAs.

Tiers: Standard (software-protected keys), Premium (HSM-backed keys), and Managed HSM (a dedicated, single-tenant HSM pool for the strictest compliance needs).

Key features

Access control: Azure RBAC is the recommended model (access policies are the legacy model). Common roles are Key Vault Secrets User (read secrets), Key Vault Secrets Officer (manage them), Key Vault Crypto User, and Key Vault Administrator.
Soft delete and purge protection: deleted items can be recovered during a retention period, and purge protection prevents permanent deletion until it ends.
Versioning: every secret update creates a new version, so you can roll back or rotate gracefully.
Auditing: logs go to Azure Monitor / Log Analytics.
Network security: firewall rules and private endpoints.
Events: Event Grid notifications for things like a secret nearing expiry.

The best practice: no credentials to reach the vault

Your app authenticates to Key Vault with a managed identity, so you don't need a secret to get your secrets. DefaultAzureCredential uses the managed identity when running in Azure, and falls back to your az login or Visual Studio sign-in locally.

1. Create the vault and grant access

az keyvault create --name my-unique-vault --resource-group my-rg \
  --location centralindia --enable-rbac-authorization true

az keyvault secret set --vault-name my-unique-vault \
  --name "ConnectionStrings--Default" --value "Server=...;Password=..."

# Enable a system-assigned identity on your App Service
az webapp identity assign --name my-unique-app-name --resource-group my-rg

# Let that identity read secrets
az role assignment create \
  --role "Key Vault Secrets User" \
  --assignee <principalId-from-previous-command> \
  --scope $(az keyvault show --name my-unique-vault --query id -o tsv)

For local development, give your own user the same role on the vault, and make sure you're signed in with az login or Visual Studio.

2. Option A: load secrets into `IConfiguration` (most common)

Install the packages:

dotnet add package Azure.Identity
dotnet add package Azure.Extensions.AspNetCore.Configuration.Secrets

using Azure.Identity;

var builder = WebApplication.CreateBuilder(args);

builder.Configuration.AddAzureKeyVault(
    new Uri("https://my-unique-vault.vault.azure.net/"),
    new DefaultAzureCredential());

var app = builder.Build();

// Reads the Key Vault secret "ConnectionStrings--Default"
var cs = builder.Configuration.GetConnectionString("Default");

Key Vault secret names can't contain :, so use -- instead. ConnectionStrings--Default maps to ConnectionStrings:Default. Secrets are loaded at startup, and the provider can refresh periodically if you set a ReloadInterval in AzureKeyVaultConfigurationOptions.

3. Option B: call Key Vault directly with the SDK

dotnet add package Azure.Security.KeyVault.Secrets

using Azure.Identity;
using Azure.Security.KeyVault.Secrets;

var client = new SecretClient(
    new Uri("https://my-unique-vault.vault.azure.net/"),
    new DefaultAzureCredential());

KeyVaultSecret secret = await client.GetSecretAsync("StripeApiKey");
string apiKey = secret.Value;

For keys and certificates, use Azure.Security.KeyVault.Keys (KeyClient and CryptographyClient for encrypt/sign operations) and Azure.Security.KeyVault.Certificates (CertificateClient).

builder.Services.AddSingleton(_ => new SecretClient(
    new Uri("https://my-unique-vault.vault.azure.net/"),
    new DefaultAzureCredential()));

4. Option C: Key Vault references (no code changes)

In App Service or Azure Functions, set an app setting whose value is a reference:

@Microsoft.KeyVault(SecretUri=https://my-unique-vault.vault.azure.net/secrets/StripeApiKey/)

@Microsoft.KeyVault(VaultName=my-unique-vault;SecretName=StripeApiKey)

The platform resolves it using the app's managed identity and injects the value as an ordinary environment variable. Your .NET code just reads IConfiguration as usual. It's the simplest approach when you're already on App Service or Functions, and leaving the version off means the app picks up newer secret versions automatically.

Practical tips

Cache secrets instead of fetching them on every request. Key Vault has per-vault throttling limits, and each call adds latency.
Prefer RBAC and least privilege: Secrets User for apps, with write roles reserved for pipelines or admins.
Use one vault per app and environment (dev/test/prod) so access can be scoped tightly.
Rotate secrets: set expiry dates, subscribe to the SecretNearExpiry event, and have your app tolerate version changes.
Don't log secret values, and avoid putting the vault URI in places where it matters if exposed (the URI isn't secret, but access control should be what protects it).
Related uses: ASP.NET Core Data Protection keys can be protected with a Key Vault key, and Entra ID app credentials can be certificate-based using Key Vault certificates.

Write a query to find gaps in a sequence of numbers (missing IDs).

2026-10-28T20:30:00.000-07:00

Finding Gaps in a Sequence (Missing IDs) in SQL

Table

CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    Customer VARCHAR(100)
);

INSERT INTO Orders (OrderId, Customer) VALUES
(1, 'A'), (2, 'B'), (3, 'C'),
(6, 'D'), (7, 'E'),
(10, 'F'),
(14, 'G'), (15, 'H');

Missing IDs: 4, 5, 8, 9, 11, 12, 13.

Query 1: gap ranges (start and end of each gap)

Uses LEAD to compare each ID with the next one.

WITH NextId AS (
    SELECT
        OrderId,
        LEAD(OrderId) OVER (ORDER BY OrderId) AS NextOrderId
    FROM Orders
)
SELECT
    OrderId + 1         AS GapStart,
    NextOrderId - 1     AS GapEnd,
    NextOrderId - OrderId - 1 AS MissingCount
FROM NextId
WHERE NextOrderId - OrderId > 1
ORDER BY GapStart;

Result

GapStart	GapEnd	MissingCount
4	5	2
8	9	2
11	13	3

This is the fastest and most compact approach. It reads the table once and returns one row per gap.

Query 2: every missing ID listed individually

SQL Server / Azure SQL (recursive CTE, no numbers table needed)

WITH Bounds AS (
    SELECT MIN(OrderId) AS MinId, MAX(OrderId) AS MaxId FROM Orders
),
AllIds AS (
    SELECT MinId AS Id, MaxId FROM Bounds
    UNION ALL
    SELECT Id + 1, MaxId FROM AllIds WHERE Id < MaxId
)
SELECT a.Id AS MissingId
FROM AllIds a
LEFT JOIN Orders o ON o.OrderId = a.Id
WHERE o.OrderId IS NULL
ORDER BY a.Id
OPTION (MAXRECURSION 0);

SQL Server 2022 / Azure SQL (`GENERATE_SERIES`)

SELECT s.value AS MissingId
FROM GENERATE_SERIES(
        (SELECT MIN(OrderId) FROM Orders),
        (SELECT MAX(OrderId) FROM Orders)) s
LEFT JOIN Orders o ON o.OrderId = s.value
WHERE o.OrderId IS NULL
ORDER BY s.value;

PostgreSQL

SELECT g AS missing_id
FROM generate_series(
        (SELECT MIN(order_id) FROM orders),
        (SELECT MAX(order_id) FROM orders)) AS g
LEFT JOIN orders o ON o.order_id = g
WHERE o.order_id IS NULL
ORDER BY g;

Result

MissingId
4
5
8
9
11
12
13

Query 3: gaps and islands (alternative using ROW_NUMBER)

Shows the present ranges; the gaps are the spaces between them.

WITH Islands AS (
    SELECT
        MIN(OrderId) AS IslandStart,
        MAX(OrderId) AS IslandEnd
    FROM (
        SELECT OrderId,
               OrderId - ROW_NUMBER() OVER (ORDER BY OrderId) AS grp
        FROM Orders
    ) t
    GROUP BY grp
)
SELECT IslandStart, IslandEnd
FROM Islands
ORDER BY IslandStart;

Result

IslandStart	IslandEnd
1	3
6	7
10	10
14	15

Query 4: gaps without window functions (older databases)

SELECT
    o.OrderId + 1 AS GapStart,
    (SELECT MIN(o2.OrderId) FROM Orders o2 WHERE o2.OrderId > o.OrderId) - 1 AS GapEnd
FROM Orders o
WHERE NOT EXISTS (SELECT 1 FROM Orders o2 WHERE o2.OrderId = o.OrderId + 1)
  AND o.OrderId < (SELECT MAX(OrderId) FROM Orders)
ORDER BY GapStart;

Slower, since it runs subqueries per row, but it works on any database.

Query 5: gaps per group (for example, per customer or per year)

WITH NextId AS (
    SELECT
        Region,
        InvoiceNo,
        LEAD(InvoiceNo) OVER (PARTITION BY Region ORDER BY InvoiceNo) AS NextInvoiceNo
    FROM Invoices
)
SELECT Region,
       InvoiceNo + 1     AS GapStart,
       NextInvoiceNo - 1 AS GapEnd
FROM NextId
WHERE NextInvoiceNo - InvoiceNo > 1
ORDER BY Region, GapStart;

PARTITION BY restarts the check for each group, which is what you want when every group has its own numbering.

Query 6: check whether the sequence starts at 1

The queries above only find gaps between existing IDs. To catch missing IDs at the beginning:

SELECT 1 AS GapStart, MIN(OrderId) - 1 AS GapEnd
FROM Orders
HAVING MIN(OrderId) > 1;

For missing IDs after the last one, you need an expected maximum from somewhere else (a counter, a config value, or a parent table), since the table itself can't know.

Details

LEAD approach (Query 1) is the best default: one pass, one row per gap, easy to read.
Listing each missing ID (Query 2) needs a generated range of numbers. The recursive CTE works anywhere; GENERATE_SERIES is simpler where available (SQL Server 2022+, Azure SQL, PostgreSQL).
Recursion limit: SQL Server stops at 100 levels by default, so use OPTION (MAXRECURSION 0) for large ranges. For millions of IDs, use a numbers (tally) table instead.
Duplicates: if the column isn't unique, add SELECT DISTINCT in the first CTE, otherwise LEAD returns the same value and hides gaps.
Identity columns create gaps naturally (rolled-back inserts, deletes, failed transactions), so gaps there are usually normal. Gaps matter for things like invoice numbers, where auditors expect a continuous sequence.
Sequence start and end: decide whether leading and trailing gaps count (Query 6).

Supporting index

-- Usually the primary key already covers this. For a non-key column:
CREATE INDEX IX_Orders_OrderId ON Orders (OrderId);

An ordered index lets LEAD and ROW_NUMBER read the rows without a sort.

Compatibility

LEAD and ROW_NUMBER work in Azure SQL, SQL Server 2012+, PostgreSQL, MySQL 8.0+, Oracle, and SQLite 3.25+. GENERATE_SERIES requires SQL Server 2022+ or PostgreSQL. In MySQL, use a recursive CTE with WITH RECURSIVE for the full list of missing IDs.

Recursive query to find the full reporting chain for each employee.

2026-10-27T20:30:00.000-07:00

Full Reporting Chain per Employee (Recursive CTE)

Table

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    Name       VARCHAR(100),
    ManagerId  INT NULL REFERENCES Employees(EmployeeId)
);

INSERT INTO Employees (EmployeeId, Name, ManagerId) VALUES
(1, 'Anita',  NULL),   -- CEO
(2, 'Rahul',  1),
(3, 'Priya',  1),
(4, 'Vikram', 2),
(5, 'Sneha',  2),
(6, 'Arjun',  4);

Approach

A recursive CTE has two parts:

Anchor member: the starting rows. Each employee paired with their direct manager.
Recursive member: joins the previous result to Employees to climb one level higher, repeating until it reaches a row with no manager.

Query 1: every employee with all their managers (one row per level)

WITH ReportingChain AS (
    -- Anchor: each employee and their direct manager
    SELECT
        e.EmployeeId,
        e.Name          AS EmployeeName,
        e.ManagerId     AS CurrentManagerId,
        1               AS Level
    FROM Employees e
    WHERE e.ManagerId IS NOT NULL

    UNION ALL

    -- Recursive: move up to the manager's manager
    SELECT
        rc.EmployeeId,
        rc.EmployeeName,
        m.ManagerId,
        rc.Level + 1
    FROM ReportingChain rc
    JOIN Employees m ON m.EmployeeId = rc.CurrentManagerId
    WHERE m.ManagerId IS NOT NULL
)
SELECT
    rc.EmployeeId,
    rc.EmployeeName,
    rc.Level,
    mgr.EmployeeId AS ManagerId,
    mgr.Name       AS ManagerName
FROM ReportingChain rc
JOIN Employees mgr ON mgr.EmployeeId = rc.CurrentManagerId
ORDER BY rc.EmployeeId, rc.Level;

Result (excerpt)

EmployeeName	Level	ManagerName
Arjun	1	Vikram
Arjun	2	Rahul
Arjun	3	Anita
Vikram	1	Rahul
Vikram	2	Anita
Sneha	1	Rahul
Sneha	2	Anita

Query 2: the chain as a single string per employee

WITH Chain AS (
    -- Anchor: start at every employee
    SELECT
        EmployeeId,
        Name,
        ManagerId,
        CAST(Name AS VARCHAR(1000)) AS ChainPath,
        0 AS Depth
    FROM Employees

    UNION ALL

    -- Recursive: append the next manager up
    SELECT
        c.EmployeeId,
        c.Name,
        m.ManagerId,
        CAST(c.ChainPath + ' -> ' + m.Name AS VARCHAR(1000)),
        c.Depth + 1
    FROM Chain c
    JOIN Employees m ON m.EmployeeId = c.ManagerId
)
SELECT EmployeeId, Name, ChainPath, Depth AS LevelsAboveEmployee
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY Depth DESC) AS rn
    FROM Chain
) x
WHERE rn = 1
ORDER BY EmployeeId;

Result

Name	ChainPath	LevelsAboveEmployee
Anita	Anita	0
Rahul	Rahul -> Anita	1
Priya	Priya -> Anita	1
Vikram	Vikram -> Rahul -> Anita	2
Sneha	Sneha -> Rahul -> Anita	2
Arjun	Arjun -> Vikram -> Rahul -> Anita	3

The final ROW_NUMBER() ... ORDER BY Depth DESC keeps only the longest (complete) path per employee.

Query 3: top-down hierarchy (the org tree from the CEO)

WITH OrgTree AS (
    SELECT EmployeeId, Name, ManagerId,
           0 AS Level,
           CAST(Name AS VARCHAR(1000)) AS Path
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT e.EmployeeId, e.Name, e.ManagerId,
           t.Level + 1,
           CAST(t.Path + ' > ' + e.Name AS VARCHAR(1000))
    FROM Employees e
    JOIN OrgTree t ON e.ManagerId = t.EmployeeId
)
SELECT REPLICATE('    ', Level) + Name AS OrgChart, Level, Path
FROM OrgTree
ORDER BY Path;

Result:

Anita
    Priya
    Rahul
        Sneha
        Vikram
            Arjun

Query 4: all direct and indirect reports of one manager

DECLARE @ManagerId INT = 2;

WITH Reports AS (
    SELECT EmployeeId, Name, ManagerId, 1 AS Level
    FROM Employees
    WHERE ManagerId = @ManagerId

    UNION ALL

    SELECT e.EmployeeId, e.Name, e.ManagerId, r.Level + 1
    FROM Employees e
    JOIN Reports r ON e.ManagerId = r.EmployeeId
)
SELECT * FROM Reports ORDER BY Level, Name;

Details

UNION ALL is required between the anchor and recursive members. Use UNION only in dialects that need it for cycle handling, and expect it to be slower.
Recursion limit: SQL Server stops at 100 levels by default. Raise or remove it with OPTION (MAXRECURSION 0) (0 = unlimited), but only after guarding against cycles.
Cycles: if bad data makes someone their own ancestor (A manages B, B manages A), the recursion never ends. Guard with a depth cap or a path check:

-- In the recursive member:
WHERE c.ChainPath NOT LIKE '%' + m.Name + '%'   -- simple guard, or
  AND c.Depth < 20                               -- depth cap

For a precise check, track visited IDs in a delimited string ('/1/2/4/') and test CHARINDEX('/' + CAST(m.EmployeeId AS VARCHAR) + '/', c.IdPath) = 0.

Multiple roots: if several rows have ManagerId IS NULL, Query 3 returns each as a separate tree.
Employees with no manager (the CEO) have no rows in Query 1, but appear with depth 0 in Query 2.
Data type: make the path column wide enough (VARCHAR(1000)). The anchor's type fixes the column type, and a too-short anchor causes truncation errors in the recursive member.

PostgreSQL / MySQL version

WITH RECURSIVE chain AS (
    SELECT employee_id, name, manager_id,
           name::text AS chain_path,
           0 AS depth
    FROM employees

    UNION ALL

    SELECT c.employee_id, c.name, m.manager_id,
           c.chain_path || ' -> ' || m.name,
           c.depth + 1
    FROM chain c
    JOIN employees m ON m.employee_id = c.manager_id
)
SELECT DISTINCT ON (employee_id) employee_id, name, chain_path, depth
FROM chain
ORDER BY employee_id, depth DESC;

Notes: the keyword is WITH RECURSIVE. Use CONCAT() instead of || in MySQL, and replace DISTINCT ON with the ROW_NUMBER() pattern from Query 2. Oracle supports both recursive CTEs and CONNECT BY PRIOR.

Supporting index

CREATE INDEX IX_Employees_ManagerId ON Employees (ManagerId) INCLUDE (Name);

Each recursive step joins on ManagerId or EmployeeId. The primary key covers the second, and this index covers the first.

Alternative: `hierarchyid` (SQL Server)

For large, read-heavy hierarchies, the hierarchyid type stores the path in the column and avoids recursion at query time:

ALTER TABLE Employees ADD OrgNode hierarchyid;
-- Ancestors of an employee:
SELECT * FROM Employees e
WHERE @targetNode.IsDescendantOf(e.OrgNode) = 1;

It requires maintaining the node values on every insert or reparent, so use it when hierarchy reads far outnumber writes.

What is Azure Service Bus, and how does it work?

2026-10-27T20:30:00.000-07:00

Azure Service Bus

Azure Service Bus is a fully managed enterprise message broker. It sits between applications and lets them communicate asynchronously: a sender puts a message on the broker and moves on, and a receiver picks it up whenever it's ready. This decouples services in time (they don't need to be online together), in load (a burst of messages is buffered), and in technology (any client that speaks AMQP, or uses the SDKs, can participate).

Core concepts

Namespace: the container for your messaging entities, with its own endpoint, pricing tier, and network and security settings.
Queue: point-to-point messaging. Many senders can send, but each message is delivered to exactly one receiver (competing consumers share the load).
Topic and subscriptions: publish/subscribe. A sender publishes to a topic, and each subscription gets its own copy of every matching message. Each subscription behaves like a virtual queue.
Rules and filters: on a subscription, you can filter messages by properties (e.g., region = 'EU') so each subscriber only receives what it cares about.
Message: a body plus system properties (MessageId, SessionId, TimeToLive, etc.) and custom application properties.

How it works

Send: the producer sends a message to a queue or topic. The broker stores it durably (replicated storage) before acknowledging.
Store: the message waits until a consumer retrieves it or its time-to-live expires.
Receive: the consumer receives the message using one of two modes:
- Peek-lock (default): the message is locked, not deleted. The consumer processes it, then completes it (removed). If processing fails, it can abandon it (becomes available again) or dead-letter it. If the lock expires without a response, the message returns to the queue.
- Receive-and-delete: the message is removed immediately on receipt. Faster, but it's lost if the consumer crashes mid-processing.
Retry and dead-letter: each message has a delivery count. Once it exceeds MaxDeliveryCount (default 10), it moves to the dead-letter queue (DLQ), a sub-queue where you can inspect and reprocess poison messages.

Key features

Delivery guarantees: at-least-once by default, so consumers should be idempotent. Duplicate detection can discard resends within a time window.
Sessions: group related messages with a SessionId to get FIFO ordering within the group and exclusive processing by one consumer at a time.
Scheduled messages: enqueue now, deliver at a future time.
Transactions: group operations (e.g., complete one message and send another) atomically.
Auto-forwarding: chain queues or subscriptions together.
Security: Microsoft Entra ID with RBAC (preferred over shared access keys), private endpoints, and customer-managed keys.
Resilience: availability zones, plus Geo-Replication or Geo-Disaster Recovery on the Premium tier.

Tiers

Standard: pay-as-you-go, shared infrastructure, messages up to 256 KB.
Premium: dedicated resources (messaging units), predictable performance, VNet and private endpoint support, and larger messages (up to 100 MB).

Quick C# example

await using var client = new ServiceBusClient(
    "mynamespace.servicebus.windows.net", new DefaultAzureCredential());

// Send
ServiceBusSender sender = client.CreateSender("orders");
await sender.SendMessageAsync(new ServiceBusMessage("Order #1234 placed"));

// Receive
ServiceBusReceiver receiver = client.CreateReceiver("orders");
ServiceBusReceivedMessage msg = await receiver.ReceiveMessageAsync();
Console.WriteLine(msg.Body.ToString());
await receiver.CompleteMessageAsync(msg);

For real workloads, ServiceBusProcessor handles concurrency, lock renewal, and error handling for you, and Azure Functions has a Service Bus trigger that does the same with almost no code.

When to use it

Order processing, payments, and other workflows where messages must not be lost
Decoupling microservices and smoothing out traffic spikes (load leveling)
Fan-out of events to multiple downstream systems via topics
Ordered processing per customer or entity using sessions

Service Bus vs. related services

	Service Bus	Storage Queues	Event Grid	Event Hubs
Model	Enterprise broker (queues, topics)	Simple queue	Event routing (reactive)	High-throughput event streaming
Ordering, sessions, transactions	Yes	No	No	Ordering per partition
Best for	Business messages and commands	Basic, cheap queuing	"Something happened" notifications	Telemetry and log ingestion

Rule of thumb: use Service Bus when a message represents work that must be processed reliably, Event Grid for lightweight notifications, and Event Hubs for massive streams of telemetry.

Find the longest consecutive streak of daily logins for each user.

2026-10-27T20:30:00.000-07:00

Longest Consecutive Daily Login Streak per User (SQL)

Table

CREATE TABLE UserLogins (
    UserId    INT,
    LoginTime DATETIME2
);

Sample data:

INSERT INTO UserLogins (UserId, LoginTime) VALUES
(1, '2026-10-01 08:15'), (1, '2026-10-01 19:40'),
(1, '2026-10-02 09:00'), (1, '2026-10-03 10:30'),
(1, '2026-10-06 07:45'), (1, '2026-10-07 08:05'),
(2, '2026-10-01 12:00'), (2, '2026-10-03 12:00'), (2, '2026-10-04 12:00'),
(3, '2026-10-05 22:10');

Approach: gaps and islands

Reduce logins to one row per user per day (users can log in many times a day).
Number each user's days with ROW_NUMBER().
Subtract that number from the date. For consecutive days the result is constant, so it identifies the streak ("island").
Group by that value, count days per streak, and take the maximum per user.

Query (SQL Server / Azure SQL)

WITH DistinctDays AS (
    SELECT DISTINCT
        UserId,
        CAST(LoginTime AS DATE) AS LoginDate
    FROM UserLogins
),
Numbered AS (
    SELECT
        UserId,
        LoginDate,
        DATEADD(DAY,
                -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate),
                LoginDate) AS StreakKey
    FROM DistinctDays
),
Streaks AS (
    SELECT
        UserId,
        MIN(LoginDate) AS StreakStart,
        MAX(LoginDate) AS StreakEnd,
        COUNT(*)       AS StreakDays
    FROM Numbered
    GROUP BY UserId, StreakKey
),
Ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY UserId
               ORDER BY StreakDays DESC, StreakStart DESC
           ) AS rn
    FROM Streaks
)
SELECT UserId, StreakStart, StreakEnd, StreakDays AS LongestStreak
FROM Ranked
WHERE rn = 1
ORDER BY UserId;

Result

UserId	StreakStart	StreakEnd	LongestStreak
1	2026-10-01	2026-10-03	3
2	2026-10-03	2026-10-04	2
3	2026-10-05	2026-10-05	1

How the streak key works

For user 1, after de-duplicating days:

LoginDate	ROW_NUMBER	LoginDate - ROW_NUMBER (StreakKey)
2026-10-01	1	2026-09-30
2026-10-02	2	2026-09-30
2026-10-03	3	2026-09-30
2026-10-06	4	2026-10-02
2026-10-07	5	2026-10-02

Consecutive days share the same key. A gap changes it, starting a new island.

Details

De-duplicate first. Without DISTINCT on the date, multiple logins in a day break the row numbering and the streak math.
CAST(... AS DATE) removes the time portion. If your timestamps are stored in UTC and "day" means the user's local day, convert the time zone first (AT TIME ZONE).
Ties: if a user has two streaks of equal length, the query returns the most recent one (StreakStart DESC). Change the order to ASC for the earliest, or use RANK() to return all tied streaks.
Users with no logins don't appear. Left join from a Users table if you need them with a 0.
Only the length, not the dates: replace the last two CTEs with

SELECT UserId, MAX(StreakDays) AS LongestStreak
FROM Streaks
GROUP BY UserId;

Variation: current streak (ending today)

WITH DistinctDays AS (
    SELECT DISTINCT UserId, CAST(LoginTime AS DATE) AS LoginDate
    FROM UserLogins
),
Numbered AS (
    SELECT UserId, LoginDate,
           DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate), LoginDate) AS StreakKey
    FROM DistinctDays
),
Streaks AS (
    SELECT UserId, MIN(LoginDate) AS StreakStart, MAX(LoginDate) AS StreakEnd, COUNT(*) AS StreakDays
    FROM Numbered
    GROUP BY UserId, StreakKey
)
SELECT UserId, StreakStart, StreakEnd, StreakDays AS CurrentStreak
FROM Streaks
WHERE StreakEnd >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE));  -- logged in today or yesterday

Alternative: LAG-based

WITH DistinctDays AS (
    SELECT DISTINCT UserId, CAST(LoginTime AS DATE) AS LoginDate
    FROM UserLogins
),
Flagged AS (
    SELECT UserId, LoginDate,
           CASE WHEN DATEDIFF(DAY,
                    LAG(LoginDate) OVER (PARTITION BY UserId ORDER BY LoginDate),
                    LoginDate) = 1
                THEN 0 ELSE 1 END AS IsNewStreak
    FROM DistinctDays
),
Grouped AS (
    SELECT UserId, LoginDate,
           SUM(IsNewStreak) OVER (
               PARTITION BY UserId ORDER BY LoginDate
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS StreakId
    FROM Flagged
)
SELECT UserId, StreakId,
       MIN(LoginDate) AS StreakStart, MAX(LoginDate) AS StreakEnd, COUNT(*) AS StreakDays
FROM Grouped
GROUP BY UserId, StreakId;

This flags a new streak whenever the previous login day isn't exactly one day earlier, then a running sum assigns a streak ID. It's easier to read and extends to other rules (for example, allowing a one-day gap).

PostgreSQL version

WITH days AS (
    SELECT DISTINCT user_id, login_time::date AS login_date
    FROM user_logins
),
numbered AS (
    SELECT user_id, login_date,
           login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS streak_key
    FROM days
),
streaks AS (
    SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS streak_days
    FROM numbered
    GROUP BY user_id, streak_key
)
SELECT DISTINCT ON (user_id) user_id, streak_start, streak_end, streak_days
FROM streaks
ORDER BY user_id, streak_days DESC, streak_start DESC;

Supporting index

CREATE INDEX IX_UserLogins_User_Time
ON UserLogins (UserId, LoginTime);

This lets the database read each user's logins already ordered. For very large tables, consider a pre-aggregated daily table (UserId, LoginDate) so the DISTINCT step isn't repeated on every run.

Compatibility

Window functions (ROW_NUMBER, LAG) work in Azure SQL, SQL Server 2012+, PostgreSQL, MySQL 8.0+, Oracle, and SQLite 3.25+. Only the date arithmetic changes by dialect: DATEADD / DATEDIFF in SQL Server, date subtraction in PostgreSQL, DATE_SUB / DATEDIFF in MySQL.

Write a query to rank employees based on salary.

2026-10-27T11:40:32.024-07:00

Employee Salary Ranking

Here is the complete, production-ready SQL script. It sets up the table structure, populates it with realistic sample data (including identical salaries to demonstrate tie-handling), and runs the ranking query.

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

-- 2. INSERT SAMPLE DATA
-- Includes identical salaries in 'Sales' and 'Engineering' to showcase tie-handling
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),
(2, 'Bob Jones', 'Sales', 75000.00),
(3, 'Charlie Brown', 'Engineering', 95000.00),
(4, 'Diana Prince', 'Marketing', 82000.00),
(5, 'Evan Wright', 'Sales', 75000.00),
(6, 'Fiona Gallagher', 'Engineering', 110000.00),
(7, 'George Clark', 'Sales', 75000.00),
(8, 'Hannah Abbott', 'Marketing', 68000.00);

-- 3. EXECUTE RANKING QUERY
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    -- Unique row index per record
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    
    -- Traditional rank (Skips ranks after ties: 1, 2, 2, 2, 5...)
    RANK() OVER (ORDER BY salary DESC) AS standard_rank,
    
    -- Dense rank (No gaps in ranking order: 1, 2, 2, 2, 3...)
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Use code with caution.

Expected Query Output

employee_id	employee_name	department	salary	row_num	standard_rank	dense_rank
6	Fiona Gallagher	Engineering	110000.00	1	1	1
1	Alice Smith	Engineering	95000.00	2	2	2
3	Charlie Brown	Engineering	95000.00	3	2	2
4	Diana Prince	Marketing	82000.00	4	4	3
2	Bob Jones	Sales	75000.00	5	5	4
5	Evan Wright	Sales	75000.00	6	5	4
7	George Clark	Sales	75000.00	7	5	4
8	Hannah Abbott	Marketing	68000.00	8	8	5

Key Takeaways from the Data Visual

Alice and Charlie both make $95,000.00. RANK places them both at 2, and skips 3. DENSE_RANK places them at 2, but the next person down (Diana) gets 3.
Bob, Evan, and George all make $75,000.00. Notice how RANK skips all the way from 5 to 8 for the final row, while DENSE_RANK moves perfectly from 4 to 5.

Running total of salaries by department.

2026-10-26T22:30:00.000-07:00

Running Total of Salaries by Department in SQL: A Complete Guide

How to build cumulative totals with window functions, avoid the common pitfalls, and keep the query fast

Introduction

A cumulative number shows up in almost every reporting request: sales to date, headcount growth, or, in this guide, the running total of salaries within each department. A running total shows how a value accumulates row by row, and it resets whenever the group changes.

Before window functions, this meant a self-join or a correlated subquery, both slow and hard to read. Today a single SUM() OVER (...) does the job. This post walks through the full solution, explains each clause, covers the mistakes that trip people up, and finishes with performance tuning.

The Scenario

We have an Employees table and want to see, for every employee, how much the department's payroll has accumulated as of their hire date.

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    Name       VARCHAR(100),
    Department VARCHAR(50),
    Salary     DECIMAL(10,2),
    HireDate   DATE
);

Sample data:

INSERT INTO Employees (EmployeeId, Name, Department, Salary, HireDate) VALUES
(1, 'Asha',  'Engineering', 90000, '2020-01-10'),
(2, 'Ravi',  'Engineering', 85000, '2021-03-05'),
(3, 'Meera', 'Engineering', 95000, '2022-07-19'),
(4, 'Karan', 'Sales',       60000, '2019-11-02'),
(5, 'Neha',  'Sales',       65000, '2021-06-14');

The Solution

SELECT
    Department,
    EmployeeId,
    Name,
    HireDate,
    Salary,
    SUM(Salary) OVER (
        PARTITION BY Department
        ORDER BY HireDate, EmployeeId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM Employees
ORDER BY Department, HireDate, EmployeeId;

Result:

Department	Name	HireDate	Salary	RunningTotal
Engineering	Asha	2020-01-10	90,000	90,000
Engineering	Ravi	2021-03-05	85,000	175,000
Engineering	Meera	2022-07-19	95,000	270,000
Sales	Karan	2019-11-02	60,000	60,000
Sales	Neha	2021-06-14	65,000	125,000

Notice that Sales starts again at 60,000 rather than continuing from Engineering's total. That reset is the point of partitioning.

How the Query Works

The OVER clause has three parts, and each one controls a different aspect of the calculation.

1. PARTITION BY Department

This splits the rows into independent groups, one per department. The sum is calculated inside each group, so totals never leak from one department into another. Unlike GROUP BY, partitioning does not collapse rows. Every employee row stays in the output.

2. ORDER BY HireDate, EmployeeId

A running total only means something with an order. Here we accumulate in hire-date order. EmployeeId is added as a tie-breaker: if two employees share a hire date, the database would otherwise be free to order them arbitrarily, and the running totals could change between executions.

3. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This is the frame. It tells the database which rows, relative to the current one, feed the sum. Here it means "from the first row of the partition through the current row." That is exactly the definition of a running total.

The Common Gotcha: Always Specify ROWS

You can write the query without the frame:

SUM(Salary) OVER (PARTITION BY Department ORDER BY HireDate)

It looks equivalent, but it isn't. Without a frame clause, SQL uses RANGE UNBOUNDED PRECEDING by default. RANGE treats rows with equal ORDER BY values as peers, so if two employees were hired on the same day, both rows receive the same total, which is usually not the intent. In SQL Server, RANGE is also typically slower because it can spill to disk.

Writing ROWS explicitly fixes both problems and makes your intent obvious to the next reader.

Variation 1: Monthly Department Payroll

Often you want the cumulative payroll over time rather than per employee. Aggregate first with GROUP BY, then apply the window function on top of the result:

WITH Monthly AS (
    SELECT Department,
           DATEFROMPARTS(YEAR(HireDate), MONTH(HireDate), 1) AS Month,
           SUM(Salary) AS MonthlySalary
    FROM Employees
    GROUP BY Department, DATEFROMPARTS(YEAR(HireDate), MONTH(HireDate), 1)
)
SELECT Department, Month, MonthlySalary,
       SUM(MonthlySalary) OVER (
           PARTITION BY Department
           ORDER BY Month
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS RunningTotal
FROM Monthly
ORDER BY Department, Month;

The CTE (WITH Monthly AS ...) produces one row per department per month. The outer query then accumulates those monthly figures. This two-step pattern, aggregate then window, is useful any time you need a running total over time periods.

Variation 2: Running Total Beside the Department Total

You can use several windows in the same query. This version shows the running total and the department's overall total on every row:

SELECT Department, Name, Salary,
       SUM(Salary) OVER (
           PARTITION BY Department
           ORDER BY Salary DESC, EmployeeId
           ROWS UNBOUNDED PRECEDING
       ) AS RunningTotal,
       SUM(Salary) OVER (PARTITION BY Department) AS DepartmentTotal
FROM Employees;

The second SUM has no ORDER BY, so it covers the whole partition and returns the same total on every row of the department. From here, the share of the department payroll reached so far is simply:

RunningTotal * 100.0 / DepartmentTotal

Ordering by Salary DESC makes this a "top earners first" view, which is handy for seeing how much of the payroll the highest-paid people account for.

Variation 3: Without Window Functions

On very old databases that lack window functions, a correlated subquery gives the same result:

SELECT e.Department, e.Name, e.Salary,
       (SELECT SUM(e2.Salary)
        FROM Employees e2
        WHERE e2.Department = e.Department
          AND (e2.HireDate < e.HireDate
               OR (e2.HireDate = e.HireDate AND e2.EmployeeId <= e.EmployeeId))
       ) AS RunningTotal
FROM Employees e;

It works, but it re-scans the table for every output row, so cost grows quickly with data size. The window version reads the data once, which is why it is the preferred approach wherever it's available.

Performance Tuning

An index that matches the PARTITION BY and ORDER BY columns lets the database read rows already in the right order and skip a separate sort:

CREATE INDEX IX_Employees_Dept_Hire
ON Employees (Department, HireDate, EmployeeId)
INCLUDE (Salary);

The key columns follow the partition and sort order, and INCLUDE (Salary) makes the index covering, so the query never has to visit the base table.

Other tips:

Filter early. Apply WHERE conditions before the window is computed so fewer rows are processed.
Read the execution plan. A Sort or Spool operator usually means the index doesn't match the window's partition and order columns.
Select only what you need. Wide result sets make the window operator more expensive.

Database Compatibility

This syntax works in:

Database	Support
Azure SQL Database / SQL Server	2012 and later
PostgreSQL	8.4 and later
MySQL	8.0 and later
Oracle	Yes
SQLite	3.25 and later

The only dialect-specific part of the examples is DATEFROMPARTS. In PostgreSQL, replace it with DATE_TRUNC('month', HireDate).

Key Takeaways

Use SUM() OVER (PARTITION BY ... ORDER BY ...) for running totals instead of self-joins or subqueries.
PARTITION BY resets the total per group, and unlike GROUP BY it keeps every row.
Add a unique tie-breaker column to the ORDER BY for deterministic results.
Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to avoid tie-related surprises and the slower RANGE default.
To get cumulative values over time, aggregate first in a CTE, then apply the window.
Back the query with an index on the partition and order columns, including the summed column.

DotNet and AI Materials

How do you handle retries, failures, duplicate messages, and dead-letter queues in Service Bus?

Retries, Failures, Duplicates, and Dead-Lettering in Service Bus

1. Two kinds of retry

2. Peek-lock settlement and redelivery

Lock duration and long processing

Retry timing: Service Bus has no built-in backoff on redelivery

3. Dead-letter queues (DLQ)

What to do with dead-lettered messages

Tuning MaxDeliveryCount

4. Duplicate messages

Broker-side duplicate detection (producer duplicates)

Idempotent consumers (the real solution)

5. Atomic operations with transactions

6. Ordering and sessions

7. Azure Functions behavior

8. Monitoring checklist

What are Durable Functions, and when would you use them?

Durable Functions

Function types

How it works

Example (C# isolated worker): function chaining

Common patterns

Built-in reliability features

Orchestrator code constraints

When to use Durable Functions

Durable Functions vs. related options

Operations and cost

Quick rule of thumb

What is Azure API Management (APIM), and why do we use it?

Azure API Management (APIM)

The three components

Core concepts

Why use it

Example policies

Tiers

Typical architecture

When APIM is a good fit

When it may be overkill

APIM vs. related services

Good practices:

What deployment options are available for a Web API in Azure—App Service or AKS?

Deployment Options for a Web API in Azure

App Service

AKS (Azure Kubernetes Service)

Side-by-side

Other options worth considering

How to choose

Cross-cutting concerns (the same on all of them)

How do you implement CI/CD for a .NET application in Azure?

CI/CD for a .NET Application in Azure

The pipeline shape

Option 1: GitHub Actions

One-time setup: passwordless Azure login

Workflow

Option 2: Azure Pipelines (YAML)

Infrastructure as Code

Database migrations

Configuration and secrets per environment

Containerized apps

Quality and safety gates

Good practices

Minimal starting point:

Find employees who have the same salary as their manager.

How do you troubleshoot a production API issue in Azure?

Troubleshooting a Production API Issue in Azure

1. Triage (first 5 minutes)

2. Check the golden signals in Application Insights

3. Narrow down by symptom

4. Platform-level diagnostics

5. Check dependencies

6. Network and connectivity

7. Compare against change history

8. Go deeper when needed

9. Mitigate while you investigate

10. Afterwards

Quick checklist:

How do you implement logging and monitoring using Application Insights?

Logging and Monitoring with Application Insights

1. Create the resource

Tuning `MaxDeliveryCount`

3. Logging with `ILogger`