19 SQL Server Agent administration

 

Now that you’re familiar with how the SQL Server Agent engine and PowerShell can best work together, we’ll discuss how dbatools can help you manage SQL Server Agent. As we discussed in chapter 1, the manual administration of SQL Server Agent jobs can be very time consuming, especially when you have many instances or many jobs to administer. This chapter provides all of the tools you need to administer your SQL Server Agent estate efficiently. You’ll learn how to gather all of the information about your SQL Server Agents, how to find a particular job in your estate easily, and how to retrieve and display the Agent job results and history.

DBAs are used to examining and administering the SQL Server Agent using SQL Server Management Studio (SSMS). dbatools enables you to perform the same tasks at the command line. Using the command line makes it easier to manage multiple objects or instances at once.

Figure 19.1 shows the view of the SQL Server Agent, which we have used to provide an order for the information in this section. It also demonstrates that there is no easy way to gather information about the objects on multiple instances in the same view with SSMS because the SQL Server Agent information is located under each node in the instance in Object Explorer.

Figure 19.1 SQL Server Agent in SSMS

We will start in a manner that should be becoming familiar to you now: by gathering information about your SQL Server Agent.

19.1 Listing SQL Server Agent information

19.1.1 SQL Server Agent jobs

19.1.2 SQL Server Agent alerts

19.1.3 Finding specific Agent jobs

19.2 Agent job results and history

19.2.1 Agent job results

19.2.2 Time line

19.3 Hands-on lab