We are testing out migrating from sql server 2000 to 2005. I installed the DTS Designer tools for my 2005 installation so I can edit old packages. While testing this out I noticed that running the package from my remote 2005 machine runs faster than running the package locally on the 2000 machine.
The 2000 machine is a pretty weak computer, but the package is transferring data from one table to another in a local db on the 2000 server. This is what puzzles me. Shouldn't the performance be the same when executing the same package from a remote computer? Isn't the remote computer just telling the local computer to run the specified package? The data transfer still takes place on the local computer and should be limited by the power and resources of the local 2000 server. Obviously I'm missing something because the package/DTS runs almost twice as fast when being fired remotely.
The package connections are setup using IPs. Could there be some overhead on the local computer using the IP instead of a named pipe or something?