Making SQL Server Memory Follow the Failover

A SQL Server Failover Cluster Instance can move between nodes. max server memory, however, is configured on the SQL Server instance itself. That creates a mismatch: the instance setting is static, but instance placement is dynamic.

After failover, one node can temporarily run more SQL Server instances than usual. Static limits then produce one of two bad states: aggregate limits too high for the node, leaving too little host headroom — or limits unnecessarily low after an instance becomes the only SQL workload left on a node. I had encountered both patterns. The configuration needed to follow the cluster state.

Turn the setting into reconciliation§

Rather than model every possible failover topology, I wrote a PowerShell script that observes the current node and computes desired state from what is actually running on it. The implemented policy was deliberately simple:

usable SQL memory budget = 90% of physical node memory
per-instance target      = usable SQL memory budget / running SQL Server instances on the node

The 90% figure is an environment-specific policy, not a universal SQL Server recommendation — another host may need more reserve for the OS, backup software, monitoring, antivirus, filesystem cache or other services. The reusable pattern underneath it is:

OBSERVE → CALCULATE → COMPARE → RECONCILE

The code below is the real script, with server names, mail addresses and domain identifiers replaced by placeholders and some logging trimmed.

Discover the current placement§

The script runs on a cluster node and asks the cluster what is online here, right now. Not the preferred owner, not a static inventory:

Import-Module FailoverClusters

# The node this script is running on, and every group it currently owns.
$MyNode         = (Get-ClusterNode -Name $env:COMPUTERNAME).Name
$GroupsOnMyNode = Get-ClusterGroup | Where-Object { $_.OwnerNode -eq $MyNode }

$RunningInstances = @()

foreach ($Group in $GroupsOnMyNode) {
    # Owning the group is not the same as running the workload.
    # The SQL Server resource itself has to be Online.
    $SqlResource = $Group | Get-ClusterResource |
        Where-Object { $_.Name -like "SQL Server*" -and $_.State -eq "Online" }

    if (-not $SqlResource) { continue }

    # Group names take the form "SQL Server (INSTANCE_NAME)". The group
    # name is the reliable source for the instance; resource names are not.
    $InstanceName = $null
    if ($Group.Name -match "SQL Server \((?<Instance>.+)\)") {
        $InstanceName = $matches["Instance"]
    }

    # ... endpoint resolution, below ...
}

Two decisions are doing real work in that loop. Ownership alone is not evidence that the instance is serving — a group can be owned by this node while its resources are offline or mid-transition, so the Online state is part of the filter rather than an afterthought. And the instance name is parsed from the group name, because the resource name is free text an operator can change.

Connect to the instance, not to the node§

The physical cluster node is not the SQL connection endpoint. An FCI answers on its own virtual network name, which travels with it. So for each running instance the script finds the Network Name resource in the same group and asks the cluster for its DNS identity:

$NetworkResource = $Group | Get-ClusterResource |
    Where-Object { $_.ResourceType -eq "Network Name" -and $_.State -eq "Online" }

$NetworkName = $null
if ($NetworkResource) {
    $NetworkParams = Get-ClusterParameter -InputObject $NetworkResource

    # The parameter carrying the DNS name is not consistently named.
    $ParamNameToCheck = "Name","HostName","DnsName"
    $NetworkParam = $NetworkParams | Where-Object { $ParamNameToCheck -contains $_.Name }

    if ($NetworkParam) {
        $NetworkName = ($NetworkParam.Value | Select-Object -First 1)
    } else {
        # Do not guess. Print what the resource actually exposes.
        Write-Host "No known DNS name parameter found for: $($NetworkResource.Name)"
        $NetworkParams | Format-Table Name,Value
    }
}

# A default instance answers on the network name alone.
$ServerName = if ($InstanceName -eq "MSSQLSERVER") {
    $NetworkName
} else {
    if ($NetworkName) { "$NetworkName\$InstanceName" } else { $InstanceName }
}

$RunningInstances += [PSCustomObject]@{
    GroupName    = $Group.Name
    InstanceName = $InstanceName
    NetworkName  = $NetworkName
    ServerName   = $ServerName
}

The three candidate parameter names are not defensive padding — the DNS name has surfaced under different parameter names across cluster and Windows versions in this estate. When none of them match, the script dumps the full parameter table instead of inventing an endpoint, which turns “this cluster is different” into a two-minute diagnosis.

The final else branch is the honest weak spot: with no network name it falls back to the bare instance name, which is usually not connectable. That is deliberate rather than silent — the connection attempt in the next step fails, and the instance is skipped instead of being configured from a guess.

Calculate the desired value§

$MemoryPercentage = 0.90
$InstanceCount    = $RunningInstances.Count

