0

We need to use the BCP utility to port database (>1GB) to a remote database server across the internet. We can use the BCP utility to connect to the remote SQL Server, or we can copy over the BCP files across the internet into the "intranet" of the remote SQL Server, and then run the BCP utility from there.

Can someone comment on the feasibility, and performance differences?

Thanks!

ycseattle
  • 165
  • 1
  • 2
  • 5
  • 1
    1 GB is a trivial amount of data if you're only going to do it once. If you're going to do it every day/week/month, then it's worth measuring time taken/bandwidth used for both approaches and picking based on those results. –  Oct 02 '10 at 02:25

1 Answers1

1

It will be slower, but BCP out on the source server, then copying the files to the remote server's file system to BCP in is likely to be the most robust option.

If you try and BCP directly from the source to the target servers over the internet, you leave yourself open to the risk of an interruption to the connection which would stop the copy and leave the target server in an inconsistent state.

Far better to give yourself the chance to validate that the files have copied correctly - you can check MD5 sums and so on - before attempting the BCP in.

Ed Harper
  • 126
  • 2
  • 1
    You might also consider running gzip or bzip2 on the BCP output file before sending it over the Internet. – jftuga Oct 02 '10 at 13:23