Why we schedule copy_only backup on secondary in always on

  • Hello Experts,

    Can someone help to understand the below 

    • why we schedule copy_only backup in always on. what will happened if did normal only ?
    • If we have schedule Full copy_Only backup in secondary, can we configure Tlog backup in primary and how it will work ?
  • If you try to take a non-copy-only backup on your secondary it will fail. And yes, you can mix the backups like that if you like, but make sure you know where all your files are in case you need to do a restore.

  • I want to understand the reason behind it. why regular full backup is not allowed in secondary replica in always on

  • In short --> An always on cluster sends log files from the primary server to the other server(s). (like mirror databases or transaction log shipping)
    During the time the always on is setup, the primary database requires a backup, and this backup is restored on the secondary with NORECOVERY.
    If all is setup correct the secondary is always waiting for log transactions to update the database.
    When you do a regular full backup on the primary database, you break the chain of this transferring of the database log files. You need to see it as just a SQL log backup, and transferred to the secondary.
    Therefor the SCN's need to be in the correct order. and with a full backup it is broken.
    A copy only backup does not break this chain.

    P.S. I did this out of my head, as ow I see this process, but I think if you google this question, you should find your answer pretty quick.

    Peter.

  • It's got nothing to do with the log records as full backups do not interfere with the log chain. EVER.

    The DB on the secondary is read-only. A normal backup modifies the DCM pages in the database, and hence cannot run on a read-only database.

    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
  • peter2501 - Friday, November 2, 2018 8:55 AM

    When you do a regular full backup on the primary database, you break the chain of this transferring of the database log files. You need to see it as just a SQL log backup, and transferred to the secondary.

    Nope.
    You can take as many normal full backups on the primary as you like, it'll have no effect on the AG. The copy_only limitation is for taking full backups on a readable secondary.

    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
  • anujkumar.mca - Friday, November 2, 2018 5:34 AM

    I want to understand the reason behind it. why regular full backup is not allowed in secondary replica in always on

    Ask Microsoft then.

  • Beatrix Kiddo - Friday, November 2, 2018 10:21 AM

    anujkumar.mca - Friday, November 2, 2018 5:34 AM

    I want to understand the reason behind it. why regular full backup is not allowed in secondary replica in always on

    Ask Microsoft then.

    No need.

    A full backup changes the database (resets the DCM pages). A readable secondary cannot be written to, hence only copy_only full backups are allowed.

    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
  • Thank you so much Gila, You always rock

Viewing 9 posts - 1 through 8 (of 8 total)

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