locked
If Mirroring cannot Run under Simple recovery model,how replication can run under simple recovery Model RRS feed

  • Question

  • Hi,

    While looking into concept of Mirroring and Replication.

    I have a doubt "If Mirroring cannot Run under Simple recovery model,how replication can run under simple recovery Model"

    What is the effect of recovery model in Mirroring and Replication.

    Thanks

    • Changed type Avi111 Friday, January 6, 2017 2:49 AM This dont not require discussion
    Wednesday, January 4, 2017 9:25 AM

Answers

  • Avi,

    Are you talking about Snapshot or Merge or Transnational Replication here?

    They are different technologies. Mirroring has to replicate or send each and every log records to the mirrored instance; While Replication only send transactions which are marked for replication. Log reader does this job of marking those transactions which has to be replicated.

    For Example: You have 10 tables in a database and there are DDL and DML transactions on those table. Mirroring will send all those transactions on all 10 tables to mirrored instance. On the other hand Replication will only replicate such transaction which are marked for replication (say you have only specified 5 tables out of these 10 tables to be replicated)

    Also please note that even you can configure Replication under any three recovery models but there will be a little bit change in behaviour of database log the moment you configure replication. Log will not be truncated once transaction is completed but NOT REPLICATED even you take backup or try to truncate log.

    Thanks,


    Kindly mark the reply as answer if they help

    • Marked as answer by Avi111 Friday, January 6, 2017 2:50 AM
    Wednesday, January 4, 2017 1:20 PM

All replies

  • There is a log reader agent component to replicate the modified data 

    https://msdn.microsoft.com/en-us/library/ms146878.aspx?f=255&MSPPError=-2147217396


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 4, 2017 9:40 AM
  • Hi ,

    As per my knowledge ,

    Replication uses SQL Agent jobs to capture the data, log and distribution agent to move the data to other databases(Publisher or subscriber), Mirroring will log data directly in unsent portion of log, once data is moved it will be cleared from unsent log portion(if commited) and no agent take cares o fit and log backups clears the log.

    Replication has intermediate database called distribution , which take cares of data distribution and logs all data.

    So Replication works on simple recovery model as well but where as mirroring uses log files internally for all data transfers and requires to be full recovery model.

    Mirroring works on databases level changes, where as Replication is table or object level.

    Hope this helps.

     


    Thanks, Dev SQL DBA


    • Edited by Hanuman D Wednesday, January 4, 2017 10:40 AM
    Wednesday, January 4, 2017 10:28 AM
  • I suspect it is because mirroring requires a greater level of logging than what is required in simple and bulk logged recovery models to mirror each modification to the mirror. A transaction is modified in the log buffer in mirroring and then mirrored to the log buffer on the mirror. With bulk logged recovery model certain changes are not logged and bcp results in bcm pages being used - which likely can't be mirrored.

    Mirroring not only mirrors DML, but DDL and changes to the database - like the addition of database files, file shrinks, and database users being added to the database.

    Transactional replication, as pointed out reads log entries and replicates them. It uses a different mechanism.

    Wednesday, January 4, 2017 10:35 AM
  • Avi,

    Are you talking about Snapshot or Merge or Transnational Replication here?

    They are different technologies. Mirroring has to replicate or send each and every log records to the mirrored instance; While Replication only send transactions which are marked for replication. Log reader does this job of marking those transactions which has to be replicated.

    For Example: You have 10 tables in a database and there are DDL and DML transactions on those table. Mirroring will send all those transactions on all 10 tables to mirrored instance. On the other hand Replication will only replicate such transaction which are marked for replication (say you have only specified 5 tables out of these 10 tables to be replicated)

    Also please note that even you can configure Replication under any three recovery models but there will be a little bit change in behaviour of database log the moment you configure replication. Log will not be truncated once transaction is completed but NOT REPLICATED even you take backup or try to truncate log.

    Thanks,


    Kindly mark the reply as answer if they help

    • Marked as answer by Avi111 Friday, January 6, 2017 2:50 AM
    Wednesday, January 4, 2017 1:20 PM
  • Maybe I am missing the actual point of the question, but if it is about why Transactional Replication works fine in Simple mode and DB Mirroring/AlwaysOn AGs don't then the answer is fairly simple: Because MS hasn't implemented that (yet).

    My assumption would be that the reason for it is in the initialization. DBM/AlwaysOn relies on Full+TLog Backups to get an initial sync of the data in SQL pre 2016. And that doesn't work in Simple mode. Replication has a different mechanism for this, so it can work around the issue.

    I had a lot of disucssions with experts at MS regarding this topic, so maybe at some point they will allow AlwaysOn with Simple recovery, I don't know... So far the pain of users was apparantly never big enough to justify the effort.

    Wednesday, January 4, 2017 6:18 PM