Chapter 5

Automating SQL Server


  • Automate Common Maintenance Activities Using Maintenance Tasks
  • Schedule Jobs to Perform Maintenance Activities Using SQL Server Agent
  • Secure the Jobs You Create Using SQL Server Agent Security
  • Configure SQL Server Agent to Meet Your Needs
  • Set Up Database Mail to Enable the SQL Server Agent to Send Notifications
  • Schedule Jobs Across Multiple Servers Using the Multi Server Admin Capabilities

Much of the work that a database administrator does is repetitive: backing up databases, rebuilding indexes, and checking for file sizes and disk-space availability. Responding to events such as a full transaction log being out of disk space may also be part of DBA daily life. The problems grow rapidly with the number of servers you must administer. Automating this work is more than a convenience; it is a requirement for enterprise systems.

Two features in SQL Server 2012 come to the rescue of the DBA: Maintenance Plans and SQL Server Agent. Maintenance Plans enable you to automate the routine maintenance activities for a database. Backups, database integrity checks, and index maintenance tasks can be automated with Maintenance Plans. The Maintenance Plan Wizard makes it easy to create Maintenance Plans. SQL Server Agent enables you to manually create a schedule of jobs to be run on a SQL Server, further enhancing the ability of the DBA to automate routine activities.


Maintenance Plans are a quick-and-easy way to automate ...

Get Professional Microsoft SQL Server 2012 Administration now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.