How do I synchronize SQL Server Agent jobs between the primary and secondary hosts in my RDS for SQL Server Multi-AZ instance?

Short description

Amazon RDS for SQL Server uses always on/mirroring for the Multi-AZ setup behind the scenes. SQL Server Agent jobs are stored in the msdb system database. This system database isn't replicated as part of your Multi-AZ deployment. So, the SQL Server Agent jobs aren't synchronized automatically. You must recreate the jobs on the new primary after failover. However, the jobs are present on the old primary where they were originally created. If you fail back the instance to the previous primary (where the jobs were initially created) you still see the jobs. To create the jobs in Multi-AZ, create the jobs in the primary (active) instance first. Then, fail over the RDS instance and create the same jobs on the new primary instance.

To avoid manually creating jobs on the new primary, turn on SQL Agent Job replication. When job replication is turned on in your Multi-AZ environment, the SQL Server Agent jobs are copied from the primary host to the secondary host automatically. You don't have to create the jobs manually on the new primary replica because they synchronized through the agent replication feature. The jobs are available in both the replicas.

For more information, see Multi-AZ deployments for Amazon RDS for Microsoft SQL Server.

Resolution

Turn on the SQL agent replication feature

Run the following procedure with the admin account on the primary instance to turn on SQL Server Agent job replication.

Important note: Make sure that you run this procedure on the instance that has all the agent jobs available. If the instance without the available agent jobs becomes the primary and you turn on this feature, then any jobs on the secondary instance are deleted. Also note that all existing and newly created jobs are replicated as part of this feature.

EXECUTE msdb.dbo.rds_set_system_database_sync_objects
@object_types = 'SQLAgentJob';

Validate that the SQL agent replication feature is turned on

Use the following query to verify that the replication feature is turned on:

SELECT * from msdb.dbo.rds_fn_get_system_database_sync_objects();

The preceding query returns SQLagentjob for object_class if the replication feature is turned on. If the query returns nothing, then the feature isn't turned on.

Verify when modified and new jobs last synchronized with the secondary

Use the following query to check the last_sync_time of the replication:

SELECT * from msdb.dbo.rds_fn_server_object_last_sync_time();

In the query results, if the sync time is past the job updated or creation time, then the job is synced with the secondary.

Note: If you don't know the time of the job creation or update, run the following query to retrieve the timestamp, and then run the preceding query:

select name as 'JobName'
,JobStatus = CASE
    When enabled =1 Then 'Active'
    Else 'Inactive'
    END
,date_created As 'JobCreatedOn' ,date_modified as 'LastModified'
from msdb..sysjobs

Note: It takes few minutes for the jobs to synchronize between the replicas.

If you want to perform DB failover to confirm that the jobs are replicated, wait for the last_sync_time to update before proceeding with the Multi-AZ failover.

Status of the jobs on secondary

A SQL Server Agent XP is in the Disabled state with or without using the replication feature on the secondary replica. So, the jobs don't run on the secondary server.

Supported and unsupported job categories for agent job replication

Jobs in the following categories are replicated:

• [Uncategorized (Local)]
• [Uncategorized (Multi-Server)]
• [Uncategorized]
• Data Collector
• Database Engine Tuning Advisor
• Database Maintenance
• Full-Text

Note: Only jobs that use step type as T-SQL are replicated.

The following are categories that don't support replication:

• Jobs with step types such as SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), replication, and PowerShell.
• Jobs that use Database Mail and server-level objects.

Turn off SQL Server Agent job replication

To turn off SQL Server Agent job replication, run the following command:

EXECUTE msdb.dbo.rds_set_system_database_sync_objects @object_types = '';

After you turn off replication, modifications to existing and newly created jobs no longer sync with the other replica.

---