Hi Mates,
Today we gonna explore all the different options for backing up the Always on Databases through maintenance plans. Definitely you will find something strange if you have not worked on it prior.
Let's explore step by step. At first I configured Always on between 2 nodes.
And I created 2 databases NormalDB on Node1 & NormalDB2 on Node2 intentionally.
I created Maintenance plan on each of them & included AG database & normal database on each server. By the by as you know by default backup option on AG is prefer secondary & I didn't make any changes to that too.
Case 1: With all Defaults
NODE 1: The backup of normaldb is happening however AG database is not getting backed up.
Note: Here job will appear as success .
Reason: Here it is because of backup preference of AG which is being set as secondary.
NODE 2: As like in first case the backup of normaldb2 is happening
Note: However unlike on Node 1 the job status will appear as failed.
Reason: On secondary Node it is not possible to take full backups. We can perform only copy-only backups.
Case 2: Now we are setting the backup preference to Primary.
NODE 1: AG Database is getting backed up along with Normal Database.
NODE 2: AG Database will not get backed up as the backup preference has been set to primary.
Case 3: Turning the backup preference back to secondary (default) but still want backup to happen on Node1
NODE 1: Edit the maintenance plan by enabling the option . This will ensure the backup to happen on Node 1 along with NODE 2 (copy only backup)
What if Fail-over happens now?
with Default settings (Backup preference being set to Secondary). Just like mentioned above if you don't click on copy-only backups then backups would fail on secondary(the previous primary server). Hence you need to ensure that part.
Recommendations & Best Practices:
--------------------------------------------
1) When we have Always on participating Databases & non AG databases it is recommended to create separate maintenance plans.
2) Backing up through Maintenance plans is advisable only if you are NON Database Administrator. We can make use of Ola Hallengren's Scripts to achieve the best results.
Today we gonna explore all the different options for backing up the Always on Databases through maintenance plans. Definitely you will find something strange if you have not worked on it prior.
Let's explore step by step. At first I configured Always on between 2 nodes.
And I created 2 databases NormalDB on Node1 & NormalDB2 on Node2 intentionally.
I created Maintenance plan on each of them & included AG database & normal database on each server. By the by as you know by default backup option on AG is prefer secondary & I didn't make any changes to that too.
Case 1: With all Defaults
NODE 1: The backup of normaldb is happening however AG database is not getting backed up.
Note: Here job will appear as success .
Reason: Here it is because of backup preference of AG which is being set as secondary.
NODE 2: As like in first case the backup of normaldb2 is happening
Note: However unlike on Node 1 the job status will appear as failed.
Reason: On secondary Node it is not possible to take full backups. We can perform only copy-only backups.
Case 2: Now we are setting the backup preference to Primary.
NODE 1: AG Database is getting backed up along with Normal Database.
NODE 2: AG Database will not get backed up as the backup preference has been set to primary.
Case 3: Turning the backup preference back to secondary (default) but still want backup to happen on Node1
NODE 1: Edit the maintenance plan by enabling the option . This will ensure the backup to happen on Node 1 along with NODE 2 (copy only backup)
What if Fail-over happens now?
with Default settings (Backup preference being set to Secondary). Just like mentioned above if you don't click on copy-only backups then backups would fail on secondary(the previous primary server). Hence you need to ensure that part.
Recommendations & Best Practices:
--------------------------------------------
1) When we have Always on participating Databases & non AG databases it is recommended to create separate maintenance plans.
2) Backing up through Maintenance plans is advisable only if you are NON Database Administrator. We can make use of Ola Hallengren's Scripts to achieve the best results.
Comments
Why we can not take normal back up on secondary database ,Can you please explain internal concept for that.