You can say I've become an "accidental DBA" as people have left this company and I'm managing many aspects of the BI system now suddenly.
My predecessor left quite the mess regardless but I'm trying to think of the best way forward in terms of hardware architecture and logical partitioning.
We have a SQL-Server Data Warehouse. We have ETL scripts (on the same server). We don't have a Test-Production split for database development.
- Should the ETL scripts (SSIS and Pentaho PDI) be located on the same server as the data warehouse?
I understand this is a complicated question. My understanding is this.
Pros for separate ETL server: Contrary to belief, SSIS and SQL Server don't play nicely via memory management and SQL Server plays best by itself. Scalability and optimization generally favor a separate ETL environment as ETL processes have entirely different needs (far fewer backup needs, far less storage needs).
Cons: Well, additional hardware costs. Additional SQL Server license costs in the case of SSIS (an additional one if separated) so for Standard edition it's an extra $900 and for enterprise and additional $13k or whatever. ALSO, our data warehouse is only used during the day (by developers) - ETL is only run overnight - there is little overlap. Only potentially backup processes may interfere. So maybe the 'competition' aspects are overblown and resource-pooling (for more CPU hardware, which our company IS FRUGAL when it comes to hardware) is advantageous.
- Same question but for Test and Production environments.
What exactly is the point of an "instance" separation, as opposed to a database (one lower) or server (one higher) separation?
I guess they have wider configuration options, but -- well even servers can be running on the same physical hardware/ computer. What are the pros of separation Test/ Prod databases via 2 servers or just 2 instances? I take it with 2 instances you're sacrificing configuration for simplicity?