Source control on SQL jobs?

We run a variety of nightly SQL jobs using the SQL Server Agent.  Can we use RedGate Source Control to track changes on these jobs?  

Currently, as far as I understand, we need to create a repository for each database and link each database to its source control repository (for which we use Redgate on the client side in SSMS and VisualSVN on the server).  Can we use a similar process to link the SQL Server Agent (or the system database this belongs under) to source control?

Best Answer

  • AlessandroAlpiAlessandroAlpi Posts: 91 Gold 2
    edited August 31, 2018 6:58AM Answer ✓
    Hi @jornvango
    SQL source control, as you know, lets you link a single database to the underlying source control manager. Jobs are stored on msdb, but that database is strictly related to the environment where SQL server has been installed on.
    Since jobs are items that could be different in every production environment (at least test and prod) also for configurations like connstrings, I suggest you to create a folder in your SCM and put the generated scripts there.
    We're managing those file by customer (a folder per customer) and we're periodically scripting jobs with dbatools.io (http://dbatools.io) powershell cmdlets, syncing them for each change.

    Hope this helps
    Alessandro Alpi
    CTO @ Engage IT Services s.r.l.
    Database Administrator and Team Leader
    Microsoft MVP - Data Platform
    Staff member of getlatestversion.it community
    personal website | blog

Answers

Sign In or Register to comment.