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.--> 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.|||Anyone...any suggestions?|||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.|||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....|||there was a thread a few days back that was talking about transactional replication without snapshot. check it out:
http://www.dbforums.com/showthread.php?p=3665793#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".
Saturday, February 25, 2012
Replication Maintanance Issues!
Labels:
database,
following,
gals,
guys,
ideas,
maintanance,
microsoft,
mysql,
oracle,
physical,
prod,
replication,
reporting,
scenariocurrent,
separte,
server,
servers,
situation-i,
sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment