MS SQL Server - Bulk Insert Across a Network

Question

I have an application that uses MS SQL Server for which I'll need to do a bulk insert from a file. The sticking point is that the database and my application will be hosted on separate servers. What is the best way to do a bulk insert across a network? Two ideas I'd come up with so far:

1. From the app server, share a directory that the db server can find, and do the import using a bulk insert statement from the remote file

2. Run an FTP server from the db server - when the import is performed, simply ftp the file to the db server and do the import using a bulk insert from the local file (I am leaning towards this option).

Can anyone else tell me if there is a better way to do this, or if not, which one makes the most sense, and why?

Answer 1

If the file is small enough then the ftp option may work (you will have a duplicate in your db box). However I don't see much of a problem to do option 1) if you have a gigabit network between the two with a single hop.

Answer 2

I've done it before, and tried both options.

In the end, I did the opposite of choice 1. Share a directory on the DB server that the app can find. You don't have to deal with bandwidth issues during the bulk insert.

The FTP server option works if you're particularly concerned with security or transferability.

A final option (be very careful) is to use DTS with a localized SQL server. It might be more secure. If you do it wrong, it'll be much less efficient.

Answer 3

I am not sure about 2k08 as there are some additional utilities to copy files between servers. FTP will be the faster of the two, because it doesn't use the window's file system to transfer the file. (there is overhead, but unless the file is large it might be negligible). There are other advantages to not using a share, such as the remote server with the file crashing mid-access.

I disagree with cmartin about adding an open share to the db server. Generally you don't want to open file shares to the db server as it is generally considered a security risk, and a lot of places won't allow it. That being said it would negate you having transfer the file to another location to use the bulk import.

Answer 4

Why did you put 2k08? Was it really that hard to write 2008? It's the same amount of characters. Are we really that stupid or are people too lazy to spell correctly? I guess with texting this is the future of spelling things out.