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

I have two SQL Server 2019 instances running on Linux. These two instances both contain a single database which is synchronized using AlwaysOn Availability Group. Data in the database is synchronized, but the problem is that the SQL Agent jobs are not part of the database itself.

Therefore, when I create a SQL Server Agent job on the primary replica, this configuration does not copy to the secondary replica. So, after creating each job, I always have to also go to the secondary and create the job there as well. And I have to keep track of all the changes I make all the time.

Is there a built-in way to automate this cross-replica synchronization of SQL Server jobs on Linux when using availability groups? Job synchronization across AG replicas seems like something that should already be natively supported by SQL Server/SQL Server Agent tools, but I found nothing from Microsoft, only a third-party tool for called DBA Tools that I can use to write my own automation scripts in PowerShell.

1 answer

  • answered 2020-08-26 06:02 David Söderlund

    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'"
        }