Save to My DOJO
Organizations know that they need to protect their essential SQL databases. Administrators can leverage Microsoft SQL Server’s maintenance plan feature to automatically back up their databases and automate other tasks that keep the database performing as fast as possible. It provides significant protection with its ease of use and no requirement for additional software or licenses.
However, enterprise administrators must follow best practices to ensure that their live data remains consistent and their backups recoverable. This article will help you understand how to put SQL Server’s native tools and third-party solutions to work for you.
SQL Backups with the SQL Server Maintenance Plan
SQL allows admins to schedule series of tasks that optimize, back up, and keep databases consistent. These tasks can operate independently or as part of a workflow. The Maintenance Plan Wizard utility guides users through configuration. The available tasks:
- Check Database Integrity – runs an internal consistency check against the data and data pages. This is an important operation and should be run regularly, but it is resource-intensive and should not run at the same time as a backup.
- Shrink Database – reduces the size consumed by data files by removing empty database pages. While this operation reduces the size of the backup on disk, it incurs a performance hit and causes fragmentation. Unless you work with sparse databases or have recently deleted a lot of data, you will likely not reclaim enough space to justify the operation. Do not run shrink in a backup plan workflow. Make sure to reorganize the index after shrinking.
- Reorganize Index – sorts indexes and reduces index fragmentation to make queries operate more efficiently. Fast and can run in a daily plan.
- Rebuild Index – drops and recreates indexes as new, completely unfragmented entities. Slower and more resource-intensive than Reorganize Index and can only run online in the Enterprise Edition. Run no more than weekly.
- Update Statistics – provides the latest information about how the data is distributed which will make queries faster. It should be used immediately after Reorganize Index, preferably in the same workflow. Rebuild Index updates statistics automatically, by default.
- History Cleanup – deletes old metadata from maintenance plan tasks, such as the SQL backup history. Based on the compliance needs of your industry, you may need to retain the proof of your backup history for several years, so you may or may not wish to use this task. Does not impact backed-up SQL data.
- Execute SQL Server Agent Job – lets you trigger any previously-created SQL Server Agent job(s)or T-SQL statement(s) on a schedule. This enables you to add custom steps during maintenance workflows.
- Back Up Database (Full) – triggers the built-in SQL Server backup. It calls Volume Shadow Copy Service (VSS) to quiesce the data, flush any existing transactions, and take a completely consistent backup. Run full backups regularly.
- Back Up Database (Differential) – triggers a partial backup which saves changes made since the last full backup. Differential backups run more quickly than a full backup but take longer to restore since multiple files need to merge during recovery. Use your recovery time objectives (RTO) and recovery point objectives (RPO) to decide whether to use the differential mode.
- Back Up Database (Transaction Log) – protects only the transaction logs of a SQL database in Full Recovery mode. These logs enable a granular rebuild of a corrupted database. The transaction log allows you to roll through transactions to troubleshoot problems or restore to a very specific point. Transaction backups finish quickly and can provide very short RPOs, so use them frequently between full backups.
- Maintenance Cleanup –run at the end of any maintenance to delete unneeded files.
SQL Backups with Third-Party Providers
SQL maintenance plans serve as an effective backup strategy but can provide some challenges at scale. You may prefer third-party backup tools that offer a universal view of the backup schedule across all databases and the rest of the datacenter. Maintenance plans also lack business logic, so they may run inefficient operations. Native SQL backup also has limitations on backup storage types. For example, it doesn’t support remote tape systems.
Third-party providers usually separate the backup management server from the SQL server to reduce the impact on database performance. Many offer advanced security features like backup encryption. Some even offer item-level recovery. However, deep integration can tie your database backup into these third-party tools in a way that might challenge your comfort level.
As a compromise, you can combine native SQL backup with your third-party application. Use a maintenance plan to write .bak files to a location where your backup and replication programs can find them. That won’t give you some of the fancier capabilities, but you’ll never have to worry about having an unsupported configuration.
Scheduling SQL Backup Plans
SQL permits scheduling through either a maintenance plan or a third-party backup provider. If you need more time to perform full backups or you want to reduce their performance impacts, you can intermix them with differential backups. At the other end, take transaction log backups frequently to maximize RPOs. You can schedule them as often as every 15 minutes. The best practices for scheduling your SQL backups include:
- Control the order of maintenance plan workflows by placing tasks that touch the same data within the same schedule. This allows you to manage the tasks within a single interface.
- Run tasks during off-hours, especially those which are resource-intensive like index rebuilds or checking the database integrity.
- Stagger the schedules of workflows so that they run at different times. This balances the workload across the backup infrastructure.
- Run a Back-Up Database (Full) task before a differential or transaction log backup. These two types depend on the latest full backup.
- Have the native tool save backups to disk, then have your regular backup tool capture the files.
- Schedule regular deletion of on-disk backups that you no longer need to conserve disk space. Administrators commonly keep the last two full backups and the transaction logs and differentials that depend on them.
Regardless of which backup tool(s) you use, make sure that the plan accounts for the unique needs of each database. Consider acceptable levels of data loss to determine the backup frequency (RPO). Make sure you know the size and growth rate of database and backup files. To plan your recovery time objective (RTO), think about your storage media. Leverage scheduling to minimize cost and maximize recovery speed. Remember the difficulty and amount of time necessary to transport data to a recovery site. Familiarize your staff with operational procedures. Most importantly, make sure that you regularly test recovery to ensure consistency of your data.
Not a DOJO Member yet?
Join thousands of other IT pros and receive a weekly roundup email with the latest content & updates!