if ($InstanceCount -gt 0) {

    $TotalMemoryGB       = (Get-WmiObject -Class Win32_ComputerSystem).TotalPhysicalMemory / 1GB
    $AvailableMemoryGB   = $TotalMemoryGB * $MemoryPercentage
    $MemoryPerInstanceMB = [math]::Round(($AvailableMemoryGB / $InstanceCount) * 1024)

} else {
    Write-Host "No running instances found. Cannot set memory."
}

The $InstanceCount -gt 0 guard wraps the entire configuration block, so a node with nothing online divides by nothing and changes nothing.

Every running instance on that node receives the same target. Equal division is intentionally easy to understand and audit. It is not workload-aware — if two instances have radically different working sets, a weighted allocation model could be more efficient, but it also introduces more feedback logic to stabilise and test. I preferred a deterministic safety policy first.

Compare before changing anything§

A reconciliation loop should be idempotent. If desired state already matches current state, the correct action is no action:

foreach ($instanceInfo in $RunningInstances) {

    if ([string]::IsNullOrWhiteSpace($instanceInfo.ServerName)) {
        Write-Host "No valid server name for '$($instanceInfo.InstanceName)'. Skipping."
        continue
    }

    try {
        $CurrentMax = Invoke-Sqlcmd -ServerInstance $instanceInfo.ServerName -Query "
            SELECT CAST(value_in_use AS INT) AS CurrentMaxMemoryMB
            FROM sys.configurations
            WHERE name = 'max server memory (MB)';
        " -ErrorAction Stop | Select-Object -ExpandProperty CurrentMaxMemoryMB
    } catch {
        Write-Host "Failed to query current memory for '$($instanceInfo.ServerName)'. Skipping..."
        continue
    }

    if ($CurrentMax -eq $MemoryPerInstanceMB) {
        Write-Host "'$($instanceInfo.InstanceName)' is already $MemoryPerInstanceMB MB. No update needed."
        continue
    }

    # ... notify, then apply ...
}

value_in_use rather than value is the point of that query. value is what was requested; value_in_use is what the engine is running with. A reconciler that compares against the requested value can convince itself that a never-reconfigured setting has already been applied.

The write is the same three statements a DBA would run by hand, including putting the advanced-options flag back the way it was found:

$SqlQuery = @"
EXEC sys.sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sys.sp_configure 'max server memory (MB)', $MemoryPerInstanceMB; RECONFIGURE;
EXEC sys.sp_configure 'show advanced options', 0; RECONFIGURE;
"@

try {
    Invoke-Sqlcmd -ServerInstance $instanceInfo.ServerName -Query $SqlQuery -ErrorAction Stop
    Write-Host "Max server memory for '$($instanceInfo.InstanceName)' set to $MemoryPerInstanceMB MB."
} catch {
    Write-Host "Could not set memory for '$($instanceInfo.InstanceName)'. Check connectivity and permissions."
}

No credential is passed to Invoke-Sqlcmd. The script authenticates as the scheduled task’s own service account over Windows authentication, so there is no SQL password stored on the node. That detail matters later.

A memory change is a failover signal§

Because the script only writes on drift, sending mail on the write path costs nothing when nothing moved — and when something does move, the mail is the failover notification:

$smtpServer = "smtp.internal.example"
$smtpPort   = 25
$from       = "[email protected]"
$to         = @("[email protected]", "[email protected]")

$subject = "ALERT: Updating max server memory for SQL Server $SQLServerName on node $MyNode"
$body    = "ALERT: Updating max server memory for SQL Server $SQLServerName on node $MyNode " +
           "from $CurrentMax MB to $MemoryPerInstanceMB MB. " +
           "Number of instances running on node $MyNode is $InstanceCount"

