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

    Unanswered: Replication Maintanance Issues!

    Hi Guys/Gals,

    Need some comments/ideas on the following scenario:


    Current Situation:
    -I have a PROD DB and a REPORTING DB( Separte Physical Servers).
    -PROD DB replicates (Transactional) to REPORTING DB. ( Using only Replication Wizard to set up, quite easy)
    -DB is for running shopfloor(manufacturing) applications, thus needs to be minimum downtime.
    -Retention period for PROD DB data is 3 months ,Reporting DB is 3 years.



    Future Problem that I will encounter:
    -Purging of data in PROD DB will be replicated into Reporting.
    -Schema change in PROD DB need to delete existing Publication before able to change schema.
    -After schema change, need to recreate publication and rerun snapshot.


    Questions:
    -How to prevent the purging to be replicated to Reporting?
    Switching off Replication when Purging takes place will not help. The only solution
    I am thinking of is to alter the store procedure for replication during purging.
    Is there any other "CLEANER" hassle free way?


    - How to cater shema changes better?
    Currently the data is not alot, but down the road, it might go to Terabytes, by then running a
    snapshot will cost us alot. Is there any way not to redo a snapshot for this scenario?


    Please give your comments/ideas/ .

    Thanks.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  2. #2
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    ---> another thing I miss out, doing a snapshot again would clear any data
    Reporting DB has stored. e.g. Prod DB( 3 months data) , Reporting DB(1 year data), do snapshot.....all gone.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

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

  4. #4
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    Patrick, have you thought about replication without snapshot? That will minimize the impact of snapshoting on publications.

    Changing replication stored procs sounds the best way to avoid data changes on the subscribers. But it could go wild if you have a lot of sprocs to modify.

  5. #5
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    replication without snapshot..hmmm....I'll check it out...
    no idea as of how not to have a snapshot for now.....can u give some highlights....


    Anybody else? I'm sure other ppl would have the same senario as me.....
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  6. #6
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    there was a thread a few days back that was talking about transactional replication without snapshot. check it out:
    http://www.dbforums.com/showthread.p...93#post3665793

    SQL Magazine also has an article on this.

    I have used this technique for years. The key is to ensure publisher and subscriber are in sync without snapshoting. make sure no one can do transactions in the servers. break replication, dropping subscriber, articles etc, while the publisher and subscriber dbs are not modified. At the end when you reset up replication, click on the option that says "subscriber already has the data".

Posting Permissions

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