July 31, 2018 at 1:21 pm
Mike Scalise - Tuesday, July 31, 2018 12:40 PMGail,Thank you. I've taken into account all of the advice. Would you mind taking a quick look to see if you notice anything I'm missing (or doing that I shouldn't be)?
NODE1 (primary), NODE2 (secondary), NPS1 (non-production server)
- Weekly:
> Take a full backup of my database from NODE1
> Restore it to NPS1.
> Run checkdb (physical_only) on NODE1.
> Run checkdb (full) on NPS1.
> Take a full backup of my database from NODE2.
> Run checkdb (full) on NODE2.
- Daily:
> Run checkdb (physical_only) on NODE1
> Run checkdb (physical_only) on NODE2
> Restore full backup + t-log backups to NPS1?
- Hourly:
> Backup t-logs from NODE2.Also, I wasn't sure exactly which part of the daily run you meant when you said "You don't need to do the second part." Do you mean I don't need to restore the full backup + t-log backups to NPS1 or that I don't need to run the full checkdb on NPS1 daily.
Mike Scalise - Tuesday, July 31, 2018 9:48 AM
- Daily: run checkdb (physical_only) on NODE1, restore weekly full backup + t-log backups to NPS1, run checkdb (logical) on NPS1.You don't need to do the second part. You did the checkDB on the weekly backup when it was taken. If it was clean then, it was clean.
As always, thank you.
Mike
I wouldn't bother taking a backup of Node2 at all. You don't need it. You've got the backup of Node1. AG guarantees that the DBs on the two nodes will be the same
As I said earlier, restoring the weekly backup every day and running checkDB is completely pointless. If it wasn't corrupt on the Sunday, it won't somehow have become corrupt on the Wednesday
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 31, 2018 at 2:25 pm
Gail,
Thanks for clarifying the Node2 thing.
Regarding this:
As I said earlier, restoring the weekly backup every day and running checkDB is completely pointless. If it wasn't corrupt on the Sunday, it won't somehow have become corrupt on the Wednesday
I wasn't sure if running checkdb on [the weekly backup + t-log backups] on a daily basis could theoretically identify corruption vs running checkdb on just [the weekly backup] once a week. In other words, are you saying that checkdb only checks for corruption in the full backup and adding in the t-log backups shouldn't ever cause it to become corrupt?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
July 31, 2018 at 2:30 pm
Corruption is (generally) the IO subsystem mangling the data file.
It's not going to then go and write a structurally correct log record that describes that corruption that can then be backed up and applied to an intact DB to damage it.
If the log got corrupt, the log backup would fail. Or fail to restore at worst.
I can think of one situation where corruption could be transferred by a log backup, and it requires bulk-logged recovery model and about 6 things happening in a specific sequence, and it would probably still fail to restore.
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
August 1, 2018 at 12:07 pm
Everyone,
Thank you so much for the feedback--it's incredibly helpful. One more question, only because I'm finding conflicting views. When it comes to index maintenance, would you personally do that before or after the full backup of the primary? I've traditionally done it before the backup. Has the rule of thumb changed over the years, because I'm seeing a lot of posts from 8+ years ago and I don't know whether the recommended approach then still applies.
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
August 1, 2018 at 12:46 pm
Mike Scalise - Wednesday, August 1, 2018 12:07 PMEveryone,Thank you so much for the feedback--it's incredibly helpful. One more question, only because I'm finding conflicting views. When it comes to index maintenance, would you personally do that before or after the full backup of the primary? I've traditionally done it before the backup. Has the rule of thumb changed over the years, because I'm seeing a lot of posts from 8+ years ago and I don't know whether the recommended approach then still applies.
Thanks in advance,
Mike
Never really worried about it one way or the other. I'd for certain make sure I did it before the consistency check, or after the backup, assuming the consistency check occurs before the backup. I wouldn't put it in between the consistency check and the backup. Other than that, it's not something I was ever really concerned with. Now statistics updates, before or after index maintenance, I'd say it depends on how you do your statistics updates. If you're just doing a universal sampled update (and some do), then before you do index maintenance since to do otherwise, you'd be throwing away some detailed statistics from the index rebuilds.
Of course, an argument can be made to stop doing index maintenance (except on columnstore indexes). Jeff Moden has quite a lot to say about it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 1, 2018 at 1:42 pm
Grant Fritchey - Wednesday, August 1, 2018 12:46 PMMike Scalise - Wednesday, August 1, 2018 12:07 PMEveryone,Thank you so much for the feedback--it's incredibly helpful. One more question, only because I'm finding conflicting views. When it comes to index maintenance, would you personally do that before or after the full backup of the primary? I've traditionally done it before the backup. Has the rule of thumb changed over the years, because I'm seeing a lot of posts from 8+ years ago and I don't know whether the recommended approach then still applies.
Thanks in advance,
Mike
Never really worried about it one way or the other. I'd for certain make sure I did it before the consistency check, or after the backup, assuming the consistency check occurs before the backup. I wouldn't put it in between the consistency check and the backup. Other than that, it's not something I was ever really concerned with. Now statistics updates, before or after index maintenance, I'd say it depends on how you do your statistics updates. If you're just doing a universal sampled update (and some do), then before you do index maintenance since to do otherwise, you'd be throwing away some detailed statistics from the index rebuilds.
Of course, an argument can be made to stop doing index maintenance (except on columnstore indexes). Jeff Moden has quite a lot to say about it.
Well, so this is a bit interesting. If I'm doing a full backup of my primary database on a weekly basis and restoring it to another server for the full DBCC CHECKDB, then it wouldn't make much sense to do any kind of stats or index maintenance on that other server... so then, to not put that maintenance work between the consistency check (physical only) and the backup on the primary, it'd have to be something like:
On Sundays (weekly): stats/index maintenance on primary, full backup on primary, checkdb (physical only) on primary......restore the database to that other server and run full checkdb,
Does that seem right to you?
Also, I'm using Ola's IndexOptimize script, and I'm 99% sure he takes into consideration that an index rebuild also updates stats, so I'm not too worried about the order or throwing away fresh statistics.
Mike Scalise, PMP
https://www.michaelscalise.com
August 1, 2018 at 1:49 pm
Actually, based on what you said, I suppose it could also go like this:
full backup on primary, checkdb (physical only) on primary, stats/index maintenance on primary......restore the database to that other server and run full checkdb
Mike Scalise, PMP
https://www.michaelscalise.com
August 1, 2018 at 1:57 pm
CheckDB before backup. No point in taking the backup if the DB is damaged or hardware is failing
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
August 1, 2018 at 2:29 pm
GilaMonster - Wednesday, August 1, 2018 1:57 PMCheckDB before backup. No point in taking the backup if the DB is damaged or hardware is failing
Gail,
I appreciate you chiming in! That was always my general view. However.........what are your thoughts on this?
https://dba.stackexchange.com/questions/163509/full-backup-before-and-after-checkdb
There is no need to take a FULL backup before and after running
DBCC CHECKDB. It is recommended that your FULL backup go before theDBCC CHECKDBso that you know exactly which FULL backups don't contain database corruption ifDBCC CHECKDBever fails due to corruption. The FULL backup before the last successfulDBCC CHECKDBis one without corruption.To recover from corruption, you can use the FULL+DIFF+LOG chain or FULL+LOG chain. Pick the FULL backup from before the last successful
DBCC CHECKDB. If there was a DIFF before the last successfulDBCC CHECKDB, then you can use that too. But a DIFF after the last successfulDBCC CHECKDBcould have database corruption, depends when the DIFF occurred and when the corruption occurred.
and some follow-up comments on that thread:
Alternatively, if you run CHECKDB immediately before the full backup, then you know the backup is good. Saves you the unnecessary step of backing up a corrupt database. But taking one both before and after is definitely overkill – Gareth Lyons Feb 7 '17 at 18:44
@GarethLyons, a FULL backup after DBCC CHECKDB runs, even if it's immediately after, could contain corruption. It's unlikely, but the corruption could have slipped in after the page was checked and before the FULL backup backed up that page. The bigger the database, the higher the likelihood of it occurring. Still would be rare. But that's why the FULL backup should occur before DBCC CHECKDB. – Tara Kizer Feb 7 '17
Mike Scalise, PMP
https://www.michaelscalise.com
August 1, 2018 at 2:56 pm
My thoughts are as I posted earlier.
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
August 1, 2018 at 3:49 pm
GilaMonster - Wednesday, August 1, 2018 2:56 PMMy thoughts are as I posted earlier.
Fair enough. Thank you.
Mike Scalise, PMP
https://www.michaelscalise.com
August 1, 2018 at 4:47 pm
Mike Scalise - Wednesday, August 1, 2018 3:49 PMGilaMonster - Wednesday, August 1, 2018 2:56 PMMy thoughts are as I posted earlier.Fair enough. Thank you.
Just FYI...That isn't a general recommendation, that's the posters recommendation. If you want to follow Paul Randal's advice, his recommendation is to do the checks before the backup:
Oh no – my backup is corrupt too! Help!
Since you can do a backup if there is corruption, I'd rather know about that corruption before doing the backup and address the issue sooner rather than later.
Not like it matters in all environments but for some, I'd rather address the corruption during a maintenance window instead of at the end of that window.
Sue
August 1, 2018 at 6:57 pm
Ok, just because this has really been enlightening and I'm curious to know more, let me throw this out there.
If the strategy is to run checkdb before the backup (so as to not back up a corrupt database--which makes a lot of sense to me), then is checkdb with phsyical_only good enough for this purpose? Does it have to be full checkdb?
The reason I ask is because earlier in this discussion, we had talked about offloading the primary node in my AOAG by restoring a backup of the database to another server and running a full checkdb on that other server.
Obviously I can't have it both ways unless I can do checkdb physical_only on the primary, then backup, then restore to other server, then full checkdb on that other server. However, if that full checkdb on the other server uncovers corruption, well then, that means I've backed up a corrupt database............
Sorry if I'm being hard headed...just trying to see if it's a choice between not taxing the primary vs ensuring I don't back up a corrupt database--or if there's a way to have my cake and eat it too.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
August 1, 2018 at 7:57 pm
Mike Scalise - Wednesday, August 1, 2018 6:57 PMOk, just because this has really been enlightening and I'm curious to know more, let me throw this out there.If the strategy is to run checkdb before the backup (so as to not back up a corrupt database--which makes a lot of sense to me), then is checkdb with phsyical_only good enough for this purpose? Does it have to be full checkdb?
The reason I ask is because earlier in this discussion, we had talked about offloading the primary node in my AOAG by restoring a backup of the database to another server and running a full checkdb on that other server.
Obviously I can't have it both ways unless I can do checkdb physical_only on the primary, then backup, then restore to other server, then full checkdb on that other server. However, if that full checkdb on the other server uncovers corruption, well then, that means I've backed up a corrupt database............
Sorry if I'm being hard headed...just trying to see if it's a choice between not taxing the primary vs ensuring I don't back up a corrupt database--or if there's a way to have my cake and eat it too.
Thanks,
Mike
You just like the questions where you can get different answers. But they are interesting problems.
When you think about it, the ideal would be to run full dbcc checkdb on any database that is or could become the primary. I think with any other process, you have potential issues. But most can't "afford" to do that maintenance on all the primaries and replicas daily and are looking for some processes to be offloaded. I've read about different approaches - some offload nothing, some offload everything, some backup the primary and restore to a lower environment for dbcc checkdb, you have some good ideas with some of what you're thinking of. One of the more interesting ones is where they offload everything to the secondaries but failover weekly so it's the former primaries are now the secondaries with the checkdb and backup. It's a challenge as any can be the primary at any time. You have to go through what you realistically can do, all the risks with the different combinations and come up with what you think is best for the business needs/requirements.
Sue
August 1, 2018 at 8:12 pm
Sue_H - Wednesday, August 1, 2018 7:57 PMMike Scalise - Wednesday, August 1, 2018 6:57 PMOk, just because this has really been enlightening and I'm curious to know more, let me throw this out there.If the strategy is to run checkdb before the backup (so as to not back up a corrupt database--which makes a lot of sense to me), then is checkdb with phsyical_only good enough for this purpose? Does it have to be full checkdb?
The reason I ask is because earlier in this discussion, we had talked about offloading the primary node in my AOAG by restoring a backup of the database to another server and running a full checkdb on that other server.
Obviously I can't have it both ways unless I can do checkdb physical_only on the primary, then backup, then restore to other server, then full checkdb on that other server. However, if that full checkdb on the other server uncovers corruption, well then, that means I've backed up a corrupt database............
Sorry if I'm being hard headed...just trying to see if it's a choice between not taxing the primary vs ensuring I don't back up a corrupt database--or if there's a way to have my cake and eat it too.
Thanks,
Mike
You just like the questions where you can get different answers. But they are interesting problems.
When you think about it, the ideal would be to run full dbcc checkdb on any database that is or could become the primary. I think with any other process, you have potential issues. But most can't "afford" to do that maintenance on all the primaries and replicas daily and are looking for some processes to be offloaded. I've read about different approaches - some offload nothing, some offload everything, some backup the primary and restore to a lower environment for dbcc checkdb, you have some good ideas with some of what you're thinking of. One of the more interesting ones is where they offload everything to the secondaries but failover weekly so it's the former primaries are now the secondaries with the checkdb and backup. It's a challenge as any can be the primary at any time. You have to go through what you realistically can do, all the risks with the different combinations and come up with what you think is best for the business needs/requirements.Sue
Well said. I think I know what is the most realistic for us, but you're right--I do need to weigh all of the factors to know for sure. Thank you for your response, Sue!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply