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

Sorry, inserts are on average of .1 seconds....

<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: 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: Errors in Directory Service Event Viewer
    ... >condition persists then please restore the database from a previous backup. ... >following directory partition. ... >Sites and Services for the KCC to create a spanning tree replication ... >- Add a Connection object to a domain controller that contains the directory ...
    (microsoft.public.windows.server.active_directory)
  • Errors in Directory Service Event Viewer
    ... Event Category: Database Page Cache ... following directory partition. ... Sites and Services for the KCC to create a spanning tree replication ... - Add a Connection object to a domain controller that contains the directory ...
    (microsoft.public.windows.server.active_directory)
  • Re: Alternate to SQL Replication
    ... Log shipping - whether manual or through the wizard won't really do it for ... The same would apply to shipping the database backups. ... Transactional replication is out because you don't have PKs, ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.dts)
Loading