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

Question

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.

Answer 1

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!