Post reply

SQL Server HA Backups

  • June 2, 2015 at 12:35 pm

    #297430

    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!

  • June 2, 2015 at 12:55 pm

    #1802408

    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

  • June 2, 2015 at 1:03 pm

    #1802411

    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.

  • June 3, 2015 at 2:44 am

    #1802515

    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

  • June 3, 2015 at 9:06 am

    #1802686

    Nailed it, thanks Gila.

    Perfect article.

  • June 8, 2015 at 3:39 pm

    #1803845

    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 5 (of 5 total)

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