Does a tool like "SQL Agent Compare" exist?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Does a tool like "SQL Agent Compare" exist?

Does a tool like "SQL Agent Compare" exist?

Rate Topic

Display Mode

Topic Options

Author

Message

iainelder

Posted Thursday, September 06, 2012 5:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:36 AM
Points: 4, Visits: 112

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?

Post #1355670

Jeff Moden

Posted Thursday, September 06, 2012 7:32 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 32,910, Visits: 26,801

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1355687

iainelder

Posted Friday, September 07, 2012 4:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:36 AM
Points: 4, Visits: 112

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.

Post #1355891

opc.three

Posted Friday, September 07, 2012 11:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 6,730, Visits: 11,778

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1356150

iainelder

Posted Monday, September 10, 2012 11:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:36 AM
Points: 4, Visits: 112

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.

Post #1356904

GSquared

Posted Monday, September 10, 2012 11:56 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #1356933

iainelder

Posted Friday, September 14, 2012 9:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:36 AM
Points: 4, Visits: 112

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!

Post #1359448

« Prev Topic | Next Topic »

Permissions