|
Home > Archive > MS SQL Server > October 2005 > Fastest method to move data between two remote SQL servers?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Fastest method to move data between two remote SQL servers?
|
|
| Mnemonic 2005-10-12, 11:23 am |
| Need to move approximately 2-5 GB of data between two servers located
in different states but on same WAN. What is the fastest method with
least intrusion on daily updates to file? (SQL Server 2000)
1. "Copy Server Object" task in DTS (any special options to enable?)
2. Use ODBC/Transformation within DTS.
3. Replication?
4. DROP / Select into via linked server?
5. Other options?
| |
| Mnemonic 2005-10-12, 11:23 am |
| P.S. I'm testing this out, but it appears step 1 and 2 are taking 6-8
hours each, so any advance help would be appreciated...
| |
| Nik Marshall-Blank 2005-10-12, 11:23 am |
| 2 options for a one-off
1 - backup and copy/restore on target server
2 - detach/copy/attach again on both servers.
But if you want to do this regularly then maybe 1 with jobs running scripts.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Mnemonic" <sunlinmj@hotmail.com> wrote in message
news:1129059378.818147.103050@f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>
| |
| Danny 2005-10-12, 11:23 am |
| I'm assuming the SQL Servers are reasonably fast, the bottle neck is the WAN
pipe, and the only scheduler available is SQL Agent.
Create a linked server from the source to the destination
On the source server have a job that does the following steps
Write a DTS package to export the data to a file
xp_cmdshell or operating system command within a job step: use gzip or
some other compression software to do reasonable compression. Gzip has
different compression levels.
xp_cmdshell or operating system command within a job step: copy the file
to the destination server (FTP is a little faster but less managable)
Use the linked server to start a job on the destination server or just
call a stored procedure
On the destination server, the job or stored procedure should
xp_cmdshell or operating system command within a job step: decompress the
file
bcp, bulk insert, or DTS (bulk loader) into SQL Server.
"Mnemonic" <sunlinmj@hotmail.com> wrote in message
news:1129059378.818147.103050@f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>
| |
| Mnemonic 2005-10-12, 11:23 am |
| Interesting! It'll take a bit to test and coordinate schedule times,
but I'll give it a shot. I believe you are correct on all your
assumptions too, althought I also suspect a little Disk IO competition.
Thanks!
FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
Option 1 did run during the day, when Option 2 had nighttime/early
morning.
| |
|
| This is the time I usually get on my soap box about an enterprise batch
scheduler and preach to management.
If you have your source server start a job on the destination server after
the copy then you shouldn't have to worry about schedule times between the
servers.
Good luck.
"Mnemonic" <sunlinmj@hotmail.com> wrote in message
news:1129132709.159007.124640@g49g2000cwa.googlegroups.com...
> Interesting! It'll take a bit to test and coordinate schedule times,
> but I'll give it a shot. I believe you are correct on all your
> assumptions too, althought I also suspect a little Disk IO competition.
>
>
> Thanks!
>
>
> FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
> Option 1 did run during the day, when Option 2 had nighttime/early
> morning.
>
|
|
|
|
|