2

We are building out a new AlwaysOn SQL Server 2016 database cluster and I am in the middle of setting up the backups using Ola Hallengren (https://ola.hallengren.com) backup scripts and I was looking for some advice when best to run the the following tasks:

DatabaseIntegrityCheck - SYSTEM_DATABASES
DatabaseIntegrityCheck - USER_DATABASES
IndexOptimize - USER_DATABASES

I am currently planning to use the following schedule for the backups:

DatabaseBackup - SYSTEM_DATABASES - FULL @ 23:30 every day
DatabaseBackup - USER_DATABASES - FULL @  23:00 every day
DatabaseBackup - USER_DATABASES - DIFF @ Every hour starting at 00:00 to 22:00
DatabaseBackup - USER_DATABASES - LOG @ Every 5 mins starting at 00:03 to 23:59:59

By reading the suggestion was to do the database integrity check before the backups so I was think maybe 30 mins before the main backup and then run the index optimize when a known time of little load. Would that make sense as a schedule for SQL Server backups?

4

[T]he suggestion was to do the database integrity check before the backups so I was think maybe 30 mins before the main backup and then run the index optimize when a known time of little load. Would that make sense as a schedule for SQL Server backups?

A few thoughts:

  • Index maintenance may not be necessary as often as you think. Usually index fragmentation has a very low impact on performance with modern hardware, especially when you take into account that all logs involved with index rebuilds have to be shipped to your Availability Group secondaries. I would suggest considering a daily (or even more frequent) statistics update, using Ola's maintenance solution, and make sure that you use this example from his site (take note of the NULLs for index fragmentation):

D. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
  • If you find that index fragmentation does cause performance problems, or you just want to keep them in decent shape, you can schedule that on a less frequent basis. Depending on the database and index size, maybe once a week or once a month.

  • This leads me into answering your "guesstimate" of 30 minutes before backups for consistency check. You won't know until you actually get the process running. If your monthly index maintenance takes three hours, you'll want to kick that off earlier than the statistics update. If CHECKDB takes five hours, then schedule it to run on an AG secondary, or restore the database to a stand-alone server and run it there (search for "offloading CHECKDB").

  • Differential backups every hour is overkill, in my opinion. I generally do these once a day, and possibly twice a day depending on the environment. You have your log backups, and you need to keep in mind that differentials are not incremental. They will continue growing until the next full backup, because they keep track of all changes since the last full backup. In some cases, differential backups can exceed the size of the full backup. In those cases, you'll need to evaluate whether a full backup is better than a differential.

  • Do you have enough disk space to keep 5-minute transaction log backups? Is that what the business needs (SLA, RTO, RPO)? Can you get away with 6 minutes? Over a three-month period, that extra minute makes a big difference in the number of files you're storing. Often I find most of my clients satisfied with a trade-off of 10- or 15-minute log backups.

  • Full backups could be done daily, but depending on your retention policy, as well as Recovery Time Objective and Recovery Point Objective, you might be more than happy with a weekly full backup of user databases, a daily full backup of system databases, and the differentials can fill in the gaps where necessary.

Ultimately, this all depends on what the business needs to satisfy service level agreements internally and externally. If a reporting / OLAP style database can be regenerated in two hours from an OLTP source, then you don't need to worry as much about it as the OLTP database.

I hope this helps you come up with an appropriate strategy for your business. That's what should drive these decisions.

  • Just a little comment to what you said about CHECKDB: I have read recommandations that CHECKDB needs to be run on all replicas, as there are different IO subsystems. – GHauan Jan 8 '18 at 12:01
  • Correct. Note that my suggestion to run it on a secondary was due to a maintenance window that wasn't big enough to run on the primary. – Randolph West Jan 9 '18 at 6:24
0

How big are your databases? And how long will you be keeping the backups for?

DatabaseBackup - SYSTEM_DATABASES - FULL @ 23:30 every day

This is a must, because your master database can't have DIFF backups, and so this is the only way to have a daily backup of it.

DatabaseBackup - USER_DATABASES - FULL @  23:00 every day
DatabaseBackup - USER_DATABASES - DIFF @ Every hour starting at 00:00 to 22:00

I usually do this once per week and once per day respectively. But this is where it gets complicated.

Most databases will handle this fine, but you might have some 10TB monsters that only have a few hundred MB of changes per day which will require special handling. Alternately you might have 1TB databases that completely cycle each day and where the DIFF backups are a waste of time.

My opinion is to standardise and then aggregate and graph out the msdb.dob.backupset data a week later to identify the worst of the worst, then set special steps just for those. Create a review process document and scripts to run once a month if you're neat and tidy. There are scripts to determine this but the best use of time is to suck it and see.

DatabaseBackup - USER_DATABASES - LOG @ Every 5 mins starting at 00:03 to 23:59:59

This is fine but generates 288 log files per database per day even if nothing is happening at all (75k uncompressed, 6k compressed). Those numbers quickly add up depending on the size and retention period of your environment.

But the worst part is restoring them. I don't know if you've ever restored 288 log file backups in a row (with a script of course) but even if there's nothing in them, they'll take more than just a few seconds to go through each. I don't know where the bottleneck is (the SAN, SMB protocol, network latency, the server, the SQL restore code, or what) but believe me it adds up and usually at the worst time when, "The application just corrupted everything and we need a complete restore right now!"

Even then. Even if you just have one server with 50 databases. If something happens to your server and you have to restore 14,400 log backups for that day on top of everything else... the scripting isn't a problem. The delays are!

Of course weigh this against, "You lost 15 minutes or data" vs "You lost 5 minutes of data", and your results may vary.

Whichever way you go one thing that will hit you is the stupid text logs that build up. Yes, I will call them stupid because it's a pain to find an error in CommandLog then have to scan through a million text files sitting in that directory just to work out what happened.

Enjoy though. We use Hallengren. But if we could redo it today we might switch to Minion. A lot of the same physical problems still apply but I believe administration and troubleshooting may be easier.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.