Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 321

Designing maintenance tasks on a
mission-critical environment
The key responsibility for a database administrator is to handle the data recovery strategy.
There are varieties of tasks included in a Disaster Recovery (DR) plan that allows the restore
of databases with minimum data loss The architecture and mechanics of backup and restore
operations are fundamental to the maintenance task process.
SQL Server includes maintenance plans that provide various database maintenance tasks
including backup, optimization, and integrity checks. There are multiple backup options
available such as full database backup, differential backups, and transaction log backups.
Similarly, the database optimization tasks - reorganizing and rebuilding of indexes-are
essential to keep up the performance in addition to the integrity checks such as database
consistency checks (DBCC). Database maintenance plans have been an integral part since
SQL Server version 6.5 and are executed and scheduled using the SQL Server agent service.
From SQL Server 2005, the maintenance plans are executed in the form of an SSIS package
and scheduled using the SQL Server agent service.
In this recipe, we will work on steps in designing maintenance tasks on a mission-critical
environment.
Getting ready
Ensure that the following prerequisites are followed in designing maintenance tasks on a
mission-critical data platform:
F The account used to create the maintenance plans is a part of the sysadmin fixed
server role group.
F The login used to schedule and execute the maintenance plan should have relevant
permissions on the server and database.
F If the backups are stored on a network file server then the login must have write
permissions on the file share.
F The recovery strategy for the user database is dictated by the recovery model; this
property dictates the level of logging and log retention.
F In order to design a database maintenance plan to perform transaction log backups,
the database recovery model must be FULL .
F The database maintenance plan strategy may vary depending upon the application
functionality and time frame to perform maintenance tasks; as a reference, the
typical maintenance plan strategy will be as follows:
Daily database backup—differential
Weekly database backup—full