18 PowerShell and 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 to administer. This chapter provides all of the tools you’ll need to administrate your SQL Server Agent estate efficiently. You will 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.

We have waited to talk fully about the SQL Server Agent until this chapter (and the next two) for a couple of reasons. We wanted you to have some knowledge about dbatools commands and be comfortable with their structure at the command line before discussing scheduling them with SQL Server Agent. As we discussed in chapter 1, we considered the order of the chapters as if we were DBAs starting to look at a new estate.

SQL Server Agent is the heart of scheduled task management in SQL Server, providing invaluable built-in functionality for DBAs to manage essential tasks such as backups, integrity checks, data imports, and more. It’s basically the Windows Task Scheduler of SQL Server that enables DBAs to run code on demand or automatically via a schedule. SQL Server Agent uses the msdb database to store its information and is available in all editions except for Express.

18.1 Which to choose, CmdExec or PowerShell job steps?

18.2 Creating Agent jobs to run PowerShell and dbatools

18.2.1 Creating a SQL Server credential

18.2.2 Creating a SQL Server Agent proxy

18.2.3 The PowerShell file

18.3 Creating the SQL Server Agent job with a CmdExec job step

18.4 Tips

18.4.1 Using default parameter values

18.4.2 Ensuring that the Agent job fails when the PowerShell fails

18.4.3 Logging

18.4.4 Execution policies

18.5 Hands-on lab