Rotating Header Image

SQL 2000 Replication… changing Recovery Mode


Short Answer: YES

It had been a while since I worked with SQL 2000 Replication so I was fairly confident that you could but wasn’t willing to just go and do it without a proper test first.

I took at copy of the database that I was looking to do this on and built myself a little test enivornment.  I made sure my database was in full recovery mode… setup a transactional replication publication, distributor, and subscriber identical to the environment I’m wanting to do this on.  I inserted, updated, and deleted some rows just to make sure everything was tickity-boo before doing my test.

I altered the publisher database and set the recovery mode to Simple without doing anything special to replication… I did more inserts, updates, and deletes and everything flowed through smoothly.  I switch it back to Full Recovery Mode and lather, rinse, repeat and everything still worked fine.

When I Binged the question I didn’t get many (or any) hits that applied.  One link I went to (totally unrelated) was very scary/interesting… A user asked about using Simple Recovery Mode and whether Replication will work or not… someone told them that you can’t use simple recovery mode cause transactional replication depends on the transaction log and that a checkpoint will flush everything out (replicated or not) and cause replication to fail. 


*sigh* another example of why you don’t want to take everything you read at face value without investigating on your own.  Some answers are just clearly wrong (like the one above)… while others are kind of right depending on the situation and need.  My replication test I just performed worked successfully for me but just for arguements sake what if there was a publication setting somewhere that would invalidate your publication and force you into a reinitializing your subscribers?

I guess the moral of the story is something that has been commented on by many people in the community.  The internet is a great resource for finding information and answers but at the end of the day it’s up to YOU to test and ensure that the information and answers you get are right for you and your environment.


Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

One Comment

  1. Given the flurry of rumors surrounding the HTC Aero and mini flagship handsets, readers should keep in mind that these are all based on speculations. As of the moment, HTC has not yet released official details regarding its upcoming device.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.