Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212

    Question Unanswered: Transact Replication Concerns-Please Help!

    Hi guys,

    Need some of ur opinion on some issues on Transactional Replication.

    Here is the scenario:

    We are going to do Transactional Replication between Production DB to
    Report DB.

    Table structure done by vendor. Can't change much.

    - How do I replicate tables that don't have PK?? What is the best "go-around" for this? ( don't even ask me why they didn't have PK in the first place!!!!!)

    -If the Production DB has a schema change( alter objects) will it be reflected in the Report Sever through Transaction Replication? Do I need to run the snapshot again? Will it be safer to manually do schema change on both DBs?


    -Production DB retention period is shorter. Reporting is longer. How do I make it to only do Update,Insert replication but not Delete? Is there some setting in Replication Profile or I have to do it through DTS? Please advice.


    Thanks in advance first!.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  2. #2
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    hello??
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    In this case, I would try to go with Log Shipping for these reasons:

    1) If the vendor gives you an upgrade, you have to reomve all traces of transactional replication before you can apply the updates, in case they try to drop/rename a published table.

    2) Log shipping will pick up all tables (and schema) regardless of PK's. provided you are running at full recovery model.

    Now the downsides:

    1) you need to pony up for enterprise edition of SQL
    2) You can not have different retentions for report vs online systems. Both DBs will be kept rigidly in sync.

    I think in order to keep that last bit about the retention, you will have to come up with some sort of ETL solution on your own. Do the main transactional tables have a last_updated or similar column? The code tables should be static enough. Mapiing tables, however, almost never have a last_updated column. Ugh. This is a bad one...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •