Ed Wagner (10/26/2016)
Eric M Russell (10/26/2016)
Where I've worked in the past, SSIS/SSAS/SSRS is typically installed on a separate box or VM, and sometimes each component warrants it's own dedicated instance. You for sure don't want anything with it's own buffer cache and ad-hoc query engine to be competing for local resources on a high volume OLTP instance.So, be careful and "don't let the camel get it's nose under the tent". It's OK to run the full SSIS/SSAS/SSRS stack on a single Dev or QA instance for economic reasons, but make it clear that this will never be the configuration in production.
Separate servers for each component? Clearly, you have more server budget than I do. 😉
The majority of them are VMs, but yes there is a an Enterprise ETL server that has about 200+ SSIS packages scheduled. Over the past year we've been consolidating ETL processes onto this instance to remove the load from line of business databases. There is another Enterprise Data Warehouse server running those packages that load the data warehouse, and at least one dedicated SSAS server containing some massive cubes. They support a national retailer with 2000+ store locations and an eCommerce website.
However, I can certainly picture a departmental database server, something like HR or billing running the full MSSQL/SSIS/SSAS/SSRS stack on their own instance.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho