-1

I have two servers (Primary and Secondary), as well as an AG that keeps them in sync.

As we all know, System databases are not covered by the AG definition.

1.I require that all jobs on the primary server be in sync with those on the secondary server.

2.However, I just want the job to run on the primary, not the secondary. Is there anything I can do? Any sql code ? Kindly explain I am new to DBA.

Improve this question

2 Answers 2

Reset to default
3

I recommend you to go through dbatools It contains all the Powershell modules required to manage the SQL Server Always On Availability Group.

1.I require that all jobs on the primary server be in sync with those on the secondary server.

For Object syncing, check this package: Sync-DbaAvailabilityGroup run periodically or schedule it.

2.However, I just want the job to run on the primary, not the secondary. Is there anything I can do? Any sql code ? Kindly explain I am new to DBA.

For running job only on the primary, at the beginning of the job, check whether is it primary or secondary, if primary, run the job.

sys.dm_hadr_database_replica_states dynamic management view will expose that information.

Check this SE answer for the simple conditional logic implementation for the same.

Improve this answer
1
  • Can I write sys.dm_hadr_database_replica_states and invoke power shell together in the sql script? One is sql and other is ps.
    – variable
    Jan 18 at 17:15
0

With regards to only running on the primary this is what I do

  1. Create the first step in the agent job to check if the server is primary or secondary. This needs to be done in the context of the master database

IF (SELECT ars.role_desc FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_groups ag ON ars.group_id = ag.group_id AND ars.is_local = 1) <> 'Primary' BEGIN -- Secondary node, throw an error raiserror ('Not the AG primary', 2, 1) END

Then for the advanced steps set the following On Success action: Go to the next step On failure action: quit the job reporting success

Then you can have job the jobs active at the same time. On the secondary node the job will stop after step 1 and not report a failure and the primary node will go to step 2 and continue with the job

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.