Automation and maintenance go hand in hand. You can automate many routine database administration tasks, most of which have to do with maintenance issues, such as backing up databases or running consistency checks. Automation allows you to increase productivity, complete tasks while away from your computer, and more. You can configure the server to monitor processes and user activities, to check for errors, and to alert you when related events occur. If you configure alerts properly, Microsoft SQL Server 2005 can monitor itself and you can focus on other areas of administration. You can also schedule jobs to automate routine administration tasks. You can configure these jobs to run once or on a recurring basis, such as once a week or the third Tuesday of every month.
SQL Server 2005 has four main database automation and maintenance components:
Typically, when you want to use these automation and maintenance features, you will select the following configurations:
Configure Database Mail for msdb and other databases. The msdb database is used by SQL Server Agent for scheduling alerts and jobs and to track operators. When you enable Database Mail, Database Mail objects are created in the msdb database. These objects allow msdb to act as a mail host database for sending alerts, notifications, and other types of messages.
Configure the SQL Server Agent service for your environment. Typically, you want to ensure that the service is automatically started with the operating system, that it uses the correct startup account, and that it has the correct mail profile so it can be used with Database Mail.
Configure SQL Server Agent alerts, jobs, and operators to enable automatic alerts and scheduled jobs. Alerts are automatically generated messages that bring an error or issue to the attention of an administrator or other user. Jobs are scheduled tasks that run automatically when triggered or at a specific interval. Operators are individuals to whom you want to send alerts and notifications.
Configure Database Maintenance Plans to automate routine database optimization and maintenance. Even though you can automate many routine tasks, you should regularly review report histories to track maintenance plan execution. Additionally, you may find that occasionally you need to perform some optimization and maintenance tasks manually, and you can do this with Database Maintenance Plans as well.
Optionally, configure log shipping to enable other SQL Servers to act as standby servers that can be brought online manually in case of primary server failure. Typically, log shipping is used as an alternative to database mirroring, which was discussed in Chapter 14. However, both features can be configured, enabled, and in use simultaneously on any given SQL Server instance.