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.