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

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: Replacing Replication with DTS
    ... DTS is very reliable. ... I would use transactional replication for this as it is far less intrusive. ... Director of Text Mining and Database Strategy ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Setting Table dynamically
    ... As long as the ActiveX Script task runs before the Data Pump task you will ... You can use Disconnected Edit (right-click in the DTS Designer ... In the source database, ...
    (microsoft.public.sqlserver.dts)
  • Re: Setting Table dynamically
    ... As long as the ActiveX Script task runs before the Data Pump task you will ... You can use Disconnected Edit (right-click in the DTS Designer ... In the source database, ...
    (microsoft.public.sqlserver.programming)
  • Re: Setting Table dynamically
    ... As long as the ActiveX Script task runs before the Data Pump task you will ... You can use Disconnected Edit (right-click in the DTS Designer ... In the source database, ...
    (microsoft.public.sqlserver)
  • Why I want do do this ...
    ... I suppose I could use DTS and make a transformable subscription. ... for each table in the consolidated database that was smart enough to figure ... Then I have only about 300 procs involved in replication no matter how many ...
    (microsoft.public.sqlserver.replication)
Loading