We're developing new IS packages. None exist on this instance yet.
Is it best to install the IS and RS features on a new server, rather than the database server the jobs will reference? I've always installed these on the db server but I want your recommendations and reasons please. Thanks
depends. we separate things so as not to have all of our eggs in one basket
Performance, space, licensing, perms etc are some considerations
The typical answer - it depends...
I do not install SSIS or SSRS on the same database server that supports and hosts the application databases. Integration Services is normally installed on a dedicated host or a dedicated read-only secondary that cannot be utilized as a failover partner.
SSRS is always installed on its own server - both database engine and reporting services. The only time I would install on separate nodes is if I was building a scale-out solution. Then - I would host the reporting services databases on either a dedicated instance or an application database server that is not part of an AG.
Reasons: Integration Services can connect to multiple sources and destinations. There is no reason to install on the production node(s) and process large imports/exports that can and will interfere with application functionality.
SSRS is designed as a standalone product - and it can also connect to many different sources to pull the data needed for the reports. No reason to allow the reporting engine to impact CPU resources on the application database servers.
Thanks to both of you. @jeffw8713 would the agent jobs (which will run the IS packages and RS reports) run on the database server or on the IS/RS server? I'm looking for the simplest configuration possible that is reasonable.
Agent jobs would run on the SSIS/SSRS servers - not on prod.