Re: Can I pick your brains for a second? transferring a large database problem

VERY awesome. I'm learning a lot from the article. But now I have this issue...

It's taking .5 (almost) seconds to insert each record and each record is in it's own transaction (Wouldn't that increase overhead?). The table is 243 columns wide and I'm loading it all through an ActiveX script. Any other suggestions?

Hey, At least I can insert data!!!!! The Update doesn't seem to want to run in tandem yet :(

<Myles.Matheson@xxxxxxxxx> wrote in message news:1118180018.558156.160730@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 
Hello Scott,

I think you have the best idea using the check sum. For performance I
would look at creating a stored proc which DTS can call from with in
the Data pump task in your source database.

Have you considered deletes as well? This is easy to do against your
audit table just do a left outer join against your source tables to see
if the keys exist.

I would move away from the cursor as you don't need it. You can do
all you comparisons in a Select statement.

I would also look at creating three DTS data pump tasks.

1. New Records
2. Updated Records
3. Deleted Records.

I use Binary checksum all the time. It works well. There is a good
article on using this as a practice see:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_busintbpwithdts.asp

Look under the section titled 'Data Transformation and Cleansing
Approach'


For everyone else I have noted some points have a look at the
following.

1. Triggers would be to slow against large inserts into the source
tables. Great for small amounts of data, but remember a trigger will
create an over head on you OLTP system.
2. Replication, although on paper this looks like a great idea again
you have an over head and new Services have to be enabled if you are
not doing replication already. Also I believe (not 100%) SQL server
2000 Replication does not support DDL changes. Still Replication would
be a great solution if the transactions are of a reasonable size.
3. One other option to look at if the tables where not being drop is
log shipping. Shipping the transaction log and processing the changes
against a database on another server is a great way of mirroring the
database.


Hope this helps

Myles Matheson
Data Warehouse Architect


.

Relevant Pages

  • Re: Distributed transactions, replication, views
    ... If you have significant processors on your server you can partition the ... The way replication works is that if a transaction is committed on the ... Looking for a SQL Server replication book? ... I have been tasked with restructuring a database. ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication vs log shipping
    ... Log Shipping ... replication target. ... Testing of target site. ... These components necessarily modify the database. ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication vs log shipping
    ... > replication target. ... Are you saying log shipping does not work ... > database is ready, but to verify that the application components work ... For instance a large book seller has designed their replication ...
    (microsoft.public.sqlserver.replication)
  • Re: transactional replication problem
    ... All transaction from killed process were rolled back... ... Did they get deleted from distribution database ... >Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication solution?
    ... Replication is your solution for distributed transaction processing...but ... some cases replication makes changes to the schema. ... identical SQL Server in the Ottawa would make it faster for the Ottawa ... I don't think log shipping would do us any good since we need two active ...
    (microsoft.public.sqlserver.replication)
Loading