Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Does a tool like "SQL Agent Compare" exist? Expand / Collapse
Author
Message
Posted Thursday, September 6, 2012 5:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:49 PM
Points: 11, Visits: 191
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
Posted Thursday, September 6, 2012 7:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1355687
Posted Friday, September 7, 2012 4:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:49 PM
Points: 11, Visits: 191
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
Posted Friday, September 7, 2012 11:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 7,081, Visits: 12,574
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

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
Post #1356150
Posted Monday, September 10, 2012 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:49 PM
Points: 11, Visits: 191
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
Posted Monday, September 10, 2012 11:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Friday, September 14, 2012 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:49 PM
Points: 11, Visits: 191
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 »

Add to briefcase

Permissions Expand / Collapse