July 12, 2017 at 9:41 am
coolchaitu - Wednesday, July 12, 2017 9:24 AMBut how is it possible? In the link, they are talking about regular full backups but you are saying regular full backups cannot be taken for read-only databases.
I said no such thing.
I said:
We cannot take full backups of readable secondaries
Before you ask the next question, ask yourself, what's the main difference between a read-only database, and 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
July 12, 2017 at 10:06 pm
GilaMonster - Wednesday, July 12, 2017 9:41 AMcoolchaitu - Wednesday, July 12, 2017 9:24 AMBut how is it possible? In the link, they are talking about regular full backups but you are saying regular full backups cannot be taken for read-only databases.I said no such thing.
I said:
We cannot take full backups of readable secondaries
Before you ask the next question, ask yourself, what's the main difference between a read-only database, and a readable secondary?
I asked myself and though hard but couldnt get it. Could you please let me know the difference between a read-only database, and a readable secondary.
July 14, 2017 at 8:52 am
coolchaitu - Wednesday, July 12, 2017 10:06 PMGilaMonster - Wednesday, July 12, 2017 9:41 AMcoolchaitu - Wednesday, July 12, 2017 9:24 AMBut how is it possible? In the link, they are talking about regular full backups but you are saying regular full backups cannot be taken for read-only databases.I said no such thing.
I said:
We cannot take full backups of readable secondaries
Before you ask the next question, ask yourself, what's the main difference between a read-only database, and a readable secondary?
I asked myself and though hard but couldnt get it. Could you please let me know the difference between a read-only database, and a readable secondary.
(edited) (and edited again to include Gails clarification!)
For purposes of recoverability, its best to learn the rules and test your backups for recovery purposes.
To get the inside story, you probably need to read up on the details, for which there seem to be plenty. I'm guessing that the primary difference between a read only replica in an AG and a read only database is that the read only replica is only read only from the VIEWPOINT of the connection to the replica, but its actually changing due to connections and transactions on the primary replica. Whereas read only databases are actually read only across the board, they don't change as long as they remain read only.
https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx gives some details on backing up a read only database, from what I can gather, SQL Server will write the backup information elsewhere. (I'm not sure what version that page refers to though.)
For read-only databases, full backups used alone are easier to manage than when they are used with differential backups. When a database is read-only, backup and other operations cannot change the metadata that is contained in the file. Therefore, metadata that is required by a differential backup, such as the log sequence number at which the differential backup begins (the differential base LSN) is stored in the master database.
So maybe full backups can happen on a read only database because by definition the database hasn't changed since it went read only. Secondary replicas differ here because even though the secondary is read only, its only read only from the connection to the secondary, IT IS ACTUALLY CHANGING DUE TO ACTIVITY ON THE PRIMARY.
So maybe its just a design decision here. It might have to do with the fact that a read only database has no "read write replica" that could receive differential backups (and then update the metadata) whereas with availability groups, you really do have a read / write database, its just not on the secondary replica. So if you took a diff full backup on the secondary replica, the "metadata" would have to be written on the primary database (thus sent over the wire) whereas with simple "read only" databases (no read / write replicas) diffs reading metadata stored in the master database can discover whether the database has been changed or not.
But that's just crackpot theories of mine because I'm a wanna be systems programmer. In reality, the takeaway here is STILL to learn the rules and test your backups.
July 14, 2017 at 9:49 am
patrickmcginnis59 10839 - Friday, July 14, 2017 8:52 AMI'm guessing that the primary difference between a read only replica in an AG and a read only database is that the read only replica is only read only from the VIEWPOINT of the connection to the replica, but its actually changing due to connections and transactions on the primary replica. Whereas read only databases are actually read only across the board, they don't change as long as they remain read only.
Yup, exactly.
It might have to do with the fact that a read only database has no "read write replica" that could receive differential backups (and then update the metadata) whereas with availability groups, you really do have a read / write database, its just not on the secondary replica. So if you took a diff backup on the secondary replica, the "metadata" would have to be written on the primary database (thus sent over the wire) whereas with simple "read only" databases (no read / write replicas) diffs reading metadata stored in the master database can discover whether the database has been changed or not.
Yup, though replace 'if you took a diff backup on the secondary' with 'if you took a normal full backup', as the normal full backup changes the DCM, differential backups don't.
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
July 14, 2017 at 11:23 am
GilaMonster - Friday, July 14, 2017 9:49 AMpatrickmcginnis59 10839 - Friday, July 14, 2017 8:52 AMI'm guessing that the primary difference between a read only replica in an AG and a read only database is that the read only replica is only read only from the VIEWPOINT of the connection to the replica, but its actually changing due to connections and transactions on the primary replica. Whereas read only databases are actually read only across the board, they don't change as long as they remain read only.Yup, exactly.
It might have to do with the fact that a read only database has no "read write replica" that could receive differential backups (and then update the metadata) whereas with availability groups, you really do have a read / write database, its just not on the secondary replica. So if you took a diff backup on the secondary replica, the "metadata" would have to be written on the primary database (thus sent over the wire) whereas with simple "read only" databases (no read / write replicas) diffs reading metadata stored in the master database can discover whether the database has been changed or not.
Yup, though replace 'if you took a diff backup on the secondary' with 'if you took a normal full backup', as the normal full backup changes the DCM, differential backups don't.
Thanks for the clarification, googling "sql server dcm" really tells much of the story here!
September 25, 2024 at 6:51 pm
On a Secondary replica of Always-On we can only take COPY_ONLY Full backup, normal Log backups. No differential backups can be taken.
Also, we can even restore the Copy-only full followed by it's Log backups with a point in time recovery. There is no LSN break or mismatch when it comes to secondary replica backup of Always on.
Keep in mind, it will be only configured when the backup preference is set to Prefer_secondary or Secondary only.
But, when it comes to a standalone database, the copy_only backup file is independent without participating in LSN and Point in time recovery.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply