patternsqlMinor
Suggested SQL Server Backup Schedule using Ola Hallengren scripts
Viewed 0 times
suggestedscriptssqlhallengrenusingserverolaschedulebackup
Problem
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:
I am currently planning to use the following schedule for the backups:
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?
DatabaseIntegrityCheck - SYSTEM_DATABASES
DatabaseIntegrityCheck - USER_DATABASES
IndexOptimize - USER_DATABASESI 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:59By 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?
Solution
[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:
D. Update modified statistics on all user databases
-
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.
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.
Code Snippets
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'Context
StackExchange Database Administrators Q#145867, answer score: 5
Revisions (0)
No revisions yet.