0

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?

1

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.

enter image description here

2
  • Can we use file system deployment in this scenario? – Ankit Mahajan Mar 5 '20 at 11:45
  • 1
    I've never actually tested this, but I think you can make a shared folder to store your packages on your ETL server and give that path on the database server. But you need to check where the package will run in this case because I believe it will be loaded on your database server and that makes your ETL server just a very expensive file share. If you want your package to run on your ETL server + file system deployment then you might need to move the SQL Agent jobs to the ETL Server and use a linked server to access your DB for the sql scripting part. – LadyBug1 Mar 6 '20 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.