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

4 minute read
0

I have an Amazon Relational Database (Amazon RDS) for Microsoft SQL Server Multi-AZ instance. I want to synchronize SQL Server Agent jobs between the primary and secondary hosts in my instance.

Short description

Amazon RDS for SQL Server uses always on/mirroring for the Multi-AZ setup behind the scenes. SQL Server Agent stores jobs in the msdb system database. This system database doesn't replicate as part of your Multi-AZ deployment. So, the SQL Server Agent jobs don't synchronize automatically. You must recreate the jobs on the new primary after failover. However, the jobs are present on the old primary where you originally created them. If you fail back the instance to the previous primary (where you created the jobs), then 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 the manual creation of jobs on the new primary, turn on SQL Agent Job replication. When you turn on job replication in your Multi-AZ environment, the SQL Server Agent copies jobs 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 replicas.

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

Note: The following resolution doesn't apply to Amazon RDS Custom for SQL Server.

Resolution

Turn on the SQL agent replication feature

To turn on SQL Server Agent job replication, run the following procedure with the admin account on the primary instance:

Important: 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 jobs on the secondary instance are deleted. Also, all existing and newly created jobs replicate 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

Verify that the replication feature is turned on:

SELECT * from msdb.dbo.rds_fn_get_system_database_sync_objects();

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

Verify when modified and new jobs last synchronized with the secondary

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 syncs with the secondary.

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

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.

To perform a DB failover to confirm that the jobs replicate, wait for the last_sync_time to update first. Then, proceed with the Multi-AZ failover.

Supported and unsupported job categories for agent job replication

Jobs in the following categories replicate:

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

Note: Only jobs that use the step type as T-SQL replicate.

The following categories don't support replication:

  • Jobs with step types such as SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), replication, or 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.

2 Comments

Does this also apply to RDS Custom for SQL Server?

zzaaee
replied 6 months ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied 6 months ago