SQL Server HA Backups

  • Ozzmodiar

    Ten Centuries

    Points: 1295

    Good day SQL friends,

    I'm looking for an opinion on an architecture I'm thinking of rolling out.

    I have a HA cluster with 3 nodes:

    Primary -- Main SQL Server

    Failover -- Passive node, only used in DR scenario

    Reporting -- Read Intent only queries directed here, SSRS.

    Pretty standard stuff.

    I am pondering with the idea of balancing the backups out between 2 nodes as follow:

    Primary: Full backup weekly, daily incremental.

    Reporting: Log backups every 5 minutes.

    I have tested that I can indeed restore the logs from the secondary node using the Full and Diff from the other server, no worries there.

    My concern is what will happen to the logs on the primary and fail-over server. Will they just grow, grow and grow?

    In the past I've always backed up everything on every server.

    Thanks for any opinions!

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Ozzmodiar (6/2/2015)


    Will they just grow, grow and grow?

    No.

    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
  • Ozzmodiar

    Ten Centuries

    Points: 1295

    Thanks Gila,

    To be clear; I will not be backing up the transaction logs on the primary node.

    A full or diff backup is not going to free up the log space. Assuming the schedule below, at what point can I expect the log space to be released? And what process will release it?

    Sunday -- Full -- Primary Node

    Monday -- Diff -- Primary Node

    Tuesday -- Diff -- Primary Node

    ...etc

    EVERY DAY -- Log Backup/5 min. -- Reporting Node.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    When the log backup runs.

    http://www.sqltechnet.com/2015/01/transaction-log-backups-on-availability.html

    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
  • Ozzmodiar

    Ten Centuries

    Points: 1295

    Nailed it, thanks Gila.

    Perfect article.

  • sqlnyc

    SSCommitted

    Points: 1726

    Just keep in mind that if REDO falls behind for any reason, backups on the Secondary will only capture up to the point of REDO, no matter how far behind it might be.

    If the hardening is up to LSN 2000 but the REDO thread is up to LSN 1500, then your backup will only capture work up to LSN 1500.

    sqlnyc

Viewing 6 posts - 1 through 6 (of 6 total)

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