MS SQL Server - Bulk Insert Across a Network
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:
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
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
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.