2

I have an environment that has dozens of SQL Server instances all running SQL agent jobs for their own maintenance as well as other processes (ETL, etc.). I am considering a change to consolidate the non-maintenance jobs to a separate SQL Server instance(s). I'm thinking that having the SQL Agent job processing setup on centralized servers would be easier to manage and coordinate jobs' schedules. I would also plan on using one of these SQL Agent only instances as the distributor for replication purposes.

Besides licensing concerns what downside would there be to move to such a configuration?

  • 5
    I'd be concerned about one server going down causing dozens of ETL processes to grind to a halt. – LowlyDBA Jan 6 '15 at 17:00
  • 1
    PowerShell is probably a more appropriate method to manage multiple SQL Servers/Agents without any changes to the config. You can refer to this post on adding jobs via powershell and this mssqltip about reporting on agent jobs as points of reference. PowerShell is pretty powerful and meant for managing multiple servers, its not just for the OS. – Your_comment_is_not_funny Jan 7 '15 at 19:56
4

A: every job query will require four-part names.

B: that Agent's credentials will require privileges on all instances which is a security risk.

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.