I read the article about the need for source control for SQL Agent jobs, and I fully agree. At my current shop, we're using SQL Server Data Tools and SQL Projects in Visual Studio to check in our database objects (tables, indexes, procs, etc.) and our source control choice is Team Foundation Server... however we have yet to find a good strategy for source control for Agent jobs. While we could always just script them out to files via SQL Server Management Studio and then check those in to TFS, we've held off for now: we're hoping to find a better way. Source control alone would be good, but maintenance and deployment problems include:
1) SSDT's import tool won't pull current definitions of agent jobs...
2) ...and even if it did, the Job Id's themselves change from environment to environment (we have a few development and test environments on the road to Production)...
3) ...which would be confusing unless we manually edited the scripts to be "environment aware" (some kind of IF...ELSE block using @@SERVERNAME, for instance), or pulled the "full stack" (Dev all the way to Prod) and source control all jobs, from each deployment environment.
4) And lastly, there is not any automatic deployment option (via SQL Project Publish / SQLPACKAGE.EXE) for these objects (to our knowledge). We'd have to write something custom in PowerShell and then stitch that into our deployment scripts.
So, I would like to know: for those of you out in the community placing SQL Agent jobs under source control, what's your environment/method? Do you just store source, or do you have a solution for upkeep and deployment?