November 15, 2018 at 7:57 pm
Hi,
I need to configure full, differential & log backups of Always On Availability databases in such a way that backups will always happen on a Primary Replica every time to NAS. Lets say Node 1 is a Primary Replica and Node 2 is a Secondary Replica currently. Full backup on Node 1 happens at a particular time to NAS. After that failover occurs and Node 2 becomes Primary Replica.
1. Will sub-sequential differential & log backups happen on Node 2 which is Primary Replica now?
2. If differential & log backups happen on Node 2, will I be able to restore database from Full backup of Node 1 and differential & log backups of Node 2 in case recovery need arises?
Answer to above questions in mentioned scenario will be helpful.
November 15, 2018 at 11:06 pm
sqldba4u - Thursday, November 15, 2018 7:57 PMHi,I need to configure full, differential & log backups of Always On Availability databases in such a way that backups will always happen on a Primary Replica every time to NAS. Lets say Node 1 is a Primary Replica and Node 2 is a Secondary Replica currently. Full backup on Node 1 happens at a particular time to NAS. After that failover occurs and Node 2 becomes Primary Replica.
1. Will sub-sequential differential & log backups happen on Node 2 which is Primary Replica now?
2. If differential & log backups happen on Node 2, will I be able to restore database from Full backup of Node 1 and differential & log backups of Node 2 in case recovery need arises?Answer to above questions in mentioned scenario will be helpful.
sqldba4u - Thursday, November 15, 2018 7:57 PMHi,I need to configure full, differential & log backups of Always On Availability databases in such a way that backups will always happen on a Primary Replica every time to NAS. Lets say Node 1 is a Primary Replica and Node 2 is a Secondary Replica currently. Full backup on Node 1 happens at a particular time to NAS. After that failover occurs and Node 2 becomes Primary Replica.
1. Will sub-sequential differential & log backups happen on Node 2 which is Primary Replica now?
2. If differential & log backups happen on Node 2, will I be able to restore database from Full backup of Node 1 and differential & log backups of Node 2 in case recovery need arises?Answer to above questions in mentioned scenario will be helpful.
1. It depends on your alwayson backup setting, if you choose a backup preference primary, it will always try primary.
2. Yes you can recover it
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 15, 2018 at 11:31 pm
muthukkumaran Kaliyamoorthy - Thursday, November 15, 2018 11:06 PMsqldba4u - Thursday, November 15, 2018 7:57 PMtHi,I need to configure full, differential & log backups of Always On Availability databases in such a way that backups will always happen on a Primary Replica every time to NAS. Lets say Node 1 is a Primary Replica and Node 2 is a Secondary Replica currently. Full backup on Node 1 happens at a particular time to NAS. After that failover occurs and Node 2 becomes Primary Replica.
1. Will sub-sequential differential & log backups happen on Node 2 which is Primary Replica now?
2. If differential & log backups happen on Node 2, will I be able to restore database from Full backup of Node 1 and differential & log backups of Node 2 in case recovery need arises?Answer to above questions in mentioned scenario will be helpful.
sqldba4u - Thursday, November 15, 2018 7:57 PMHi,I need to configure full, differential & log backups of Always On Availability databases in such a way that backups will always happen on a Primary Replica every time to NAS. Lets say Node 1 is a Primary Replica and Node 2 is a Secondary Replica currently. Full backup on Node 1 happens at a particular time to NAS. After that failover occurs and Node 2 becomes Primary Replica.
1. Will sub-sequential differential & log backups happen on Node 2 which is Primary Replica now?
2. If differential & log backups happen on Node 2, will I be able to restore database from Full backup of Node 1 and differential & log backups of Node 2 in case recovery need arises?Answer to above questions in mentioned scenario will be helpful.
1. It depends on your alwayson backup setting, if you choose a backup preference primary, it will always try primary.
2. Yes you can recover it
Appreciate your response. I was more concerned about the 2nd point which is regarding db recovery from backups on different replicas. If its the case, it is quite helpful.
November 16, 2018 at 8:40 am
sqldba4u - Thursday, November 15, 2018 11:31 PMmuthukkumaran Kaliyamoorthy - Thursday, November 15, 2018 11:06 PMsqldba4u - Thursday, November 15, 2018 7:57 PMtHi,I need to configure full, differential & log backups of Always On Availability databases in such a way that backups will always happen on a Primary Replica every time to NAS. Lets say Node 1 is a Primary Replica and Node 2 is a Secondary Replica currently. Full backup on Node 1 happens at a particular time to NAS. After that failover occurs and Node 2 becomes Primary Replica.
1. Will sub-sequential differential & log backups happen on Node 2 which is Primary Replica now?
2. If differential & log backups happen on Node 2, will I be able to restore database from Full backup of Node 1 and differential & log backups of Node 2 in case recovery need arises?Answer to above questions in mentioned scenario will be helpful.
sqldba4u - Thursday, November 15, 2018 7:57 PMHi,I need to configure full, differential & log backups of Always On Availability databases in such a way that backups will always happen on a Primary Replica every time to NAS. Lets say Node 1 is a Primary Replica and Node 2 is a Secondary Replica currently. Full backup on Node 1 happens at a particular time to NAS. After that failover occurs and Node 2 becomes Primary Replica.
1. Will sub-sequential differential & log backups happen on Node 2 which is Primary Replica now?
2. If differential & log backups happen on Node 2, will I be able to restore database from Full backup of Node 1 and differential & log backups of Node 2 in case recovery need arises?Answer to above questions in mentioned scenario will be helpful.
1. It depends on your alwayson backup setting, if you choose a backup preference primary, it will always try primary.
2. Yes you can recover itAppreciate your response. I was more concerned about the 2nd point which is regarding db recovery from backups on different replicas. If its the case, it is quite helpful.
Create a sample database, add it to your AG, run the backups, failover, run more backups, and try to restore. See what happens.
Are you using the maintenance plans? If so, try something else. They are not smart enough for an AG. Ola Hallengren's scripts work very well for this. https://ola.hallengren.com/. Same with MinionWare. http://www.minionware.net
In our case, we have the backups being copied to a share. Regardless of which server is the primary, the backups go to the same place.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 20, 2018 at 12:55 am
Michael L John - Friday, November 16, 2018 8:40 AMsqldba4u - Thursday, November 15, 2018 11:31 PMmuthukkumaran Kaliyamoorthy - Thursday, November 15, 2018 11:06 PMsqldba4u - Thursday, November 15, 2018 7:57 PMtHi,I need to configure full, differential & log backups of Always On Availability databases in such a way that backups will always happen on a Primary Replica every time to NAS. Lets say Node 1 is a Primary Replica and Node 2 is a Secondary Replica currently. Full backup on Node 1 happens at a particular time to NAS. After that failover occurs and Node 2 becomes Primary Replica.
1. Will sub-sequential differential & log backups happen on Node 2 which is Primary Replica now?
2. If differential & log backups happen on Node 2, will I be able to restore database from Full backup of Node 1 and differential & log backups of Node 2 in case recovery need arises?Answer to above questions in mentioned scenario will be helpful.
sqldba4u - Thursday, November 15, 2018 7:57 PMHi,I need to configure full, differential & log backups of Always On Availability databases in such a way that backups will always happen on a Primary Replica every time to NAS. Lets say Node 1 is a Primary Replica and Node 2 is a Secondary Replica currently. Full backup on Node 1 happens at a particular time to NAS. After that failover occurs and Node 2 becomes Primary Replica.
1. Will sub-sequential differential & log backups happen on Node 2 which is Primary Replica now?
2. If differential & log backups happen on Node 2, will I be able to restore database from Full backup of Node 1 and differential & log backups of Node 2 in case recovery need arises?Answer to above questions in mentioned scenario will be helpful.
1. It depends on your alwayson backup setting, if you choose a backup preference primary, it will always try primary.
2. Yes you can recover itAppreciate your response. I was more concerned about the 2nd point which is regarding db recovery from backups on different replicas. If its the case, it is quite helpful.
Create a sample database, add it to your AG, run the backups, failover, run more backups, and try to restore. See what happens.
Are you using the maintenance plans? If so, try something else. They are not smart enough for an AG. Ola Hallengren's scripts work very well for this. https://ola.hallengren.com/. Same with MinionWare. http://www.minionware.net
In our case, we have the backups being copied to a share. Regardless of which server is the primary, the backups go to the same place.
I tried with maintenance plan and it worked. But thanks for giving a lead to Ola Hallengren's scripts.
November 20, 2018 at 9:09 am
You should try OLA script, it is really good and make our job easier.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply