Re: Can I pick your brains for a second? transferring a large database problem
- From: "Jéjé" <willgart@xxxxxxxxxxxxxxxxx>
- Date: Sun, 19 Jun 2005 10:05:34 -0400
have you try to create a local copy, then zip it, send the file by FTP,
unzip and load the data?
For example, you export to CSV files the new data from the source server.
A CSV file can be loaded in a database very quickly using bulk insert
commands.
Or you could detach a database, zip it, copy, reattach on the target server.
Or do the same with a backup file.
I have a demo database which is a 5gb of data, when I compress it, the size
is only 150mb and the time to transfert is excellent. (10min to compress,
10min to transfert, 5 to expand; instead-of 2h of transferts)
"Scott M" <scott_M@xxxxxxxxxxxxx> wrote in message
news:ui2eBfuaFHA.584@xxxxxxxxxxxxxxxxxxxxxxx
> -=-=- Sorry for the dup post, I'm posting it under my MSDN universal acct
> now :) -=-=-
>
> Okay, I have a problem that I need some suggestions with.
>
> I have a 30 Gig database that I need to sync across a T1 every night.
> This
> database is a data dump of our billing system data, the tables are dropped
> ad re-created every night.
>
> I can do a very long and laborious process on the server of creating
> tables
> with the primary keys from each table and a binary checksum of a row, then
> complaining it to a copy of the database from the day before. Then I
> generate SQL statements for the updates (Actually deletes and inserts).
> All
> of this on the source server.
>
> I'd like to compact this down using a DTS package on the destination side,
> however I can't use a dynamic query in the "openRowSet" command to get
> only
> the row that I want.
>
> Here is what I'm doing (Just for one table, I'll have data driven steps
> for
> each table in my DB)
>
> insert into @tempChecker
> select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
> chgno from charge_t')
>
> Declare Charge_t_Cursor Cursor
> FOR
> select
> a.chgno
> from
> (select
> BSum,
> chgno
> from @tempChecker
> ) a
> where
> not exists
> (
> select 'x'
> from charge_t b
> where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> )
>
> Once I get all the chgno's (The key in the table) I want to only pull back
> the data that has changes. So I have this (Which I know I can't do put
> you
> get the idea)
>
> Open Charge_t_Cursor
>
> Fetch next from Charge_t_cursor into @ChgNo
>
> While @@FETCH_STATUS = 0
> BEGIN
> insert into @ChargeT select *
> from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> cast(isnull(@ChgNo, 0) as varchar)))
>
> Fetch next from Charge_t_cursor into @ChgNo
> END
>
> Close Charge_t_cursor
> deallocate Charge_t_cursor
>
> select * from @ChargeT
>
> this way I'm only pulling over the wire the data that is updated and it
> will
> be in a nice, compact binary format. ANY help would be VERY
> appreciated!!!
> Thanks much!
>
> Scott
>
.
- References:
- Prev by Date: msmdsrv.exe takes more than 95% cpu
- Next by Date: Re: ADOMD
- Previous by thread: Re: Can I pick your brains for a second? transferring a large database problem
- Next by thread: Urgen: Visio vs SQL Server
- Index(es):
Relevant Pages
|
Loading
