Settings and activity

  1. 29 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)

    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    An error occurred while saving the comment
    Aleksandar commented  · 

    AFAIK, maintenance plans just create Agent Jobs. Merging this request with the linked one might make sense
    http://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/471713-agent-jobs?ref=comments#comments

  2. 477 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)
    31 comments  ·  SQL Source Control  ·  Flag idea as inappropriate…  ·  Admin →

    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    under review  ·  Kendra responded

    Thank you everyone for your comments and votes on this over the years. While I don’t have a 100% full resolution for this suggestion, I can sum up our current recommendations here. Continued feedback is very welcome.

    Our current recommendation is to use the post-deployment script feature of SQL Source Control (released in V6.3) to manage SQL Server Agent jobs.

    An example script for this is here: https://documentation.red-gate.com/soc/common-tasks/working-with-pre-post-deployment-scripts/create-sql-server-agent-job

    As some commenters in this thread have alluded to, it is possible (and sometimes very common) for SQL Agent jobs to have steps that touch multiple databases on a single SQL Server Instance. For this reason, some customers prefer to create a separate database for instance-level management and objects (sometimes named DBA or similar) and choose to manage things like linked servers and SQL Agent jobs with the post-script associated with that database.

    This separate-database architecture also makes sense if the jobs…

    An error occurred while saving the comment
    Aleksandar commented  · 

    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
    ON msdb.dbo.sysjobsteps
    AFTER INSERT, UPDATE, DELETE
    AS

    MERGE DBMan.dbo.JOBS
    USING
    (
    Select
    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]
    From
    msdb.dbo.sysjobs
    JOIN msdb.dbo.sysjobsteps
    ON msdb.dbo.sysjobs.job_id=msdb.dbo.sysjobsteps.job_id
    ) AS TEMP

    ON
    ( 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
    )
    THEN
    UPDATE Set
    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
    DELETE

    ;

    After youve done this, Just link the 'JOBS' table as static data and it works well :)

  3. 4 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)
    2 comments  ·  SQL Source Control  ·  Flag idea as inappropriate…  ·  Admin →

    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    An error occurred while saving the comment
    Aleksandar commented  · 

    While not the greatest work around, turning polling off or severely reducing polling frequency will help quite a bit. See here:

    http://www.red-gate.com/MessageBoard/viewtopic.php?t=12837