Would be great if SQL Server Agent jobs could be incorporated into SVN
This would bevery useful, jobs (searchable would be great) in TFS.
It's currently a real pain keep track of all that once you accumulate any number of production jobs.
Phil Helmer commented
We do the same as what Pete Cousins described. It works pretty well for us in terms of saving the job, but it doesn't help us with deployment. It would seem that controlling the jobs more tightly to the databases in SVN would make it more likely that the tools (compare or deployment manager) could deploy those more easily than a series of manual script executions.
Jay Ouzts commented
I would also like to see jobs incorporated into TFS
Yes.. a great new feature .. but for SVN and TFS :)
Pete Cousins commented
This can already be done. Script the SQL Agent Job from SSMS (amend script so it drops existing by name, and not job_id), save the file, and add it into to SVN. I have a seperate folder under Trunk called SQLAgentJobs.
We also use VisualSVN (http://www.visualsvn.com/) to version control SSIS packages & SSRS reports using SVN too.
Lakeland's idea makes sense. much easier to keep track of changes that way.
If you wanted to keep jobs like they are though, theres a few options. I wrote the idea of putting MSDB into source control before i tried it, only to find that i cant actually do it.
Im quite new to sql so am constantly learning about useful things... like triggers! As such ive created a trigger that automatically updates a table when a change is made to a job :)
Hopefully this turns out okay on this form:
CREATE TRIGGER AFTER_JOBS_CHANGE
AFTER INSERT, UPDATE, DELETE
msdb.dbo.sysjobs.name AS [job_name],
msdb.dbo.sysjobsteps.step_id AS [step_no],
msdb.dbo.sysjobsteps.step_name AS [step_name],
msdb.dbo.sysjobsteps.command AS [step_details],
msdb.dbo.sysjobsteps.step_uid AS [step_uid]
) AS TEMP
( JOBS.STEP_UID = TEMP.step_uid
WHEN MATCHED AND -- If it exists AND has changes
JOBS.JOB_NAME <> TEMP.job_name OR
JOBS.STEP_NO <> TEMP.step_no OR
JOBS.STEP_NAME <> TEMP.step_name OR
JOBS.STEP_DETAILS <> TEMP.step_details
JOBS.JOB_NAME = TEMP.job_name,
JOBS.STEP_NO = TEMP.step_no,
JOBS.STEP_NAME = TEMP.step_name,
JOBS.STEP_DETAILS = TEMP.step_details,
JOBS.STEP_UID = TEMP.step_uid
WHEN NOT MATCHED AND TEMP.step_uid IS NOT NULL THEN -- New files
INSERT (JOB_NAME, STEP_NO, STEP_NAME, STEP_DETAILS, STEP_UID)
VALUES (TEMP.job_name, TEMP.step_no,TEMP.step_name,TEMP.step_details,TEMP.step_uid)
WHEN NOT MATCHED BY SOURCE THEN
After youve done this, Just link the 'JOBS' table as static data and it works well :)
I currently work around this by making my jobs short and trivial - all calling a SP which is under source control. e.g. sp_daily_load. That way the only bits I miss out on are things like scheduling and notifications but I get the upside of tightly tying the version of the job to the version of the database.
Leslie P commented
I like your idea Aleksandar. Looks like SQL Source Control can't be used to put system tables under static data control. Boo.
I know this might be a bit late for some, but a workaround for this would be to to put the msdb database in source control and filter out everything other than the table below:
It stores all the steps for all jobs, any updates to jobs will update this table. only issue is that you'd have to get people to remember to commit the change when they change a job.
Might even be useful for the redgate team to use this table and integrate jobs into version control... ;)
I think in this tool is a must to have this control.
Versioning jobs would definetely be an awesome feature.
Alexandre Corrêa commented
Very useful feature!
Barry H commented
A good idea - TFS
Rui Ferreira commented
would be a very nice and useful feature!
John H commented
Yes, please. SVN.
Daniele Barlocco commented
It would be greater if you could control in TFS not only the jobs, but also the DTSs and the maintenance plans.
Leslie P commented
I would like SQL Server jobs controlled under TFS. Ideally integrated into SSMS.
Would definitely like to see this feature in a future version.
and TFS, too please!
Bret Lowery commented