You are a database administrator for Verigon Corporation. You maintain all the SQL Server 2008 databases forthe company. The company receives data regarding the processed orders from the various branches of thecompany. After the data is verified by the audit department, the appropriate tables in the database are updatedwith the data by 6 A.M. You are required to perform the following tasks after the tables have been updated:
? Rebuild the indexes in the tables at 8:00 A.M.
? Update the statistics for the tables and indexes in the database at 10:00 P.M.
? Perform a differential backup of the database at 7:00 P.M.
Which method should you use to perform the tasks by using the least administrative effort?
A.
Create a new job for all the specified tasks.
B.
Create a new maintenance plan for the database by using the Maintenance Plan Wizard.
C.
Create new maintenance plans for each of the specified tasks by using the Maintenance Plan Wizard.
D.
Create a new maintenance plan for the database using the design surface.
Explanation:
You should create a new maintenance plan for the database by using the Maintenance Plan Wizard . The Maintenance Plan Wizard creates the necessary jobs to perform the specified tasks. With SQL Server 2005Service Pack 2 and SQL Server 2008, a maintenance plan can schedule a group of tasks to be performed at aspecific time or you can use a specific schedule for each task. You can create a maintenance plan using the Maintenance Plan Wizard or using the design surface, but using the Maintenance Plan Wizard
would involvethe least administrative effort. In this scenario, you have tasks that should run at different times. Therefore, youshould create a separate schedule for each task in the maintenance plan. You should not create new maintenance plans for each of the specified tasks by using the Maintenance PlanWizard . This would require more administrative effort than creating a single maintenance plan. With SQL Server2005 Service Pack 2 and SQL Server 2008, a maintenance plan can be used to schedule the different tasks to beperformed at a specific time or you can use a specific schedule for each task. You should not create a new job for all the specified tasks because you cannot create a single job and to performtasks that are scheduled to run at different times. You can create a job containing different tasks, but all the taskswithin the job are scheduled to run at the same time. To create jobs for tasks scheduled to run at different times,you would have to create a separate job for each task.You should not create a new maintenance plan for the database using the design surface. Using the designsurface would involve more administrative effort than using the Maintenance Plan Wizard . The MaintenancePlan Wizard
is beneficial if you must perform basic maintenance tasks. In this scenario, the tasks to beperformed are basic maintenance tasks. Therefore, the use of the Maintenance Plan Wizard is recommended.You would use the design surface, if you needed to create more complex maintenance plans that needed to useenhanced workflow features.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Maintain a database by using maintenance plans.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Administration: How-to Topics > Server Management How-to Topics > SQL Server Management Studio How-toTopics > How To: Create a Maintenance Plan TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Databases > Maintaining Databases (Database Engine) >Maintenance Plans