Send-MailMessage -From $from -To $to -Subject $subject -Body $body `
                 -SmtpServer $smtpServer -Port $smtpPort -Priority "High"

Idempotence gives quiet alerting for free. An inbox that only receives mail when a node’s instance count changed is an inbox worth reading.

There is an unfinished idea sitting in that block in the real script. The priority was meant to be conditional: high only when the node is carrying more instances than the cluster’s node count implies it should, normal otherwise. The instance-per-node calculation is still commented out at the top of the file, so in practice every change pages at high priority. It is a small thing, and it is exactly the kind of small thing that never gets finished while the policy lives in a file on forty-eight machines.

Failover events are noisy§

A cluster transition generates more than one event. Boot overlaps with resource startup, several resources change state within seconds of each other, and a scheduled run can land next to an event-driven one. Without protection, several copies of the script start at once and race to configure the same instances from different instance counts.

The guard is a timestamp file next to the script:

$scriptDirectory = Split-Path -Path $MyInvocation.MyCommand.Path
$lockFile = Join-Path -Path $scriptDirectory -ChildPath "max-memory-mgmt-lock-file.txt"

$currentTime = Get-Date

if (Test-Path $lockFile) {
    $lastRunTime = Get-Date (Get-Content $lockFile)

    if (($currentTime - $lastRunTime).TotalMinutes -lt 10) {
        Write-Output "Task already ran within the last 10 minutes. Exiting..."
        exit 0
    }
}

$currentTime.ToString() | Out-File $lockFile -Force

This is a debounce, not a mutex. Two processes starting in the same second can both read a stale timestamp and both proceed. That case is covered a layer up, by the scheduled task refusing to start a second copy of itself; the lock file covers the burst that arrives after the first run has already finished. Calling it a lock file — which I did, in the filename — oversells what it is.

Event-driven plus periodic reconciliation§

The trigger definition is the other half of the design, and it ships as the scheduled task XML deployed alongside the script:

<!-- One EventTrigger per cluster event ID. The Subscription is stored
     XML-escaped in the file; shown decoded here for readability. -->
<EventTrigger>
  <Enabled>true</Enabled>
  <Subscription>
    <QueryList>
      <Query Id="0" Path="Microsoft-Windows-FailoverClustering/Operational">
        <Select Path="Microsoft-Windows-FailoverClustering/Operational">
          *[System[Provider[@Name='Microsoft-Windows-FailoverClustering'] and EventID=1201]]
        </Select>
      </Query>
    </QueryList>
  </Subscription>
  <Delay>PT10M</Delay>
</EventTrigger>

<BootTrigger>
  <Enabled>true</Enabled>
  <Delay>PT10M</Delay>
</BootTrigger>

<CalendarTrigger>
  <StartBoundary>2026-01-01T22:00:00</StartBoundary>
  <Enabled>true</Enabled>
  <ScheduleByDay><DaysInterval>1</DaysInterval></ScheduleByDay>
</CalendarTrigger>

Six event IDs from the Microsoft-Windows-FailoverClustering/Operational log are subscribed the same way — 1069, 1201, 1204, 1254, 1641 and 1674 — covering resource failures, groups coming online and failing to come online, and failover-threshold events. Plus boot, plus a daily run.

The PT10M delay on every trigger is the most important line in the file. The instinct is to react immediately, but the ten minutes are the point: a cluster mid-transition is not a state worth measuring. Resources are still coming online, ownership is still settling, and an instance count read at that moment is a number that will be wrong in ninety seconds. The delay lets the cluster finish moving before the script asks it what happened.

The same ten minutes appear in the script’s debounce window, so the two layers agree on what “one failover event” means.

The rest of the settings are unremarkable and load-bearing:

<MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
<StartWhenAvailable>true</StartWhenAvailable>
<ExecutionTimeLimit>PT1H</ExecutionTimeLimit>

IgnoreNew is the real concurrency guarantee — six event triggers firing into one task definition produce one running copy. StartWhenAvailable recovers the daily run on a node that was down at 22:00. The task runs S4U with HighestAvailable, so no password is stored in the task definition itself.

Event-driven execution gives quick convergence after a known change; periodic execution repairs missed events and configuration drift. A repair path is valuable even when event triggers are reliable — infrastructure automation should not assume every event was observed forever.

Getting it onto forty-eight nodes§

None of the above is interesting until it exists everywhere. The deployment was a second script, run once from a workstation, that pushed both files and registered the task across the estate:

$RemoteHosts = @(
    # Production clusters, two nodes each
    "sitea-p1-sqlnode1", "sitea-p1-sqlnode2",
    "siteb-p1-sqlnode1", "siteb-p1-sqlnode2",
    # ... and the larger four-node clusters
    "sitei-p5-sqlnode1", "sitei-p5-sqlnode2",
    "sitei-p5-sqlnode3", "sitei-p5-sqlnode4"
    # ... staging and integration clusters follow the same pattern
)

$credential   = Get-Credential -Message "Service account for the scheduled task."
$taskUser     = $credential.UserName
$taskPassword = $credential.GetNetworkCredential().Password

$scriptContentPS  = Get-Content -Path ".\dynamic-sqlserver-max-memory.ps1"  -Raw
$scriptContentXML = Get-Content -Path ".\dynamic-sqlserver-max-memory.xml" -Raw

foreach ($remoteHost in $RemoteHosts) {
    try {
        Invoke-Command -ComputerName $remoteHost -ScriptBlock {
            if (-not (Test-Path "C:\scripts")) {
                New-Item -ItemType Directory -Path "C:\scripts" | Out-Null
            }
        }

        Invoke-Command -ComputerName $remoteHost -ScriptBlock {
            param ($content, $destination)
            Set-Content -Path $destination -Value $content
        } -ArgumentList $scriptContentPS, "C:\scripts\dynamic-sqlserver-max-memory.ps1"

        # ... same again for the XML ...

        Invoke-Command -ComputerName $remoteHost -ScriptBlock {
            param ($xmlPath, $user, $password)
            schtasks /Create /XML $xmlPath /TN "dynamic-sqlserver-max-memory" `
                     /RU $user /RP $password /F | Out-Null
        } -ArgumentList "C:\scripts\dynamic-sqlserver-max-memory.xml", $taskUser, $taskPassword

    } catch {
        Write-Host "Failed to process $remoteHost $_" -ForegroundColor Red
    }
}

It works, and the /F on schtasks makes re-running it the upgrade path. But look at what it actually is: a hand-maintained host list, a credential typed into a prompt and handed to forty-eight remote schtasks calls, and a try/catch whose failure mode is a red line in a console window that nobody will see again. If one node is unreachable that evening, it keeps the old version and nothing anywhere records that fact.

Why reconciliation beats a failover matrix§

A topology-specific design tends to become a growing list of conditionals — if A moves to node 2, set this; if B moves to node 1, set that; if A and C end up together, set something else. That gets harder to reason about as the cluster grows.

The reconciliation approach does not care how the current state was reached. It only needs physical memory and the currently running instances to derive the desired configuration, which is much easier to test than a matrix of failover scenarios.

Failure boundaries§

The safest result is to refuse a change when a prerequisite observation is missing or invalid: physical memory cannot be read, running SQL resources cannot be determined, an endpoint cannot be resolved, current configuration cannot be read, or the calculated target is invalid. Each of those has an explicit continue or an early exit in the code above.

Where this should live instead§

The deployment script is the tell. The reconciler is forty lines of real logic wrapped in a distribution problem, and the distribution problem is larger than the logic. The plan is to move it into the database engineering portal — the same platform that already holds inventory, a work queue, worker ownership and the observation/control split — rather than keep it as a standalone script scheduled on every node.

Most of what is weak here stops being a problem the moment it moves:

• The policy is a literal in a file on forty-eight machines. The 90%, the ten-minute window, the abandoned instances-per-node calculation — all of it becomes inventory-backed configuration, set once, per cluster where the host profile differs.
• There is no record. Today a memory change produces an email. It should produce a row: which node, which instance, from what value to what value, triggered by which cluster event, and whether it succeeded.
• Version drift is invisible. The platform knows which nodes exist and can report which are running which version, instead of that being knowable only by connecting to each one.
• Alerting is a per-node SMTP call. Send-MailMessage is deprecated, duplicated forty-eight times, and bypasses the alert routing everything else in the estate already uses.

That split is the part worth being careful about, because the obvious version of this idea — the portal connects to each cluster and sets memory remotely — is worse than what exists today, for two reasons.

The first is timing. A node-local task fires on the cluster’s own event log within seconds of the cluster settling. A central controller has to learn that a failover happened, and the moment immediately after a failover is the worst possible moment to add a dependency on an external system being reachable. Site failure, network partition and a failover storm are correlated events, not independent ones. A memory limit that only converges when the platform is healthy is a memory limit that is wrong exactly when it matters.

The second is blast radius. The script today authenticates as a local service account over Windows auth — nothing is stored, and its reach ends at its own cluster. A central controller with sp_configure rights on every instance in the estate is a meaningfully different security object, and it needs the permissions, audit trail and hard inspect/change separation that the portal’s control surface is being designed around anyway.

So: yes, it should be part of the portal, as a control plane rather than as a remote executor. The portal computes and distributes policy, records every action, and reports drift. The node keeps the reflex — because the whole point of this script is that it works during a failover, and a control plane that has to be reachable during a failover has not understood the problem it is solving.

Limitations§

Equal shares are not workload shares. Equal allocation prioritises predictable host safety over perfect utilisation. A more advanced controller could incorporate workload history or minimum/maximum bands, but that adds behaviour that has to be stabilised and tested in its own right.

The host reserve is environment-specific. The implemented 90% SQL budget is a policy parameter, not a magic constant, and a different host profile would justify a different number.

max server memory is not total process memory. SQL Server has allocations outside the memory that setting controls. Host headroom still matters, and summing configured limits does not perfectly predict resident process memory.

The debounce timestamp is locale-dependent. $currentTime.ToString() writes in the node’s culture and Get-Date parses it back the same way. A round-trip format would be the durable choice; this one works until a node is built with a different locale.

Nothing enforces a floor. A node that briefly owns an unusually large number of instances will divide its budget down accordingly. A minimum per-instance target would be a cheap addition, and is the first thing I would add when the policy moves into inventory.

The broader pattern§

This script is small, but it follows the same pattern as larger reliability systems: observe actual state, derive desired state, compare, apply only on drift, and repeat after events and periodically. It does not try to make failover predictable. It makes the configuration adapt after failover — and that is the more useful property.