locked
why full recovery model is required for mirroring RRS feed

  • Question

  • According to my understanding in mirroring transactions are copied to mirrored database from log buffer in principal server so why we need to keep database in full recovery model
    Wednesday, January 27, 2016 10:00 AM

All replies

  • Hi, first of all this feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.

    However, mirroring is implemented on a per-database basis and works only with databases that use the FULL recovery model.

    Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server.

    More information

    https://msdn.microsoft.com/en-us/library/ms189852(v=sql.120).aspx

    Hope will help


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    • Proposed as answer by Ice Fan Wednesday, February 3, 2016 11:25 AM
    Wednesday, January 27, 2016 10:09 AM
  • So is data read from ldf of log buffer for mirroring ? will number of vlf's impact mirroring
    Wednesday, January 27, 2016 12:50 PM
  • Number of VLF will always impact performance..

    What does the below command return?

    DBCC  LOGINFO (dbid)

     

    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

    Sunday, January 31, 2016 7:37 AM