Link to home
Start Free TrialLog in
Avatar of Dragon0x40
Dragon0x40

asked on

Data ODBC connection slow to load into SQL database

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?

Thanks
SOLUTION
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dragon0x40
Dragon0x40

ASKER

I agree that Latency could be a problem but pingtest.net is the way to test your ISP speed to one of the pingtest.net servers.

I don't think you can test a WAN link between the US and India using pingtest.net?

http://www.serviceassurancedaily.com/2008/06/latency-and-jitter/
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Maybe I can run wireshark on the SQL server and filter for traffic coming from the US server and compare that to a filter of the traffic coming from the India server?

Then I should be able to look for retransmissions, the amount of time between packets, errors, etc.

Of course any latency could be in the WAN link or the Server in India.

Maybe SQL profiler would be a better tool but I don't know how to use it well enough to know what to capture and what to look for.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Still working on this.

Will post something when I find out any new information.
Still working on this.
never figured this out so I will assign points for those that helped