I have a SQL Server 2008 Enterprise Edition database running on Windows Server 2008 R2 in a major US city that is working fine when data is loaded from a server in the same city.
I have an identical server setup in India but the data from that server is loading into the database very slowly.
The connection to India is 40mb and about half utilized.
Currently we are using odbc connections at both locations but I am considering changing the India site to use bcp.
How can I tell what is causing the slow connection without just assuming it is the WAN connection?
Also will the database care that one server is using odbc and the other is using bcp?
I checked the SQL logs and there is not any obvious errors to point to anything wrong with the connection. The connection will not keep up with the amount of data that India is generating. The amount of data coming from India is much less than the server in the US is is sending to the same database.
I am thinking it is not the database because the database is processing the data from the local server quickly.
The server in India does not show that it is overloaded. CPU, memory usage is low.
Will bcp make a big difference?
Is SQL profiler the tool to use?