While migrating SQL Server databases on updated hardware/software we separated the ETL server from the database server (referred this link).
We were moving our SSIS packages and agent jobs on ETL server. But the T-SQL steps in agent jobs need databases to be present on same server as well.
How do we proceed from here, do we create databases on the ETL server OR move SSIS packages only on ETL server and keep agent jobs on database server?
Best Answer
You can trigger a SSIS job from another server, no need to move your jobs.
So you can leave your SQL Agent jobs on the database server, deploy the SSIS packages to the new ETL server and then modify your job steps to start the SSIS packages running on the ETL server from your database server. You might need to modify the 'Run As' part depending on how you set up your permissions in between your 2 servers.