Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features

SQL Server’s Automated Administration Architecture

SQL Server 2005, like its predecessors, comes equipped with an impressive array of management interfaces and services. This chapter looks at several aspects of its automated administrative functionality:

• The ability to program SQL Server to perform administrative tasks automatically and to keep you, the DBA, informed

• The ability to troubleshoot SQL Server when temperatures start to rise, in the server room and under your collar

• The ability to monitor the performance of SQL Server

We will look at the automated administrative architecture and describe how to work with SQL Agent. Then we will explore SQL Mail and Database Mail for automating e-mail alerts for both legacy applications and new projects.

SQL Server 2005’s administrative architecture provides a facility that allows the DBA to program the server to administer for itself the many repetitive actions and duties it needs to perform to ensure service level, availability, and optimum performance.

This is not only useful for you as the DBA, allowing you to spend more time doing things like index management, query performance optimization, and so on, but it is also an ideal mechanism for turnkey operations in which a DBA is typically not available. The ISV or integrator can install specialized scripts and upon deployment of a product can allow the SQL Server DBMS to self-administer its databases and other objects.

The automation capability is not only limited to features such as backup, index defragmentation, and so on, but you can program the server to automatically fire replication events and specialized scripts that are deployed to meet certain business rules and objectives.