December 13, 2011 at 7:27 am
I understand this thread is over a year old, but I thought I'd leave some more information for the next person...
I'm working through this issue right now. We purchased an HP SAN, specifically the P4500 G2 14.4 TB.
There's two different types of snapshots available - "Application Managed" which quiesces the application, and "Point In Time" which does not. I chose "Application Managed" to insure a consistent view of the data. When I take this snapshot, HP takes it upon itself to write a record to the msdb.dbo.backupset table indicating to SQL that a FULL backup just occurred. Well, as we all know and is discussed above, this renders all subsequent SQL DIFFERENTIALs useless as they're based on the most recent FULL.
Take away: If this matters to you, be sure to ask the vendor ahead of time how their snapshots work.
December 13, 2011 at 7:37 am
Henry_Lee (12/13/2011)
When I take this snapshot, HP takes it upon itself to write a record to the msdb.dbo.backupset table indicating to SQL that a FULL backup just occurred. Well, as we all know and is discussed above, this renders all subsequent SQL DIFFERENTIALs useless as they're based on the most recent FULL.
Whether or not a record is written into the msdb backup tables or not is totally irrelevant. SQL does not use those to determine if a full backup is based on a diff or not. That's just a history.
The important point is whether the snapshot backup clears the DIFF maps and changes the differential base in the database header page. If it does that, then the next diff is based on the snapshot, regardless of any rows in msdb's backup tables
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
December 13, 2011 at 7:49 am
Fair enough. I did word that poorly.
I didn't mean literally the presence of the record in the backupset table rendered the subsequent DIFF's useless. I just meant the act of taking a FULL (without COPY_ONLY) rendered the DIFF's useless.
December 13, 2011 at 8:37 am
Henry_Lee (12/13/2011)
I understand this thread is over a year old, but I thought I'd leave some more information for the next person...I'm working through this issue right now. We purchased an HP SAN, specifically the P4500 G2 14.4 TB.
There's two different types of snapshots available - "Application Managed" which quiesces the application, and "Point In Time" which does not. I chose "Application Managed" to insure a consistent view of the data. When I take this snapshot, HP takes it upon itself to write a record to the msdb.dbo.backupset table indicating to SQL that a FULL backup just occurred. Well, as we all know and is discussed above, this renders all subsequent SQL DIFFERENTIALs useless as they're based on the most recent FULL.
Take away: If this matters to you, be sure to ask the vendor ahead of time how their snapshots work.
We were able to resolve this by making change to EMC SAN replication process. EMC SAN was changing the LSN while taking a SAN backup, there is a check flag on EMC console, enabling that resolved our issue. I am sure there should be something on HP side also.
December 13, 2011 at 8:47 am
I am sure there should be something on HP side also.
Thanks for the suggestion. However, there's no option in their console for anything like that and no hint of it in any of their docs. I contacted HP support and they didn't seem to have much knowledge on the subject.
December 14, 2011 at 9:30 am
Henry_Lee (12/13/2011)
I am sure there should be something on HP side also.
Thanks for the suggestion. However, there's no option in their console for anything like that and no hint of it in any of their docs. I contacted HP support and they didn't seem to have much knowledge on the subject.
Did you check with them if there backups are making change to LSN's?
December 14, 2011 at 10:14 am
making change to LSN's?
I'm not really sure what you mean by that. Changing the LSN of what specifically?
My understanding is the DIFFERENTIAL decides what to back up based on whether the extent has been been marked as changed. My assumption has been that their snapshot is causing all the bits on the extents to be reset indicating the extents have not changed and thus do not need to be included in the DIFF. I don't see where LSN's come into play.
I was able to escalate this question at HP and will post back my findings.
December 14, 2011 at 10:41 am
Full backups do two things.
Clear all the bits on the DIFF map pages. Set the value of the differential base LSN in the database header. That's the LSN of the full backup and all diff backups taken after that point record that LSN so that it's possible to tell if they belong to the backup used to restore the DB.
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
December 14, 2011 at 11:37 am
Set the value of the differential base LSN in the database header
Ok, I see. Yes, the DifferentialBaseLsn has changed.
This reaffirms the issue that the SAN snapshot is imitating the behavior of a FULL backup.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply