SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backups - AlwaysON Availabilty Groups


Backups - AlwaysON Availabilty Groups

Author
Message
Alejandro Santana
Alejandro Santana
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 863
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?
Beatrix Kiddo
Beatrix Kiddo
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26773 Visits: 6895
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?

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.

Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208627 Visits: 18553
aledavsanort - Tuesday, January 23, 2018 6:30 AM
Backups 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" ;-)
Alejandro Santana
Alejandro Santana
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 863

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.

Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208627 Visits: 18553
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.


That's how it is and it's by design

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Smendle
Smendle
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3300 Visits: 1207
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search