Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2010-02-09 : 03:21:04
|
| i have 2 sql 2005 servers on different networks.what is the fastest way to move data from one to the other through queries.I am not doing opendatasource but it seems very slow.what is the best way? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-09 : 04:37:41
|
| have you tried using linked server approach? |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-02-09 : 05:59:45
|
| I couldnt' link the servers as they are on different networks so I had trouble |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 08:30:30
|
| "what is the fastest way to move data from one to the other through queries."I reckon that is going to be slow (if the tables are large).My guess would be:Fastest would be to export the data using BCP's NATIVE formatted files Ordered By the Clustered Index (on the table you are going to import to) and then move the file to the target machine and use BCP to import specifying both the native file format flag and the HINT that the file is in clustered-index order.Second fastest would be to use SQL Server's SSIS tool setting a suitable batch size |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-02-09 : 08:49:00
|
| it's something that i have to do scheduled so say move all records where move=1 so it's not so many but there are records. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 08:55:01
|
| "I couldnt' link the servers as they are on different networks so I had trouble"If you can't find a way to make a linked server then you aren't going to be able to use a query.However, as you are using opendatasource it must be possible to make a linked server. But I don't think it will be any difference in speed to opendatasource.You can schedule either BCP or SSIS, and both can run with a query (i.e. "WHERE move=1") |
|
|
|
|
|
|
move data between 2 sql servers