Merge Replication using Simple Recovery Model

Today I came across a rather trivial scenario where the publication database was set to Simple recovery. Typically I am use to having the recovery model set to Full, because that was a business norm. After a few trials within my development environment (thanks to VMWare) I began to seriously change my position. To be honest it never really settled well with me. Especially knowing that fact that even though the model was set to Full I still could not reap the benefits of a “Point-in-Time” (PIT) restore. Why? Well if you really must know, it is due to the fact that you won’t get exclusive access to the database since it is being replicated. Matter of fact, you’ll get a nifty little error indicating so if you attempt a restore of any sort. Here I’ll prove it to you.

PIT Restore Attempt with Replicating DB

See why did you doubt me? Now with that out of the way I figured I should shift my practice and change the recovery model to Simple for these specific instances and quite frankly for other similar projects going forward. So truthfully the recovery model does not really play a significant factor unless you have plenty of hard drive space that you are willing to sacrifice for log file consumption. Seriously the log file will grow and grow since it logs all transactions while in Full recovery. From a performance standpoint having your replicating DB in simple recovery mode might be a better route. Exactly the same for Bulk-Logged when you are (coincidentally) bulk loading data. It just makes things faster.

I also read an online article published by Microsoft which was sent to me via twitter by a SQL Server (Bad Ass) named Kendal Van Dyke (websitetwitter). The article stated that the recovery model can be either of the three. So that there just confirmed my original theory. I must tell you that Mr. Kendal has been extremely responsive to several of my questions even with the topics varying significantly by area. He is a top notch guy! That’s my story and I am sticking to it.

The only drawback that I can think of at this particular moment is that some backup software like Symantec’s Backup Exec requires databases to be set to Full in terms of the recovery model. I would suspect you would be bombarded with notifications screaming at you until you change it. If you know of any drawbacks with having a merge replicated database set to simple recovery, please let me know. I would be more than interested in hearing your experience.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s