SQL Server Performance

Allow Table Drop - Interesting Issue

Discussion in 'SQL Server 2005 Replication' started by Foyura, May 29, 2008.

  1. Foyura New Member

    Hi All:
    I have a client that wants Replication setup (SQL 2000 publisher/subscriber, SQL 2005 Remote Distributor) to replicate data to a reporting server. The challenge is the following:
    a. They have legacy scripts that run that drop specific production tables, re-create the tables, populate the tables with data.
    b. Then they want this data to be replicated to the Subscriber.
    Snapshot replication is setup. Not surprisingly, their script can not drop these tables that have been included in the Publications.
    I need to propose a solution that will allow them to continue with this process (against my recommendation. :)) and still replicate the data. They can't/won't change any of their scripts.

    Given their requirements, here's what I was thinking and I'd like to sanity check it with you guys before I propose it:
    Note: Tables are very small. But there are about 100 of them.
    1. Add a script to run by the Snapshot Agent (nightly) that:
    a. Add these tables to the Publications
    b. Generate Snapshot and bring the Subscriber up-to-date
    c. Remove tables from Publications.

    This is a messy approach. Does anyone else have any thoughts or recommendations? The client recognizes that they should not be dropping and re-creating Production tables. But, they are unwilling to alter their process at this time.
    The other variable is handling the addition of new tables created. However, I am recommending that this fall under their Change Management Process and a request be sent to a DBA for inclusion.
    Alternatively, I was thinking about removing these tables from Replication completely and use DTS/SSIS to accomplish the data sync.
    What are your thoughts?
    Thank you.



  2. ndinakar Member

    I would recommend creating another set of tables and map those tables to the subscriber. When the prod tables are dropped/recreated/repopulated.. I would just truncate the new tables and re-populate them..so the truncate, population scripts are applied at the subscriber..for example lets say yoiu have TablePubA that is being published. I would create another table TableTempA (exactly same structure as TablePubA) and map it to TablePubA on the subscriber instead of mapping TablePubA. Whenever your TablePubA gets dropped/recreated/repopulated I would just add another bunch of scripts to that process to truncate TableTempA and re-populate it from TablePubA. So only the truncate, population scripts get applied at the subscriber..
    hope I was clear..
  3. alent1234 New Member

    can they just delete the data in the tables instead of dropping them? our DW people do this in their processes, but they don't replicate these tables

Share This Page