SSIS and SQL Database on the same machine?

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


more ▼

asked Mar 12 at 04:44 PM in Default

avatar image

10 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

I found this link: https://social.msdn.microsoft.com/Forums/en-US/c183134f-33ab-4a24-a2a5-f2892e73c550/running-ssis-on-a-separate-server?forum=sqlintegrationservices

Hope that helps!

more ▼

answered Mar 12 at 07:23 PM

avatar image

14.4k 3 7 15


Currently I have 2 separate machines for SSIS and DWH. My ELT process runs for approximately 15 minutes every hour, with small CPU usage peaks. Average CPU usage in SSIS machine - up to 15% during ELT and up to 5% overall.

For the rest of the hour my SSIS machine remains idle. I though if I move SSIS service to the DWH server, adding the CPU cores and RAM from SSIS machine to the DWH machine, I could improve DWH performance during most of the time, almost without compromising the performance for the 15 minutes of ELT process.

What do you think?

Thanks so much for your help! Alex


Mar 13 at 06:16 AM alex.mendelev
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 13 at 11:30 AM

avatar image

Magnus Ahlkvist
22.5k 20 43 43

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 12 at 04:44 PM

Seen: 25 times

Last Updated: Mar 13 at 11:30 AM

Copyright 2018 Redgate Software. Privacy Policy