Can i have simple recovery for my distribution Database in a transactional replication

Question

Can i have simple recovery for my distribution Database in a transactional replication

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

MG Chowdary Ten Centuries Points: 1064 More actions · Answer 1

Roy Ernest (10/5/2010)
The distributor can be set up in Simple mode. I do not see why you will want to keep it in Full mode.

If you set the distributor in simple recovery mode, then for point in time recovery what is the DR stratagy you practice? (as per BOL there will be transactional inconsistency if you do not set 'sycwithbackup' and if you set 'sycwithbackup' you sud have to take Tx-log backups where recovery sud be FULL/BULK.)

MG Chowdary Ten Centuries Points: 1064 More actions · Answer 2

So, Finally I can't set my Distribution database recovery to SIMPLE as I set the SYNCWITHBACKUP "true" on distribution database. the publisher log will not be truncated untill log is backedup on distribution database( where for logbackups the recovery sud be FULL).

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

It is best to not to set "Sync with Backup" option set if you want minimum latency. If you are worried about a DR situation for your distributor, why not set it up in a cluster environment? That is the safest way. In this case you can change the recovery mode to simple and get minimum latency.

If you are using log shipping method for your distributor for your DR, then you will need to have Master, MSDB back up. The Server name should be the same. (I am not sure if the IP too has to be the same) Therefore you are not having a Warm Standby.

-Roy

MG Chowdary Ten Centuries Points: 1064 More actions · Answer 4

Roy Ernest (10/22/2010)
It is best to not to set "Sync with Backup" option set if you want minimum latency.

The Latency will come in to focus only if i set "Sync with Backup" on publisher. But I am planning to set it on Distributor where there won't be a latency but will have a publisher log growth untill distribution is backedup.

I just found a Thread what i Actually looking out for, http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/ac861287-43ac-4d13-bff3-4fd5413a5d02

and a DR Strategy like this

http://support.microsoft.com/kb/886839

Thanks for your replies and suggest a good DR strategy for below mentioned scenario...

MG Chowdary Ten Centuries Points: 1064 More actions · Answer 5

In my case the current DR strategy is pretty basic where we have a full backup evry single day followed by a Diff evry 'x' Hrs and log for evry 'y' Hrs. for all the system DB , publisher DB & Distributor DB ( where both on a same machine/ instance). and the sync with backup "0".

the only criteria is that the time to recover the replication process sud be minimal and dataloss is accepted.

so sud i switch all databases (system,publisher,distribution) to simple recovery with regular full and diff backups and after diaster apply backups and run the reinitialize subsciptions.

or

to follow a complex backup strategy mentioned here

http://technet.microsoft.com/en-us/library/ms152560(SQL.90).aspx