2

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.

Image: DTExec.exe

PID: 8864

Address: 192.168.1.55

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.

Image: DTExec.exe

PID: 8864

Address: 192.168.1.101

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?

Robert_DBA
  • 21
  • 1

1 Answers1

1

Your SQL Server does have a different IP address from you SSIS. The clustered SQL will alway get traffic from the "virtual network address", ip=101 in your case, even if it is being hosted on "Node A" and you have a program running on "Node A" , SSIS ip=100 in your case.

That is the way clustering works.

BTW the virtual IP is controled by the Clustering service and is bound to one of the network cards on Node A or B which ever is hosting the clustered SQL Server

RC_Cleland
  • 236
  • 1
  • 3