Does a tool like "SQL Agent Compare" exist?

Question

Post reply

Does a tool like "SQL Agent Compare" exist?

iainelder

SSC Eights!

Points: 899
More actions
September 6, 2012 at 5:41 pm

#263827

Hi everyone,
Can anyone tell me about a tool that would make it easy to script out, version, and diff the configuration of SQL Agent - like Red Gate SQL Source Control for Agent objects?
In my organization we version our database code in an SVN repository. We rely on Red Gate's SQL Compare and SQL Source Control to script out database changes to our version control system.
Not all of our database code is in a database, however. Some of our servers run lots of agent jobs to perform back-end processing tasks. The Red Gate tools are ignorant of objects managed by SQL Server Agent, such as jobs and operators.
Currently we manage our Agent settings manually. We try at least to script out every job we create and re-script the job when we make a change. We don't always remember to do this.
To attempt to automate the process, I crafted the following PowerShell to script out all the jobs on the local server to the working directory:
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
$server = New-Object Microsoft.SqlServer.Management.Smo.Server;
$server.JobServer.Jobs | % {$_.Script() > "$_.sql"};
This is proof-of-concept only; it would need a lot of work to become robust.
Should I consider putting effort into developing my own tools for this, or does such a product already exist?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 1

I believe all you need to do is a compare on MSDB where all the SQL Agent stuff actually lives.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

iainelder SSC Eights! Points: 899 More actions · Answer 2

Thanks for the suggestion, Jeff. I tried that, but it doesn't behave how I would like.

When I compare the msdb database on two instances with different jobs configured, I see differences, but not in job definitions.

SQL Compare knows only how to compare database-level objects, so the differences it shows me are in the DatabaseMailUserRole, in SQLAgentOperatorRole, SQLAgentReaderRole, and in ##MS_AgentSigningCertificate##. Being able to compare these objects is useful, but I can't use the tool to compare job definitions.

I tried to use Red Gate's SQL Data Compare to compare the row data between two msdb databases, but I couldn't see any of the tables. Maybe the tool automatically ignores system tables.

In any case, I don't want to manage jobs as row data, I want to manage them as job creation scripts with calls to msdb.dbo.sp_add_job and msdb.dbo.add_jobstep.

Maybe the limitation exists because T-SQL has no CREATE JOB statement. Job definitions are stored as row data in system tables. You 'create' jobs by calling msdb procedures like sp_add_job, sp_add_jobstep, and sp_add_jobschedule to insert data into the tables sysjobs, sysjobsteps, and sysjobschedules.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

I have a PowerShell script that does exactly what you're proposing. It's not much more than what you have. I have it explicitly write the IF EXISTS...DROP statement because as you may have learned scripting it using SMO (SSMS uses SMO) uses @job_id when calling sp_delete_job instead of @job_name to drop the job which is not a portable solution :blink:

The GUI is nice for creating or managing jobs but not necessary. However, the GUI becomes quite necessary for managing job schedules due to the cryptic nature of the way dates and times are stored in msdb. Once a job is created or reconfigured we run the PowerShell against the instance to get a new copy of the 'standard job script' and that is checked into source control.

We haven't automated anything because we are not making tons of job changes but it would be a trivial task to run the PowerShell once per night and check for differences in the jobs scripted out the day before. Or even check the changes into into TFS automatically. We seem to be doing fine keeping up with the changes manually using the PowerShell script and checking changes into TFS on our own.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

iainelder SSC Eights! Points: 899 More actions · Answer 4

Thanks, opc.three.

Since no-one has responded with a suggestion for an off-the-shelf solution, I guess there are none.

I'll use my script for now and run it against my server after making any changes to jobs. This way I can review the changes before I commit to version control and provide a meaningful comment.

GSquared SSC Guru Points: 260824 More actions · Answer 5

Have you tried the TableDiff utility? http://msdn.microsoft.com/en-us/library/ms162843.aspx

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

iainelder SSC Eights! Points: 899 More actions · Answer 6

Thanks for the suggestion, GSquared. I didn't know about the tablediff utility.

You can compare the jobs that exist on two servers using a command like like this:

tablediff -strict -f sysjobssync.sql -sourceserver DEV -sourcedatabase msdb -sourceschema dbo -sourcetable sysjobs -destinationserver PROD -destinationdatabase msdb -destinationschema dbo -destinationtable sysjobs

This would generate a script to syncronize the contents of sysjobs on PROD with the contents of sysjobs on DEV. You would have to do this for all the other job tables in msdb to fully deploy the jobs.

Theoretically you could use this to syncronize the job server states between two different instances of msdb, but Microsoft discourages direct modification of the system tables there.

I think that is why SQL Data Compare does not give you the option.

This tool would not help with generating a job creation script that called the recommended stored procedures, so I can't really use this for version control.

I think I will stick with the PowerShell SMO solution. But thanks again!

databoffin SSC Veteran Points: 279 More actions · Answer 7

Apologies for replying to an old post but for the benefit of anyone looking for a tool to compare configuration settings (inc. jobs). You can use the beta version of OmniConfig from Alien??Armpit.

http://www.alienarmpit.rocks/[/url]

Solving data & infrastructure issues via xTEN[/url] and our application, Aireforge[/url].

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

pg 93049 (2/24/2016)
Apologies for replying to an old post but for the benefit of anyone looking for a tool to compare configuration settings (inc. jobs). You can use the beta version of OmniConfig from Alien??Armpit.
http://www.alienarmpit.rocks/[/url]

Agreed. Old but still valuable post.

To add to what everyone has stated, upgrading prod with dev jobs is absolutely insane unless one has done very close scrutiny of the code to look for things like 3 and 4 part naming and anything that may look at external boxes. It would be a real bitch if you suddenly started updating prod with dev data because of copying a job.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)