Chapter 14. Monitoring and automation

This chapter is dedicated to the production DBA, whose role, among other things, includes monitoring SQL Server instances for abnormal activity, managing the response to failure conditions, and carrying out a number of proactive maintenance tasks. In large organizations, such DBAs are typically located within, or accessible to, a command center, whose role is to perform similar tasks for all supported infrastructure and applications on a 24/7 basis.

We'll begin this chapter with coverage of a range of monitoring tools including Activity Monitor, SQL Server Profiler, and Performance Monitor. With a vast range of monitoring tools available, choosing the right tool for the job is an important skill; in addition to covering how these tools are used, we'll touch on how they should not be used.

Our focus will then shift to the importance of automation and its role in reducing errors and achieving more within the limited hours of a day. We'll cover maintenance plans and SQL Server Agent jobs before looking at setting up alerts for error and performance conditions.