SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Does a tool like "SQL Agent Compare" exist?


Does a tool like "SQL Agent Compare" exist?

Author
Message
iainelder
iainelder
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 210
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219504 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
iainelder
iainelder
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 210
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
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40792 Visits: 14413
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
iainelder
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 210
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
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59101 Visits: 9730
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
iainelder
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 210
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!
Phil Grayson
Phil Grayson
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 34
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/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219504 Visits: 42002
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/


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search