Source control on SQL jobs?

jornvango

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?

AlessandroAlpi

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

jornvango

Thanks Alessandro.  We will link the 'msdb' database on the Production SQL server to source control and see if we can commit job changes.