I was asked a pretty good question recently on what are the pros and cons of deploying SQL Server Integration Services packages on the same server that has your SQL Engine Server as well. While we covered a lot in Top 10 SQL Server Integration Services Best Practices, we never really addressed this issue specifically – so let’s do it:
Advantages of placing SSIS and SQL on the same server:
- You can use the SQL Server Destination instead of OLE DB which allows you to load data into SQL faster.
- You remove network as a bottleneck for insertion of data by SSIS into SQL
- If you have these newer beefy boxes which has a lot of memory, CPU, and a solid direct attach storage (and separate volumes with their own set of physical disks for each set of processes) it may make more sense to consolidate. After all, if SSIS and SQL individually are using less than half of the available resources, you can consolidate AND you can get faster performance.
Disadvantages of placing SSIS and SQL on the same server:
- If you have a number of SSIS packages that need to run in parallel and/or need to use a lot of memory (especially ones that have a lot of complex transforms), you’d have memory allocation conflicts between SQL and SSIS. SQL will typically win this battle which means SSIS pages to file. Once SSIS pages to file, you will have suboptimal processing by SSIS – i.e. don’t do it!
- The same can be said for CPU – you need to ensure there are enough processors allocated for SSIS and SQL separately. If the two compete, SQL will typically win which will mean SSIS will run much more slowly.
I’m sure many other people can suggest other very good advantages and disadvantages. Saying this, from my experience these are the top advantages / disadvantages for the deployment of SSIS and SQL on the same box.
Enjoy!
Denny Lee 
Since all Microsoft RDBMS products (SQL Server, SSIS, SSRS, etc) are greedy in their design (they assume they own the whole box), unless you cannot afford it, you should always put SSIS on it’s own box. Yes, you do loose the SQL Server Destination object, which is an in-memory copy and the fastest destination, but I think that is small loss relative to the gain of CPU and memory. When you build an SSIS package you shouldn’t be worried about whether or not you will interfere with the SQL Server you are trying to insert into (memory/cpu constraints). Also, having other processes on the same box as SQL Server, be it Reporting Services or SSIS, makes it that much more difficult to troubleshoot when you do have a problem. I prefer segregation of products.
While I do agree from the standpoint of easier maintenance and ensuring that you do not have SSIS and SQL interfering with each other – if you have a standard enterprise commodity box with 64GB RAM and 16 cores, and your systems are not that large, you can better utilize your resources by actually making use of wasted CPU cycles. No doubt its easier to diagnose when you separate the systems, but if it makes economical sense to consolidate – you can consolidate, eh?!
Awesome.. Thank you for this post. It helped me take a decision. I am going with SSIS and SQL Server on their own separate boxes for my application. Those both are hugely resource and memory intensive processes in my case..
We have a SQL Server with 6G of RAM on x64, of which 4G is allocated to SQL Server (i.e. the max memory is set to 4G). The server has only 2 CPUs. Most of our SSIS packages don’t have anything to do with SQL Server (i.e. source/desitination is file based or non-SQL databases). Some of these packages are disk i/o intensive.
Does it makes sense to run these packages on the server that is running SQL Server? Thanks in advance.
Probably not. Based on your above scenario – SSIS isn’t interacting with SQL. If the packages are disk IO intensive – the concern is whether its because of the source read, target write, or is it writing a lot of temporary files b/c its running out of memory. Best to check if the system is running under memory pressure – if so, that would be a potential way to solve this.