Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stephe40
Posting Yak Master
218 Posts |
 Posted - 2007-11-06 : 11:28:44
|
| I currently have a simple transactional replication setup for a database. My publisher and distributor are on the same box. The subscription is setup using a push agent.My question is related to recovery of the subscriber.So lets say replication is setup and working fine. Suddenly we had a failure on the subscriber database. Now I could just reconfigure the subscription, and the subscribing database would be back up and good to go, but the problem is that over time, we have made some changes to the subscribing database that are not made in the publisher. For example, the tables have different indexes. Just reconfiguring the subscritpion would not recover these objects.So I have to acutally restore the subscriber database. So I do that, and apply transaction logs up to the most recent transaction log backup. Now, consider that my transaction log backups on the subscriber happen every 4 hours, and the most recent transaction log backup I had was from 3 hours ago. So now at this point, my subscribing database is 3 hours behind my publisher.Now, will the distribution agent resend the missing 3 hours of transactions? In the distribution agent properties, there are two settings for transaction retention, "at least" and "but not more than". Currently they set to 0 and 72 hours respectivly. Now I would assume that if I set the "at least" setting to the subscriber transaction log backup period, in this case 4 hours, I would be covered, and the distribution agent would indeed re-replication the transactions that happend since the recovery point 3 hours. I just wanted to verify that this is acutally what these settings are refering too, and that if I set the "at least" setting to 4 hours, I would be covered.- Eric |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-06 : 11:50:08
|
quote:
Originally posted by stephe40
So lets say replication is setup and working fine. Suddenly we had a failure on the subscriber database. Now I could just reconfigure the subscription, and the subscribing database would be back up and good to go, but the problem is that over time, we have made some changes to the subscribing database that are not made in the publisher. For example, the tables have different indexes. Just reconfiguring the subscritpion would not recover these objects.....- Eric
It depends on what kind of changes you have made.If its just indexes you would be fine but if you changed schema or modified the data you might run into issues when you "reconfigure the subscription" ad the souce and destination db's would not be in sync. Then you'd have to re-create the snapshot and re-apply all your changes on the subscriber.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2007-11-06 : 12:05:57
|
| In this case, only indexes would be changed. So, is my assumption right? Will the distribution agent re replicate transactions? And how does the "at least" setting affect this operation? I guess the whole point is so that I don't have to re apply a snapshot, and hence lose my custom indexes.- Eric |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2007-11-06 : 12:12:51
|
| In this artile, http://msdn2.microsoft.com/en-us/library/ms152560.aspx, way at the bottom, it kind of addresses this issue. "Is the latest subscription database backup more recent than the maximum distribution retention setting on the distribution database (this determines whether the Distributor still has all the commands necessary to bring the Subscriber up-to-date)? If yes, go to step 2. If no, reinitialize the subscription; recovery is complete."So this statment implys that transactions, even after transactions are replicated, are saved in the distribution database for the maximum retention value. This doesn't make sense though, because if that was the case, my distribution database would be huge, and its not. I am assuming that after a transaction is successfully replicated, if difference between the current time, and the time the transaction happend is greater than the "at least" duration, it will be removed from the distribution database. Or am I wrong here?- Eric |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2007-11-14 : 08:47:44
|
| Anybody else have anything to add?- Eric |
|
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-14 : 08:58:57
|
| WOW, I would just not want to risk it if it was me. Can you not snapshot the data? |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2007-11-14 : 12:45:31
|
| I can snapshot the data. But considering recovery time, recreating a snapshot and reapplying it at the subscriber is almost the same thing as just recreateing the whole replication configuration. Considering we are planning to replication a VLDB, (approx 300 gb), resyncing the replication would take a while.- Eric |
|
|
|
|
|
|
|
|
Distribution transaction retention