January 23, 2018 at 6:30 am
Hello,
I'm studying Windows Server Failover Clustering for a SQL Server Availability Groups with 2 instances and another node as a witness and i'm reading a book regarding SQL Server 2012 administration and i saw in this part and i quote:
"Backups of availability databases participating in availability groups can be conducted on any of the
replicas. Although backups are still supported on the primary replica, log backups can be conducted
on any of the secondaries. Note that this is independent of the replication commit mode being
used synchronous-commit or asynchronous-commit. Log backups completed on all replicas form a
single log chain.
As a result, the transaction log backups do not all have to be performed on the same replica.
This in no way means that serious thought should not be given to the location of your backups. It is
recommended that you store all backups in a central location because all transaction log backups are
required to perform a restore in the event of a disaster. Therefore, if a server is no longer available
and it contained the backups, you will be negatively affected. In the event of a failure, use the new
Database Recovery Advisor Wizard; it provides many benefits when conducting restores. For example,
if you are performing backups on different secondaries, the wizard generates a visual image of a
chronological timeline by stitching together all of the log files based on the Log Sequence Number
(LSN)." - Introducing Microsoft SQL Server 2012 from Ross Mistry and Stacia Misner
Can i make a full backup for the primary database and it will let me make transaction logs in the secondaries? i think the prerequisite on making transaction logs is making a full database backup.
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?
It creates a log chain, but it doesn't explain how the log chain works, what happens if i want to restore the primary database to a point in time? i got the full backup of the main database, how would i use these log chain backups made in the secondaries, will the primary allow the use of transaction logs made from another database engine to be used in it?
January 23, 2018 at 7:54 am
aledavsanort - Tuesday, January 23, 2018 6:30 AMSo.. 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?
It creates a log chain, but it doesn't explain how the log chain works, what happens if i want to restore the primary database to a point in time? i got the full backup of the main database, how would i use these log chain backups made in the secondaries, will the primary allow the use of transaction logs made from another database engine to be used in it?
Yes and yes. You can take full backups on the primary, and transaction log backups on the secondary, and combine those backups if you need to do a point in time restore (provided, of course, that you can get hold of all of those backups when you need them). HOWEVER, unless this has changed recently, you won't be able to use the GUI to do a nice, simple restore for you, you'll have to script it.
Whether it's a good strategy for you remains to be seen. In the even of a disaster, will you know where all of your backups are and have easy access to them all? Perhaps you could write them all to the same fileshare, ensuring that the files are date-stamped?
It's really worthwhile setting up a Dev cluster and testing this scenario yourself, because it seems easier when you're actually doing it.
January 23, 2018 at 8:37 am
aledavsanort - Tuesday, January 23, 2018 6:30 AMBackups of availability databases participating in availability groups can be conducted on any of the
replicas.
Except differentials, they are not supported on secondarys
aledavsanort - Tuesday, January 23, 2018 6:30 AM
Although backups are still supported on the primary replica, log backups can be conducted
on any of the secondaries. Log backups completed on all replicas form a
single log chain.
This is correct
aledavsanort - Tuesday, January 23, 2018 6:30 AM
As a result, the transaction log backups do not all have to be performed on the same replica.
This in no way means that serious thought should not be given to the location of your backups. It is
recommended that you store all backups in a central location because all transaction log backups are
required to perform a restore in the event of a disaster. Therefore, if a server is no longer available
and it contained the backups, you will be negatively affected.
Serious thought should be given to the location of backups for Availability Groups as they should ideally be centrally stored.
This is thankfully made easier with Cluster Shared Volumes in Windows Server Failover Clusters and SQL Server 2014 onwards
aledavsanort - Tuesday, January 23, 2018 6:30 AM
Can i make a full backup for the primary database and it will let me make transaction logs in the secondaries?
yes, you can. The secondarys have to be enabled for backup operations in the Availability Group config
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?
what exactoly do you mean here, it's unclear
aledavsanort - Tuesday, January 23, 2018 6:30 AM
It creates a log chain, but it doesn't explain how the log chain works, what happens if i want to restore the primary database to a point in time? i got the full backup of the main database, how would i use these log chain backups made in the secondaries, will the primary allow the use of transaction logs made from another database engine to be used in it?
same as you would any log backups, use a complete chain to roll the database forward
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2018 at 10:22 am
Perry Whittle - Tuesday, January 23, 2018 8:37 AMaledavsanort - Tuesday, January 23, 2018 6:30 AMBackups of availability databases participating in availability groups can be conducted on any of the
replicas.Perry Whittle
Except differentials, they are not supported on secondarysHow 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 > Logaledavsanort - 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 unclearIf 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.
January 24, 2018 at 8:13 am
aledavsanort - Tuesday, January 23, 2018 10:22 AMPerry Whittle - Tuesday, January 23, 2018 8:37 AMaledavsanort - Tuesday, January 23, 2018 6:30 AMBackups of availability databases participating in availability groups can be conducted on any of the
replicas.Perry Whittle
Except differentials, they are not supported on secondarysHow 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 > Logaledavsanort - 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 unclearIf 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.
That's how it is and it's by design
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 19, 2018 at 1:12 pm
aledavsanort - Tuesday, January 23, 2018 10:22 AMPerry Whittle - Tuesday, January 23, 2018 8:37 AMaledavsanort - Tuesday, January 23, 2018 6:30 AMBackups of availability databases participating in availability groups can be conducted on any of the
replicas.Perry Whittle
Except differentials, they are not supported on secondarysHow 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 > Logaledavsanort - 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 unclearIf 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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply