Post reply

What recovery model should the replication subscriber db be using?

  • September 9, 2009 at 2:24 pm

    #137995

    Three servers:

    A : production

    B : hot-standby

    C : replicated

    Database on Server A is in full recovery model. Transaction Logs are being shipped to Server B on a regular basis.

    The database on Server A is also being replicated through transactional replication to Server C.

    The database on Server C is set up in "Bulk-Logged" recovery model. (okay, this gets real ugly here.) Daily, the database is set to simple recovery model, the log file is shrunk via DBCC ShrinkFile, then the database is reset to "Bulk-Logged" again. The shrinking is through a job that runs at 3pm; the daily backups run a 1am.

    Now, I recognize that shrinking the log file is not good... there's been plenty of discussion about that already here, and I'm not looking to repeat it. And definitely not doing a full backup after the shrinking is VERY BAD - but again, a different topic.

    What I want to find out is if there is any reason that the database on Server C needs to be in "Bulk-Logged" at all? I feel that it should be put in the simple recovery model (which will avoid all the issues above), but co-workers are saying that the replication requires it to be in bulk-logged.

    I've searched BOL (in the Replication and Recovery Model topics) and the internet (http://www.google.com/search?q=sql+replication+recovery+mode and http://www.google.com/search?q=sql+replication+recovery+mode+site%3Amicrosoft.com)... even Gail sidesteps this issue at http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/. BOL does talk about certain features (such as database mirroring) that require the database to remain in the full recovery model, but doesn't say anything about replication. It also talks about considerations when switching to/from the simple recovery model (which we aren't doing...). I can find internet forum posts where people have set the subscription database to simple (and other posts suggesting this is not a good idea), but I can't find anything definitive from MS as to whether replication requires the subscription database to be in any particular recovery model.

    So, are there any pros/cons about having the subscription database in the simple recovery model? Links (especially MS links) would be appreciated.

    Thanks,

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 9, 2009 at 3:11 pm

    #1051458

    replication is not dependant on the recovery mode to function, the publisher can be in simple mode as it will not allow transactions to be removed from the publisher until they are transferred to the distributer.

    You don't say what type of replication you are using. Is the subscriber updateable? That would govern your recovery mode and whether you need log backups of the subscriber. If the subscriber is read only you don't need log backups and I presume in the event of the loss of the subscriber you would reinitialise it from a snapshot, so simple mode would do.

    hope that helps.

    george

    ---------------------------------------------------------------------

  • February 3, 2014 at 3:51 am

    #1685422

    I imagine using bulk logged on the subscription replica is a replication throughput issue,

    all the insert update action from the replication stored procs will be getting logged in the simple and full recovery model.

    Bulk logged may be delivering a throughput benefit as each insert/update isnt getting fully logged and you can argue that this isnt required on a subscription copy.

    Regards

    ColinR

  • February 3, 2014 at 4:08 am

    #1685426

    Replication doesn't require any recovery model from any of its databases (publisher, distributer, subscriber). It's technically in simple recovery anyway since the log is truncated without another backup running.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 3, 2014 at 4:09 am

    #1685427

    colin Robinson-345240 (2/3/2014)

    I imagine using bulk logged on the subscription replica is a replication throughput issue,

    all the insert update action from the replication stored procs will be getting logged in the simple and full recovery model.

    Bulk logged may be delivering a throughput benefit as each insert/update isnt getting fully logged and you can argue that this isnt required on a subscription copy.

    Any operation that can be minimally logged in bulk-logged recovery is minimally logged in simple recovery as well. Updates are always fully logged and it's unlikely that inserts from replication would qualify for minimal logging as it it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

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