Should buffering and data storage be recommended in the same instance of the server?
1) OLTP Source —> 2) StagingDB -> 3) Data Warehouse
I understand that the OLTP source and the data store must be on different servers.
Reports / analyzes, long-running queries, can interfere with the memory / CPU of highly critical OLTP operations.
However, I always felt that data storage and storage operations are sister / premium databases. We load all the OLTP databases into Staging, and then DW.
This is causing a discussion, forcing developers to create SSIS ETL packages, which can be slower and more difficult to encode complex queries with SSIS instead of online T-SQL. (all with the purpose of being able to transfer the staging to another Datawarehouse Server).
I understand that this question can be closed simply for "reason of opinion base", etc. However, I would like to understand the benefits / negative aspects of each aspect, without a complete answer, instead of just closing. This question can affect many companies, analyzing the same problem.