If i want to my test server database should be in sync with my prod database,
which one is the best. after complete data sync i dont want any data updates.
i am going to setting up only one way, from prod to test.
please advise if any risks or issues
John,
please take a look here and post back with any questions:
http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I know it Paul, i saw in your web site in the past. just curious if any other
issues or concerns.
thanks to you and Hilary, may god bless you two people for saving so many
DBA's tough time.
thanks once again
"Paul Ibison" wrote:
> John,
> please take a look here and post back with any questions:
> http://www.replicationanswers.com/Standby.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Showing posts with label prod. Show all posts
Showing posts with label prod. Show all posts
Monday, March 12, 2012
Saturday, February 25, 2012
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.--> 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".
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".
Subscribe to:
Posts (Atom)