Enhancement Request: Ability to pass parameters in Freeze/Thaw scripts

[ratkinsonuk]

I've created a Powershell script that allows the backup of a Server B to wait for the snapshot of Server A to complete. It takes parameters that allow everything to happen using a single script.

Code: Select all

<# 
------------------------------------------------------------------------------
AUTHOR: Robert Atkinson - April 2025 
------------------------------------------------------------------------------
DESCRIPTION: Utility to allow servers to be chained together so that their backups run in sequence
------------------------------------------------------------------------------
RUN FREQUENCY      : As required
PRERUN REQUISITES  : None
PARAMETERS         : Action -
                                 - CreateTrigger - Create the trigger file for a server
                                 - WaitForTrigger - Wait for the trigger file, then delete it
                                 - DeleteTrigger - Delete the trigger file for a server
                     Server - Server name(s) to create triggers for

USAGE              : "Backup Sync.ps1" -Action DeleteTrigger -Server "UKWSWOSQLPIN01"  # Clear down the trigger file for a fresh run
                     "Backup Sync.ps1" -Action CreateTrigger -Server "UKWSWOSQLPIN01"  # Create the trigger file to say the server snapshot is complete
                     "Backup Sync.ps1" -Action WaitForTrigger -Server "UKWSWOSQLPIN01"  # Wait for the trigger file, then delete it and continue with the next backup
#>

Unfortunately, Veeam B&R can't pass parameters from the backup job config to the script being executed remotely, so the script has to be split into 3, one for each action. If I want to run the same process on a different backup, I'd need to copy the same scripts again, meaning 6 scripts to maintain and all doing the same thing.

What I'd like is the ability to pass parameters in the job configuration. I'm aware I could do something with a .BAT file to call the script with parameters instead or even split the backup and have them chain that way, but this Powershell 'Wait For' process is the cleanest way to do it.

Apologies if this enhancement is already logged somewhere - I couldn't find it in a search.

Thanks, Rob.

[david.domask]

Hi Rob,

Thanks for the request -- I'm not aware of the request earlier but it can be considered sure. I know it's possible with Pre/Post-job scripts so perhaps there's a technical limitation as to why this isn't possible with pre-freeze/post-thaw. Let me research this a bit and I'll update the thread.

As for the use-case for your script, can you explain the workflow a bit more? I understand you're trying to avoid two machines being snapshotted at the same time and based on your script sample looks like it's about SQL nodes - there are a few ways this could be handled potentially without scripting but if you can explain what the script does and a bit more on the use-case for the script, can advise better.

[ratkinsonuk]

This is the complete code David. Basically, it uses a trigger file to determine that the snapshot has been created. We created the procedure because both SQL nodes in an AG Failover set are in the same backup, and Veeam's snapshotting them at the same time which means they aren't able to failover properly. I think it causes the SQL services to stop completely. We need(ed) a pause between the servers, and the chaining script seemed the simplest approach.

Code: Select all

#region Change Control
<# 
------------------------------------------------------------------------------
AUTHOR: Robert Atkinson - April 2025 
------------------------------------------------------------------------------
DESCRIPTION: Utility to allow servers to be chained together so that their backups run in sequence
------------------------------------------------------------------------------
RUN FREQUENCY      : As required
PRERUN REQUISITES  : None
PARAMETERS         : Action -
                        - CreateTrigger - Create the trigger file for a server
                        - WaitForTrigger - Wait for the trigger file, then delete it
                        - DeleteTrigger - Delete the trigger file for a server
                     Server - Server name(s) to create triggers for

USAGE              : "Backup Sync.ps1" -Action DeleteTrigger -Server "UKWSWOSQLPIN01"  # Clear down the trigger file for a fresh run
                     "Backup Sync.ps1" -Action CreateTrigger -Server "UKWSWOSQLPIN01"  # Create the trigger file to say the server snapshot is complete
                     "Backup Sync.ps1" -Action WaitForTrigger -Server "UKWSWOSQLPIN01"  # Wait for the trigger file, then delete it and continue with the next backup

ENHANCEMENTS       : Code doesn't currently allow for more than one server
------------------------------------------------------------------------------
AMENDMENT LOG:
Date        By          Reason
------------------------------------------------------------------------------

#>
#endregion


#region CLI Parameters

[CmdletBinding()]
param
(
    [Parameter(Mandatory = $True)]
    [string] $Action,
    [Parameter(Mandatory = $True)]
    [string] $Server
)


Import-Module "PRH-Loader"
Set-Config

