asked
How do we sync Jobs in AlwaysOn Availability Groups
I have two servers, Server A and Server B. I have linked server between Server A and Server B. I have SAME JOBS on Server A and Server B. I need TSQL to get below information in 1 table 1. Schedule and name of those jobs which are not same on both Servers 2. Names of those jobs which are not same on both Servers 3. Names of disabled jobs on Server A, only if the same job is not disabled on Server B 4. Names of disabled jobs on Server B , but are active on Server A Can anyone help me please? In short for AlwaysOn Availability Groups I am trying to keep jobs in sync on both primary and secondary
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
Hi there are a number of good resources out there SQLMag has a series on AG sync [here][1] [SQLSkills][2] also have a SSMS add-on to generate a script that will allow a DBA to review and execute. In terms of enabling/disabling jobs, in the past I have taken the approach to add step to all jobs to check if the current server is the PRIMARY (or SECONDARY depending on your requirements), if not then jump out and report success. Another approach would be to have a centralised job server (resilience would be an issues if you only had one) and all the connection strings/configs point to the listener and let SQL do the rest. Hope this helps [1]:
http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-1-introduction [2]:
https://www.sqlskills.com/blogs/jonathan/synchronize-availability-group-logins-and-jobs/
Thanks for your help. I just need tSQL which show me the differences on jobs between 2 servers and sends me an alert, so that I can manually change it . I have a job step which checks for Primary on all my jobs setup already. but at times we forget to deploy same job on secondary, so I need to find out the differences between jobs on primary and secondary .
Thanks for responding to my reply 1. job might exist on primary but not on secondary 2. I might have change schedule on primary but forget to change the schedule on secondary. 3. Job might have been disabled on primary but forgot to disable on secondary 4. I might have added a new step to a job on Primary but forgot to add that on secondary, so need to check no of job steps for each job, if they match then it is fine, if not I need an alert So the difference is at job level, but to identify change in schedule that is at step level if I am not wrong I need to set up an alert which does all above checks and send me 1 email, so that I can manually sync them. Please help me in doing this if you can. Thanks in advance
I like the idea of the SQLSkills add-on. If it generates the job scripts, it sounds like it's just what you need. If you want to code something up yourself with the same functionality that compare jobs across servers, a couple options come right to mind. 1. You can put together an SSIS package that queries the appropriate details out of the msdb sysjob* tables, compares everything then sends you an email with the results. 2. Programmatically via SMO - [
https://www.mssqltips.com/sqlservertip/3675/using-smo-with-powershell-to-obtain-sql-server-job-information/][1] 3. You can query the sysjob tables from both of the servers using something like openrowset using a SQL 'except' operator ([
https://msdn.microsoft.com/en-us/library/ms188055.aspx][2]) to identify jobs that have differences in job details, step details or schedule details. Email yourself this result set. For option 3, you'll probably want to query the following tables, leaving out anything with a GUID type, such as job_id, or that references historical job executions since those will be different between servers: select * from msdb.dbo.sysjobs as sj inner join msdb.dbo.sysjobsteps as sjs on sj.job_id = sjs.job_id inner join msdb.dbo.sysjobschedules as sjc on sj.job_id = sjc.job_id Hopefully this helps to get you started. [1]:
https://www.mssqltips.com/sqlservertip/3675/using-smo-with-powershell-to-obtain-sql-server-job-information/ [2]:
https://msdn.microsoft.com/en-us/library/ms188055.aspx
You might add this to the WHERE clause so you only return jobs with differing states: AND sj.[enabled] ssj.[enabled] That should limit your results to job "B" with a result like PrimaryJobName primaryJobdisabled SecondaryJobName SecondaryJobdisabled B 1 B 0 If you really want to replace the 1 and 0 with natural language, consider a case statement: case when primaryJobDisabled = 0 then 'disabled' else 'enabled' end + ' on Primary'
Thanks Kenj for your help. I have managed to get the status of jobs which are disabled on primary and might be enabled on secondary Jobs might be enabled on Primary but might be disabled on secondary, but struggling with TSQL SELECT sj.[name] AS [Primary Job Name], sj.[enabled] AS [primary Job disabled], ssj.name [Secondary Job Name], ssj.[enabled] AS [Secondary Job disabled] FROM msdb.dbo.sysjobs sj FULL OUTER JOIN [aps01sql06\apsins01].msdb.dbo.sysjobs ssj ON sj.name =ssj.name WHERE sj.[enabled]<>1 OR ssj.[enabled] <>1 when i run above query i am getting ouput like below PrimaryJobName primaryJobdisabled SecondaryJobName SecondaryJobdisabled A 0 A 0 B 1 B 0 C 0 C 0 D 0 D 0 E 0 E 0 But i need output like this PrimaryJobName primaryJobdisabled SecondaryJobName SecondaryJobdisabled B enabled on Primary B disabled on secondary Can some one help me please
18 People are following this question.
