August 2, 2018 at 2:44 am
Mike Scalise - Wednesday, August 1, 2018 6:57 PM--or if there's a way to have my cake and eat it too.
Backup WITH CHECKSUM, and most logical corruptions would fail the backup. Most, not all, so that full checkDB is still needed.
There's no way to be completely certain that everything's fine in all cases and at all points
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 2, 2018 at 4:36 am
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
Since we're offloading the logical checks in this scenario, yeah, only the physical checks would be run before the backup is taken. However, I'd run those physical checks before the backup is taken. Yes, because we're offloading the logical checks, we could have a logical consistency error in the backup. However, that's something we'd have to deal with. What you don't want is no check at all, then a backup.
----------------------------------------------------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 2, 2018 at 8:32 am
Grant Fritchey - Tuesday, July 31, 2018 5:58 AMAlso, if you're doing restores of the databases to non-production environments, you can offload the logical consistency checks to those databases.
Don't you have to be careful here of not falling foul of licensing terms by doing "Production offload"?
https://www.brentozar.com/archive/2015/04/microsoft-sql-server-licensing-simplified-into-7-rules/
August 2, 2018 at 8:49 am
Beatrix Kiddo - Thursday, August 2, 2018 8:32 AMGrant Fritchey - Tuesday, July 31, 2018 5:58 AMAlso, if you're doing restores of the databases to non-production environments, you can offload the logical consistency checks to those databases.Don't you have to be careful here of not falling foul of licensing terms by doing "Production offload"?
https://www.brentozar.com/archive/2015/04/microsoft-sql-server-licensing-simplified-into-7-rules/
That's an interesting question and I'm not sure I have the answer. I do know that we were audited while doing something like this and Microsoft was OK with it. We're testing after all, not running it as production.
----------------------------------------------------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 2, 2018 at 9:17 am
Hmm. It's really hard to tell for sure but we were specifically told not to do it where I worked previously. More here:
- With regards to licensing, I recently spoke to Data Platform MVP Joey D’Antoni to get better clarification about required licensing. It appears that if you’ve got Software Assurance, creating a dedicated testing server for your backups would not require additional licenses, HOWEVER, you will have to skip the DBCC CHECKDB by specifying the -NoCheck parameter because CHECKDB is considered a “production offload process.”
- Alternatively, the SQL Server 2016 licensing model basically says that “anything that isn’t prod is free.” This means that, even without Software Assurance, you can use SQL Server 2016 as your test restore server without additional license requirements. But again, CHECKDB is considered production and is not covered under this licensing model.
August 2, 2018 at 9:21 am
More here:
The most common response is to offload the integrity checks to a restored database backup, on a secondary, production-licensed server. However, if you already have SQL Clone set up, for database provisioning, then an alternative might be to offload DBCC checks, and possibly other administrative jobs, to a clone database on a server licensed as per the production server
.
https://www.red-gate.com/hub/product-learning/sql-clone/sql-clone-quick-tip-offloading-dbcc-checks
August 13, 2018 at 7:00 am
Hey all,
Thanks again for all of the great discussion. Not to beat a dead horse here, but I'm curious--let's say that my primary and secondary replicas are not super taxed and either could handle the dbcc checks, the full and t-log backups, etc. -- is there a good reason NOT to perform all of these tasks on each of the replicas?
And if that does make sense, I've tried to run Ola's scripts on the secondary and it seems that even if I have my backup preference set to "Any Replica", it doesn't take backups on the secondary replica. I'm not sure if it sees that the backups can be taken on the primary and that is enough that it simply won't do a copy-only backup on the secondary. I've tried to add "AVAILABILITY_GROUP_DATABASES" to the command (even though I didn't think I needed to)--and it didn't make a difference. Am I missing something fundamentally with AOAG and backups that's preventing me from doing this (e.g., something like if backups are happening on one replica, you're not allowed to do the exact same backups on the other replicas)?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
August 13, 2018 at 8:22 am
something like if backups are happening on one replica, you're not allowed to do the exact same backups on the other replicas
So you want to take normal backups on the Primary, and additional (you hope identical but copy-only) backups on the Secondary replicas? No, you can't do that.
August 13, 2018 at 8:40 am
To be honest, I wouldn't change from the original backup plan except to do the transaction log backups every half hour. Restoration of a database that only has weekly backups can take a huge amount of time without DIFFs and, just because you're doing the "always on" thing, doesn't mean that it will be. 😀 Restores are the final line of defense when everything else has gone wrong. Don't short-sheet yourself.
I recently went through such a thing. To make a very long story shorter, we needed to recover a shedload of data for just one table. I went to our network folks to have them do the magic thing of a nearly instant restore of the database to a special server that we have for extraordinary circumstances. The had misconfigure the magic methods and the data simply wasn't available. Thank goodness I stood my ground more than 2 years ago when I told them that, despite their fine promises, that I wasn't backing off on the self-imposed rigors of doing backups not was I going to allow them to backoff on the rigors of backups to tape.
--Jeff Moden
August 13, 2018 at 10:08 am
Mike Scalise - Monday, August 13, 2018 7:00 AM-- is there a good reason NOT to perform all of these tasks on each of the replicas?
Yes. Other than CheckDB you can't.
You definitely do not want to try and take transaction log backups on all replicas. The chaos of trying to get them in order to restore would be horrible.
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 13, 2018 at 12:18 pm
Thanks for the responses. My thought was to just implement a complete backup strategy (checkdb, full, diff, t-log) for each of the databases (as if they weren't even in an AG)--that way if either one ever needed to be restored, they would each independently be covered--but I see now that that type configuration in an AG just isn't supported. I think I'm going to stick with checkdb and full, diff, t-log backups on the primary (since it can handle the overhead), with the possible change of taking the t-log backups every 30 minutes--and leave the secondary alone (unless it becomes the primary and therefore subject to all of these tasks).
Thanks again,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
August 14, 2018 at 4:30 am
You still want to run CheckDB on the secondaries.
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 19, 2018 at 8:27 am
Mike Scalise - Monday, July 30, 2018 1:48 PMHi,I'd be interested to hear how most of you have your backups set up. I know a lot of it depends on RPO/RTO etc. but I've just implemented Always On Availability Groups and I think it's a good time to re-visit everything related to backups. I currently have three backup jobs running on the primary (using Ola Hallengren's scripts):
1. Database Maintenance - HOURLY - Transaction Log backups.
2. Database Maintenance - DAILY - Full backup of system databases, differential backup of user databases.
3. Database Maintenance - WEEKLY - Cleanup, user and system database integrity checks, index and statistics maintenance, and full backups.
StepNo StepName
1 sp_delete_backuphistory
2 sp_purge_jobhistory
3 CommandLog Cleanup
4 Output File Cleanup
5 DatabaseIntegrityCheck - USER_DATABASES
6 IndexOptimize - USER_DATABASES
7 DatabaseBackup - USER_DATABASES - FULL
8 DatabaseIntegrityCheck - SYSTEM_DATABASES
9 DatabaseBackup - SYSTEM_DATABASES - FULLI know with moving the backups to the secondary, we won't be doing DIFFs anymore. So, aside from removing that step from the daily backups, are there any other changes that you would make to this configuration? Also, do most of you compress your backups?
Thanks in advance. I look forward to hearing your thoughts.
Mike
Daily integrity check is the ideal, but I personally cannot operate that way for some of our MSSQL stacks. All due to poor planning from the beginning (before my time ;)), but some of our DB's are TB's and integrity checks take over 12 hours and cut into business operation. Mind you some of these DB's are huge just due to horrible design (stale, old data that has no plan to be archived, horrible choice of data types, redundancy, wide indexes, etc.). The tech leader of that particular application is unwilling to devote both more resources to the box and is very slow to adopt our numerous suggestions to make the database more efficient in the long run. Off topic, but I digress. Weekly integrity check seems to be the norm for most organizations I have worked for in the past, if they do integrity checks at all. I think it largely depends on your current constraints, size of DB, resources, volume of DML - all those things .Also recoverability requirements - if the data is not deemed critical to the organization, then a daily integrity check may simply be overkill.
Regarding backups, we are just doing the following for now:
1. Daily fulls
2. transaction log every 15 minutes
I just stick with base SQL maintenance plans, they do the trick for our purposes.
We upload the volume daily to AWS S3, which further contains rules to archive to AWS Glacier after a certain period of time, depending on the application's recoverability requirements.
Ideally I like:
1. Weekly fulls
2. Daily differentials
3. transaction log every 15 minutes
But given that we are a cloud configuration and we have that extra layer to go through incase of a disaster, we decided to just stick with daily fulls for now and retain the simplicity of recoverability at the expense of increased backup storage requirements (and daily backup times)
August 19, 2018 at 6:50 pm
eshults5 - Sunday, August 19, 2018 8:27 AMMike Scalise - Monday, July 30, 2018 1:48 PMHi,I'd be interested to hear how most of you have your backups set up. I know a lot of it depends on RPO/RTO etc. but I've just implemented Always On Availability Groups and I think it's a good time to re-visit everything related to backups. I currently have three backup jobs running on the primary (using Ola Hallengren's scripts):
1. Database Maintenance - HOURLY - Transaction Log backups.
2. Database Maintenance - DAILY - Full backup of system databases, differential backup of user databases.
3. Database Maintenance - WEEKLY - Cleanup, user and system database integrity checks, index and statistics maintenance, and full backups.
StepNo StepName
1 sp_delete_backuphistory
2 sp_purge_jobhistory
3 CommandLog Cleanup
4 Output File Cleanup
5 DatabaseIntegrityCheck - USER_DATABASES
6 IndexOptimize - USER_DATABASES
7 DatabaseBackup - USER_DATABASES - FULL
8 DatabaseIntegrityCheck - SYSTEM_DATABASES
9 DatabaseBackup - SYSTEM_DATABASES - FULLI know with moving the backups to the secondary, we won't be doing DIFFs anymore. So, aside from removing that step from the daily backups, are there any other changes that you would make to this configuration? Also, do most of you compress your backups?
Thanks in advance. I look forward to hearing your thoughts.
Mike
Daily integrity check is the ideal, but I personally cannot operate that way for some of our MSSQL stacks. All due to poor planning from the beginning (before my time ;)), but some of our DB's are TB's and integrity checks take over 12 hours and cut into business operation. Mind you some of these DB's are huge just due to horrible design (stale, old data that has no plan to be archived, horrible choice of data types, redundancy, wide indexes, etc.). The tech leader of that particular application is unwilling to devote both more resources to the box and is very slow to adopt our numerous suggestions to make the database more efficient in the long run. Off topic, but I digress. Weekly integrity check seems to be the norm for most organizations I have worked for in the past, if they do integrity checks at all. I think it largely depends on your current constraints, size of DB, resources, volume of DML - all those things .Also recoverability requirements - if the data is not deemed critical to the organization, then a daily integrity check may simply be overkill.
Regarding backups, we are just doing the following for now:
1. Daily fulls
2. transaction log every 15 minutesI just stick with base SQL maintenance plans, they do the trick for our purposes.
We upload the volume daily to AWS S3, which further contains rules to archive to AWS Glacier after a certain period of time, depending on the application's recoverability requirements.
Ideally I like:
1. Weekly fulls
2. Daily differentials
3. transaction log every 15 minutesBut given that we are a cloud configuration and we have that extra layer to go through incase of a disaster, we decided to just stick with daily fulls for now and retain the simplicity of recoverability at the expense of increased backup storage requirements (and daily backup times)
Thank you for sharing all of this information. It's always helpful to see what others are doing!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply