Moving data between sources
I have a rather unusual situation, and I'm looking for advice on how best to solve it.
I need to transfer data between SQL Servers on remote servers. However, the two remote servers cannot see each other. And, I am forced to connect to each server with a full-tunnel VPN, meaning that I cannot be connected to both at once.
So, what I need to do is connect to the source, pull the data down to my PC, disconnect from the source, connect to the destination, and upload the data.
The data may be as much as 1GB per transaction (a couple hundred records, and each record could be up to 8MB). The schemas are similar, except that the columns have different names. Oh, and to make life difficult, one of the columns is a text field.
I have a few possible solutions. But, I'm really not sure what the relative performance will be. I could create two datasets, and transfer the rows. I'm a little worried about the data remaining persistent while I'm closing connections and changing the VPN over. So, I thought about writing the datasets out to XML files, but I wasn't sure what the performance would be.
Thoughts? Tips? Other solutions?