try {
    $LogFile = "$Global:LogFilePath\Backup Sync\Backup Sync - $Server - " + (Get-Date -format FileDateTime) + ".log"
    Start-Transcript -Path $LogFile -Append

    Write-Verbose "Action = $Action"
    Write-Verbose "Server = $Server"

    if ([string]::IsNullOrEmpty($Server.Trim())) {
        Throw "No server name supplied"
    }
    $TriggerFilePath = "$Global:DataFilePath\Backup Sync\"
    $TriggerFile = "$Global:DataFilePath\Backup Sync\Trigger-$Server.txt"
    Write-Verbose "TriggerFile = $TriggerFile"


    switch ($Action) {

        ### Delete Trigger ###
        ("DeleteTrigger") {
            Write-Verbose "Running DeleteTrigger"

            if ($null -ne (Get-ChildItem $TriggerFile -ErrorAction SilentlyContinue)) {
                Remove-Item $TriggerFile -Verbose
            }
        }

        
        ### Create Trigger ###
        ("CreateTrigger") {
            Write-Verbose "Running CreateTrigger"

            if ($null -ne (Get-ChildItem $TriggerFile -ErrorAction SilentlyContinue)) {
                Remove-Item $TriggerFile -Verbose
            }

            if (!(Test-Path $TriggerFilePath)) {
                New-Item -Path $TriggerFilePath -ItemType Directory  -Verbose | Out-Null

            }

            "Trigger file created at $(Get-Date)" | Out-File $TriggerFile -Verbose
            Write-Host "Trigger file $TriggerFile created"
        }


        ### Wait For Trigger Trigger ###
        ("WaitForTrigger") {
            Write-Verbose "Running WaitForTrigger"

            do {
                if ($null -ne (Get-ChildItem $TriggerFile -ErrorAction SilentlyContinue)) {
                    Remove-Item $TriggerFile -Verbose
                    Write-Host "Trigger file found and removed at $(Get-Date) - exiting"
                    exit
                }

                Write-Host "Trigger file not found - sleeping for 10 seconds at $(Get-Date)"
                Start-Sleep -Seconds 10
                

            } while (1 -eq 1)


        }


        Default {
            Throw "Invalid action - use CreateTrigger, DeleteTrigger or WaitForTrigger"
        }
    }
    
}
catch {
    throw
}
finally {
    Stop-Transcript
}



#endregion

[david.domask]

Got it, thank you for explaining and sharing the script.

Regarding why we don't have this functionality, simply put it hasn't been requested yet, so we can consider your request for sure.

However, for the issue you're trying to solve, a few questions:

1. Have you considered the Veeam SQL plugin? The plugin does not require VM snapshots and backs up the data directly from SQL (including clusters), and I think solves a lot of your issues today.
2. Additionally have you considered Veeam Agent for Windows, as it has support for MSSQL clusters, and avoids the snapshot issue as well.

[ratkinsonuk]

Thanks David, it would be great to get that functionality introduced.

The backup problem isn't really anything to do with SQL (we're taking a backup of the VM only excluding SQL), it's due to the server being stunned by VMWare. The SQL AG sees the I/O being paused because of the stun and tries to failover the AG. At the same time, the secondary node is being stunned and SQL decides the whole cluster is dead and kills itself.

The solution is either to put a gap between the stuns and let the AG failover, or even better, do a controlled failover from one node to the other. Both would require me splitting the backup in two because of the freeze/thaw parameter limitations unfortunately.

[david.domask]

Thank you for the request

As for the problem, the issue is understood, but my point more is that both the SQL plugin AND Veeam Agent for Windows are able to handle SQL AGs quite readily and avoid the stun entirely. By removing the snapshot (which does add a lot of pressure to the clusters and often triggers failovers) and doing the backup to a VDI device (SQL Plugin) or doing an in-guest backup, the pressure ought be considerable less and you can avoid the stun issues.

[ratkinsonuk]

Would it be worth setting up a conversation through my account manager David? I'd like to understand the difference between the VMware snapshot process and how the SQL plugin works.

[david.domask]

I strongly recommend do reach out to your Account Manager for a walk-through and discussion on the options for backing up SQL with Veeam, they will be able to help explain the differences and pros and cons.

In brief the main difference is that the VM backup method uses VSS + VM snapshots to quiesce the databases -- while this is fine and supported, snapshots always introduce a performance penalty for a VM. I think that's the biggest part to consider as the impact from snapshots can be quite substantial and often triggers the failovers.