• aledavsanort - Tuesday, January 23, 2018 10:22 AM

    Perry Whittle - Tuesday, January 23, 2018 8:37 AM

    aledavsanort - Tuesday, January 23, 2018 6:30 AM

    Backups of availability databases participating in availability groups can be conducted on any of the
    replicas.

    Perry Whittle


    Except differentials, they are not supported on secondarys

    How would someone backup the database the proper way then? full backups and transaction logs all the time?
    By proper way i mean: Full backup > Differential > Log

    aledavsanort - Tuesday, January 23, 2018 6:30 AM


    So.. if i make a full database backup in my primary it will allow me to make transaction logs in the secondaries? if so.. i can apply the transaction logs to the primary? 

    Perry Whittle


    what exactoly do you mean here, it's unclear

    If i make a full backup on the primary database and the transaction logs in the secondary database, the transaction logs could be applied to the primary database to go to a point in time?

    Everything falls if i can't make the differential backups in secondary replicas, i tought it would be a good idea to have the secondary replica make the full > differentials > log backups so we could put less pressure in the primary replica.

    as stated this is by design on the differential backups.  The only real pressure is IO and if you are backing up to an appliance whose sole purpose is for backups only then that pressure is mitigated against any IO pressure on the database(s) data and log files.
    Just as a note if you have a custom backup solution to take care of your entire enterprise of sql servers, you will note that when you issue a differential backup command it errors out on the secondary's.  There is a quick check you can do to determine if a database is a secondary replica or not.
    lookup the use of fn_hadr_backup_is_preferred_replica.  I essentially check for the existence of the function and then set a bit flag to 0 if it does NOT exist so I know to bypass that database on backups.  Thus all of my backups are against the primary, of course you could create logic to work everything but a differential against a secondary but I found it easier to keep the log chain as close to the primary as I could.