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?
Best Answer
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.