I am testing different configurations for loading data from a remote SQL server using SSIS. In a clustered situation SSIS seems slower to speak to the local clustered SQL instance when compared to a non clustered local SQL instance. It looks like any scenario where SSIS speaks to a clustered SQL instance; the communication occurs through the network regardless if the SSIS instance is local or not. Is this true?
In my current scenario, SSIS 2008 R2 is running on a node with clustered SQL Server 2008 R2 instances. It seems to communicate with the clustered instance through some network interface even if they are both on the same Node. Oddly, I cannot identify which interface it is.
Here is the general layout:
Cluster Node 1 physical IP 192.168.1.100
SQL Server "sql2008" instance name has an IP 192.168.1.101
SQL Server Instance is on Node1
Remote SQL server source for SSIS IP 192.168.1.55
The SSIS Load transfer rate seems slow. The SSIS network session to the remote SQL server source appears under the Network section in Resource Monitor.
Send (B/sec): 968
Receive (B/sec): 1,682,022
Total (B/sec): 1,682,990
But the SSIS for loading also appears in network. It seems to run from the local node to to the destination SQL server IP through network communication because DTexec shows up under Resource Monitor - Network as speaking to the Destination SQL server IP.
Receive (B/sec): 120
Send (B/sec): 20,300,868
Unfortunately when I use network monitor to see the traffic, the dtexec traffic from source appears but the load activity does not seem to appear to be using any of the network interfaces and cannot be seen.
When running this same package (against the same unchanged source) with a less powerful non clustered box, the SSIS package runs 2x faster speaking to a local instance and the Dtexec load process does not appear in the Network section in Resource Monitor. Only the Dtexec network session for the source process appears and is the same speed.
Is the missing Network traffic on the non clustered scenario giving me the boost or am I looking at something misleading?
Software/Hardware used: Windows 2008 R2, SQL Server 2008 R2, SQL Server Integration Services 2008 R2, IBM XIV, IBM x3690 X5