SQL Agent jobs to alternately target TST or PRD environments RRS feed

  • Question

  • (Best forum for this?...)

    Server #1 is PRD SQL 2014 instance running Always On AG Listener.

    Server #2 is TST SQL 2014 instance running Always On AG Listener.

    Server #3 is a standalone SQL 2017 instance where Dev Team want to create and run SQL Agent jobs against either remote TST or remote PRD environment as needed.

    Agent jobs on job server (#3) may run T-SQL, SSIS, or OS job steps. There are over 100 jobs. There is currently only one set of jobs, not one set to run against TST environment and another set to run against PRD environment.

    The Dev Team had the idea to use a DNS alias to alternately point each Agent job (and also their web apps) to either the TST AlwaysOn listener or the PRD AlwaysOn listener.   That way they don't have to crack open each job to manage the target server name.  Just have the DNS admins flip the DNS alias between TST and PRD as needed. Here is an example of a CmdExec job step which calls a stored proc on a remote server:

        SQLCMD -S < -d MyDB -Q "EXEC dbo.usp_MyProc"

    I don't agree with the Dev Team approach using DNS alias to switch environments.  When DNS admins flip the alias between TST and PRD, aLL the jobs that refer to that alias will follow it, as opposed to ability to point individual jobs to appropriate environment.

    Is there a best practice approach to point Agent jobs to different environments?  My idea was to suggest to the Dev Team that they maintain two SQL instances on their job server - one for PRD jobs and one for TST.  In this case, what is the best way  to manage/promote the jobs between instances from a source control perspective?  is it simply to script it and copy it between the instances on their server?  Thanks.

    • Edited by District9 Wednesday, October 16, 2019 4:25 PM
    Wednesday, October 16, 2019 4:16 PM


All replies