October 30, 2018 at 8:24 pm
Hello Experts,
Can someone help to understand the below
October 31, 2018 at 2:09 am
If you try to take a non-copy-only backup on your secondary it will fail. And yes, you can mix the backups like that if you like, but make sure you know where all your files are in case you need to do a restore.
November 2, 2018 at 5:34 am
I want to understand the reason behind it. why regular full backup is not allowed in secondary replica in always on
November 2, 2018 at 8:55 am
In short --> An always on cluster sends log files from the primary server to the other server(s). (like mirror databases or transaction log shipping)
During the time the always on is setup, the primary database requires a backup, and this backup is restored on the secondary with NORECOVERY.
If all is setup correct the secondary is always waiting for log transactions to update the database.
When you do a regular full backup on the primary database, you break the chain of this transferring of the database log files. You need to see it as just a SQL log backup, and transferred to the secondary.
Therefor the SCN's need to be in the correct order. and with a full backup it is broken.
A copy only backup does not break this chain.
P.S. I did this out of my head, as ow I see this process, but I think if you google this question, you should find your answer pretty quick.
Peter.
November 2, 2018 at 9:33 am
It's got nothing to do with the log records as full backups do not interfere with the log chain. EVER.
The DB on the secondary is read-only. A normal backup modifies the DCM pages in the database, and hence cannot run on a read-only database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2018 at 9:34 am
peter2501 - Friday, November 2, 2018 8:55 AMWhen you do a regular full backup on the primary database, you break the chain of this transferring of the database log files. You need to see it as just a SQL log backup, and transferred to the secondary.
Nope.
You can take as many normal full backups on the primary as you like, it'll have no effect on the AG. The copy_only limitation is for taking full backups on a readable secondary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2018 at 10:21 am
anujkumar.mca - Friday, November 2, 2018 5:34 AMI want to understand the reason behind it. why regular full backup is not allowed in secondary replica in always on
Ask Microsoft then.
November 2, 2018 at 11:45 am
Beatrix Kiddo - Friday, November 2, 2018 10:21 AManujkumar.mca - Friday, November 2, 2018 5:34 AMI want to understand the reason behind it. why regular full backup is not allowed in secondary replica in always onAsk Microsoft then.
No need.
A full backup changes the database (resets the DCM pages). A readable secondary cannot be written to, hence only copy_only full backups are allowed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2018 at 11:52 am
Thank you so much Gila, You always rock
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply