Home / server / Questions / 186858
Accepted
ycseattle
Asked: 2010-10-02 17:04:44 +0800 CST

SQL Server BCP performance: over the wire, or copy to intranet first

  • 772

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!

sql-server bcp

1 Answers

  1. Best Answer

    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.

    • 1