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.
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.
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.
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…
I keep SQL Agent creation scripts in source control. In one place I worked, when you checked a .sql file in it would write VSS tags into the file. I made them write into the @description variable so that my agent jobs had an obvious version number. I haven't found a way to do that in TFS yet. Thing is though, it is still a drop and create process so all the execution history gets lost as is not associated with the new job as the new job gets a new job_id.
I am very interested in getting a better way to manage this.
I keep SQL Agent creation scripts in source control. In one place I worked, when you checked a .sql file in it would write VSS tags into the file. I made them write into the @description variable so that my agent jobs had an obvious version number. I haven't found a way to do that in TFS yet. Thing is though, it is still a drop and create process so all the execution history gets lost as is not associated with the new job as the new job gets a new job_id.
I am very interested in getting a better way to manage this.