I have a busy data warehouse running ETLs once an hour.
I would like to run my data warehouse database server and SSIS on the same server. However I heard that the best practice is to separate Integration Service and database to different servers.
Is this correct? Why? Where can I find the official Microsoft information on the topic?
Thanks in advance
asked Mar 12 at 04:44 PM in Default
Yes, that's what I usually recommend. SSIS uses resources, like memory, outside of SQL Server so it can be difficult to manage both of them on the same box. It really comes down to the hardware you have available and what the work loads you have running on both SSIS and SQL Server.
Given you have a busy ELT process running fairly consistently, I would move it to its own server. This allows you to dedicate resources specifically to the ETL process without impeding on the performance of SQL Server.
Hope that helps!
answered Mar 12 at 07:23 PM
It depends. As always. Make sure to leave plenty of RAM to the operating system when you set max server memory in your SQL Server instance, as SSIS uses mainly memory as the resource for fairly simple dataflow tasks. It seems like you have already monitored CPU usage and found that the ETL process is not eating up all the CPU. In that case, I would test having them on the same box. SQL Server and SSIS does compete about the same resources, especially memory.
Before rebuilding the boxes completely, I would install Integration Services on the database box and run the ETL there for a while, but leaving the current ETL machine in standby to take over if you experience performance issues on the DWH database while doing ETL. So that you can quickly shift back. If you DON'T experience any performance degradation from moving ETL to the DWH box, then you can consider moving permanently and even boosting the performance in the box. In that case, I would start with adding the memory from the ETL box to the DWH box and see if that makes for a performance improvement or not. Adding CPU if you only use up 15% + 5% of the CPU you already have is just a waste of license money. Generally speaking, SQL Server tends to not use too much CPU, but that of course depends on the complexity of the queries you run. Same with SSIS. If you only have fairly simple dataflow tasks, memory is what you need. If you do complex transformations in your dataflow tasks and/or script tasks you might want to add CPU. But keep in mind that when it comes to SQL Server licensing - CPU cores is what you pay for. I've seen way too many SQL Server (dbengine, ssis, ssas etc) installations where there are 32 physical cores and the max CPU consumption is something like 10%, and that's during index maintenance windows when users's aren't using the databases anyway. I'd much rather have seen those license dollars spent on speedy disks and more RAM.
answered Mar 13 at 11:30 AM