What is the fastest way to transfer data from tables (views) between two SQL Servers ? By using linked servers - distributed transactions, SSIS packages, replications or something else ?
The data will be transfered every night after scheduled job starts or by user request which should trigger some job or stored procedure or SSIS package. The data transfer should start only when it is scheduled or when user requests for it which could happen in the case of failed scheduled transfer. So, there is no special reason to use replications. The data validation is designed and it starts after tranfer is over and compare count and check sum on two sites tables. The transfers are between different continents, so it's possible to have slow or failed network links sometimes.
Also, I would appreciate if you can paste a web link where I can read some smart compares, analysis or documentation about all types of SQlL Server 2000/2005 data transfers ?
Thank you !