why cant we take regular full backup on secondary replica?

Question

why cant we take regular full backup on secondary replica?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

coolchaitu - Wednesday, July 12, 2017 9:24 AM
But 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10315 More actions · Answer 2

GilaMonster - Wednesday, July 12, 2017 9:41 AM
coolchaitu - Wednesday, July 12, 2017 9:24 AM
But 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.

x SSC-Insane Points: 23660 More actions · Answer 3

coolchaitu - Wednesday, July 12, 2017 10:06 PM
GilaMonster - Wednesday, July 12, 2017 9:41 AM
coolchaitu - Wednesday, July 12, 2017 9:24 AM
But 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.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

patrickmcginnis59 10839 - Friday, July 14, 2017 8:52 AM
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.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

x SSC-Insane Points: 23660 More actions · Answer 5

GilaMonster - Friday, July 14, 2017 9:49 AM
patrickmcginnis59 10839 - Friday, July 14, 2017 8:52 AM
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.
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!