Post reply

Replication vs DTS

  • October 9, 2002 at 9:06 am

    #80031

    Hello all,

    SETUP:

    (A) AS400 running DB2

    (B) Win2000 Advanced Server SP1 running SQL2000 SP2

    (C) Win2000 Advanced Server SP1 running SQL2000 SP2 - Datawarehouse

    We currently have DataMirror's Transformation Server mirroring data from the AS400 (A) in to our datawarehouse (C). There are also a number of DTS steps transferring data from (B) in to (C). These steps involve a lot of transformation and manipulation.

    Note that the flow of data from (A) to (C) is sacred and making any change that affects its performance will result in [my] death.

    To my question....

    I have been asked to replace the DTS job that transfer data between (B) and (C) with SQL Replication.

    First of all, I am trying to find out why/if this is a good idea. What sort of benefit will we see by doing this? My preliminary investigation suggests that DTS gets worse as the amount of data being transferred increases. Is this the general opinion?

    Secondly, can anybody think how SQL Replication may affect Transformation Server's replication jobs?

    Thirdly, what sort of replication would be best for me? Although data will only ever flow from publisher to subscriber, Merge Replication allows more data manipulation tools to be used - specifically the JOIN clause??? Our existing DTS jobs do a lot of mucking about with the data so I will need all the tools available to me. Does this mean Transactional Replication is out?

    Thanks in advance for all you help.

    Jonathan

  • October 15, 2002 at 8:14 am

    #438568

    Replication is usually set to update changes more frequently than DTS and will comence every time a change is made if that is how you set it up.

    However, I believe you can manipulate the data more using DTS and some manipulations I don't think can be done using replication.

    You can design a DTS package to only move changed data rather than all the data and so if the issue is that it takes to long then this would reduce the time it takes to move changes and you could run the DTS more often.

    Before making the change, I would make sure that replication can handle all the data manipulations the DTS package currently does. If it can't, then you probably will have to stick with the DTS package.

    Also there are techniques that can keep a DTS package running at a good speed.

    Robert W. Marda

    SQL Programmer

    bigdough.com

    The world’s leading capital markets contact database and software platform.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply