How to synchronize SQL Server Agent jobs across availability group replicas on Linux?

dbatools can sync them but I haven't tried it on an AG running on linux. Let me know if it works or not! The first parameter is the name of your AG, the second is the virtual network name of your cluster.

param($AvailabilityGroup, $SqlInstance)
    try {
        $replicas = Get-DbaAgReplica -AvailabilityGroup $AvailabilityGroup -SqlInstance $SqlInstance
        $primary = $replicas | Where-Object Role -EQ Primary | Select-Object -ExpandProperty Name
        $secondaries = $replicas | Where-Object Role -EQ Secondary | Select-Object -ExpandProperty Name
        $primaryInstanceConnection = Connect-DbaInstance $primary -ClientName 'ScriptBorrowedFromStackOverFlow'
        $secondaries | ForEach-Object {
            $secondaryInstanceConnection = Connect-DbaInstance $_ -ClientName 'ScriptBorrowedFromStackOverFlow'
            Copy-DbaAgentJob -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection -Force
        }
    }
    catch {
        $msg = $_.Exception.Message
        Write-Error "Error while syncing jobs for Availability Group '$($AvailabilityGroup): $msg'"
    }

After some trial and error, I ended up with this script that works on Ubuntu Linux 18.04. Big thanks to Derik Hammer and his blog for the base of the script and also to David Söderlund for his reply.

For the script to work, you will need to install PowerShell for Linux and both DBATools and SqlCmd2 PowerShell modules. You will also have to store sql credentials in a file somewhere. I chose /var/opt/mssql/secrets/creds.xml for mine and changed access rights to root only. Script can sync logins, DBmail settings, SQL Agent categories, jobs, operators and schedules from primary replica to all secondaries (uncomment what you need, but be careful, order matters and some things cannot be synched in one connection, i.e operators and jobs), skipping configuration replicas if you have any.

You can set up scheduled execution as root with output logged into file using CRON. To set this up, run:

sudo crontab -e

and adding this line to the file:

*/5 * * * * pwsh /<PATH>/sync-sql-objects.ps1 >> /<PATH>/sync-sql-objects.log

Script:

<# 
.DESCRIPTION
This script will detect your Availability Group replicas and copy all of its instance level objects from primary replica to secondary replicas within the Availability Group. It will skip any configuration replicas.
 
.EXAMPLE
sudo pwsh sync-sql-objects.ps1
 
.NOTES
One limitation of this script is that it assumes you only have one availability group. This script should run on your configuration replica server.
 
.LINK
https://www.sqlhammer.com/synchronizing-server-objects-for-availability-groups/
 
DEBUG
To see logs on Ubuntu Linux, install Postfix Mail Transfer Agent and then go to see mails in /var/mail/<username>
#>  

Write-Output ("Sync started: " + (Get-Date -Format G))
 
#Error handling
$ErrorActionPreference = "stop";
 
Trap 
{
    $err = $_.Exception
    while ( $err.InnerException )
    {
 
        $err = $err.InnerException
        Write-Output $err.Message
 
    };
}
 
# Prerequisites
try
{
    Write-Output "Valiating prerequisites."

    # You need to have these modules installed in advance, otherwise the import will fail

    if ((Get-Module -Name dbatools) -eq $null)
    {
        Import-Module dbatools | Out-Null
    }

    if ((Get-Module -Name Invoke-SqlCmd2) -eq $null)
    {
        Import-Module Invoke-SqlCmd2 | Out-Null
    }

    Write-Output "Prerequisites loaded."
 
}
catch
{
    Write-Error $_.Exception.Message -EA Continue
    Write-Error "One or more of the prerequisites did not load. Review previous errors for more details." -EA Continue
    return
}

# Detect Availability Group replicas
Write-Output "Begin query for Availability Group replicas"
 
$ConfigurationMode = "CONFIGURATION_ONLY"
$Hostname = hostname 
$Credentials = Import-CliXml -Path /var/opt/mssql/secrets/creds.xml

$ReplicasQuery = @"
SELECT replica_server_name,
availability_mode_desc,
primary_replica
FROM sys.availability_replicas AR
INNER JOIN sys.dm_hadr_availability_group_states HAGS
INNER JOIN sys.availability_groups AG ON AG.group_id = HAGS.group_id
    ON HAGS.group_id = AR.group_id;
"@

$Replicas = Invoke-Sqlcmd2 -ServerInstance $Hostname -Query $ReplicasQuery -ConnectionTimeout 30 -Credential $Credentials
 
if(([DBNull]::Value).Equals($Replicas[0].primary_replica))
{
    Write-Error "Availability Group query returned no results. Confirm that you connected to a SQL Server instance running an Availability Group. No work was accomplished."
    return
}
 
Write-Output "Completed query of Availability Group replicas"
 
foreach($replica in $Replicas)
{
    # Skip if destination replica is primary replica itself
    if($replica.primary_replica.CompareTo($replica.replica_server_name) -eq 0)
    {
        continue
    }
 
    # Skip configuration replicas
    if($replica.availability_mode_desc.CompareTo($ConfigurationMode) -eq 0)
    {
        continue
    }

    #Connect
    $PrimaryReplica = Connect-DbaInstance $replica.primary_replica -ClientName 'ConfigurationReplica' -SqlCredential $Credentials
    $SecondaryReplica = Connect-DbaInstance $replica.replica_server_name -ClientName 'ConfigurationReplica' -SqlCredential $Credentials

    Write-Output "Copying instance objects from $sourceReplica to $replica"

    # Copy objects
    # Write-Output "Copying Logins."
    # Copy-DbaLogin -Source $PrimaryReplica -Destination $SecondaryReplica

    # Write-Output "Copying DBMail."
    # Copy-DbaDbMail -Source $PrimaryReplica -Destination $SecondaryReplica -Force

    # Write-Output "Copying Agent Categories."
    # Copy-DbaAgentJobCategory -Source $PrimaryReplica -Destination $SecondaryReplica -Force

    # Write-Output "Copying Agent Schedules."
    # Copy-DbaAgentSchedule -Source $PrimaryReplica -Destination $SecondaryReplica -Force

    # Write-Output "Copying Operators."
    # Copy-DbaAgentOperator -Source $PrimaryReplica -Destination $SecondaryReplica -Force

    Write-Output "Copying Jobs."
    Copy-DbaAgentJob -Source $PrimaryReplica -Destination $SecondaryReplica -Force
       
    Write-Output "Copy complete from $PrimaryReplica to $SecondaryReplica"
}

Write-Output "SQL Instance object sync complete."

Enjoy!