Page 115 - DCAP508_DATABASE_ADMINISTRATION

Unit 7: Automating Administrating Tasks

7.4 Maintenance Plans Notes

In earlier versions of SQL Server, you create database maintenance plans by using a single
wizard, and they create multiple agent jobs as an output. These agent jobs themselves contain
command-line parameters that indicate the function to perform. Maintenance plans create a
workflow of the tasks required to make sure that your database is optimized, is regularly
backed up, and is free of inconsistencies. The Maintenance Plan Wizard also creates core
maintenance plans, but creating plans manually gives you much more flexibility. Maintenance
plans create an Integration Services package, which is run by a SQL Server Agent job. These
maintenance tasks can be run manually or automatically at scheduled intervals.
7.4.1 Features

Maintenance plans provide the following features:
Workflow creation using a variety of typical maintenance tasks. You can also create your
own custom Transact-SQL scripts.

Conceptual hierarchies. Each plan lets you create or edit task workflows. Tasks in each
plan can be grouped into subplans, which can be scheduled to run at different times.

Support for multiserver plans that can be used in master server/target server environments.
Support for logging plan history to remote servers.
Support for Windows Authentication and SQL Server Authentication. When possible, use
Windows Authentication.

Maintenance plans only run against databases set to compatibility level 80 or higher. The
maintenance plan designer in SQL Server Management Studio does not display databases set to
compatibility level 70 or lower.
You can migrate database maintenance plans by right-clicking the database maintenance plan
and choosing Migrate.

You must be a member of the sysadmin role to create and manage maintenance plans, and to
view them in Object Explorer. The SQL Server Agent node in Object Explorer is only displayed
for members of the sysadmin fixed server role, SQLAgentReaderRole, SQLAgentUserRole, or
SQLAgentOperatorRole fixed database roles.

Did u know? Members of the db_ssisadmin role and the dc_admin role may be able to
elevate their privileges to sysadmin. This elevation of privilege can occur because these
roles can modify Integration Services packages and Integration Services packages can be
executed by SQL Server using the sysadmin security context of SQL Server Agent. To
guard against this elevation of privilege when running maintenance plans, data collection
sets, and other Integration Services packages, configure SQL Server Agent jobs that run
packages to use a proxy account with limited privileges or only add sysadmin members to
the db_ssisadmin and dc_admin roles.

7.4.2 How to Create a Maintenance Plan?

There are two ways to create a maintenance plan: you can create a plan using the Maintenance
Plan Wizard, or you can create a plan using the design surface. The Wizard is best for creating
basic maintenance plans, while creating a plan using the design surface allows you to utilize
enhanced workflow.

LOVELY PROFESSIONAL UNIVERSITY 109