Eighth SQL Server Agent uses external programs

This article is the eighth article of the SQL Server Agent series, please refer to the original text for details

In the first few of these series, you learned how to monitor job activity and view job history with the SQL Server Agent job Activity Monitor. Job Activity Monitor is a good tool for real-time monitoring and management of SQL Server Agent jobs. Eighth, you will see another feature of SQL Server Agent--the ability to shell out to the operating system and run programs outside of the SQL Server en Vironment. Some programs are command lines (either traditional command applications/scripts or PowerShell scripts), or even ActiveX scripts, but you can run almost any program, as long as the program does not require the user to enter directly. You will learn how to run CmdExec and PowerShell scripts, and we will briefly discuss the applicable scenarios for each subsystem. You will learn how to invoke other programs from SQL Server Agent.
built-in job subsystem
As we saw in the previous article, there are several built-in job subsystems. In this article, you'll learn three built-in job subsystems that can run scripts or programs on Windows Server-wide, rather than in a SQL Server environment. These three subsystems include:
Operating System (CMDEXEC)
->powershell
->activex Script
When a program or script is started from these three subsystems, a separate process is created in Windows, and when the script or program runs, the information is passed back to the SQL Server Agent job.
operating System (CMDEXEC) subsystem
The first subsystem we discussed is the operating system (CMDEXEC) subsystem. The CmdExec subsystem opens a command prompt, as if you are logged on to a Windows computer that is running SQL Server. From this point you can run any command you can type in the command Prompt window yourself. This includes any batch files, scripts, or even programs that exist on the server.
How secure
Of course, the big question of all this is authority--what is the security context? By default, when you create a CmdExec subsystem job (or other), the job will run in the security context of the SQL Server Agent service account (8.1). You may also notice that there is a drop-down option under run identity. You will understand these proxy accounts in the tenth article. Another important tip: you must be a member of the sysadmin role under the SQL Server role to run the job with the SQL Server Agent service account.

Figure 8.1 Job safety in the CmdExec job step
Create a CmdExec job step
Create a new Job (Shellout), as shown in the new job step, 8.1. We set the step name to S1, change the job step type to "operating system (CMDEXEC)", run the Identity default (SQL Server Agent service account). In the Command window type "dir C:", click OK, and then OK to save the job. Run the job and view the history after execution is complete. Click on the job step to view the dir output (Figure 8.2)
Figure 8.2 CmdExec Job Run results
As you can imagine, this is a normal command, but you can run complex batch scripts and even start programs (for example, "Start Notepad" will run Notepad). It is worth noting that if you run a program, such as Notepad, it will run on a virtual hidden desktop, waiting for user input. Because the desktop is hidden, the user can not provide any input, even if the command exits the program. In other words, your job step will not complete/end (after the test job is completed, Notepad is still open). You can find the Notepad process under task Manager and kill the process. There are many interesting scenarios for running programs from the CmdExec subsystem, which automatically returns control to SQL Server Agent as soon as the program is complete.
PowerShell Subsystem
The subsystems of PowerShell are added to the database as SQL Server 2008 is released. It has PowerShell 1.0 or PowerShell 2.0 versions, depending on which version is installed on your server. When you create a job step and choose your step type for the PowerShell subsystem, you will have options similar to the CmdExec subsystem. You can enter a PowerShell script, or call an existing PowerShell script (. ps1). When you start a PowerShell session using SQL Server Agent, PowerShell provider and cmdlets are preloaded.
There are many things to consider with PowerShell script signing and security processing, which is too extensive for this article. However, you can read these technical development articles on Windows PowerShell Owner's manual.
However, PowerShell is likely to be your favorite scripting subsystem. For normal tasks in SQL Server, it is usually simple to run SQL scripts. However, for repetitive tasks, using PowerShell's subsystems is much simpler.
For example, reopen your shellout job and add a job step s2. Type select PowerShell, and then enter the following script (Figure 8.3)

  $server  = New-object (' Microsoft.SqlServer.Management.Smo.Server ') "(local)"  foreach  ( $database  in   $server  .databases) {  $dbName  =  $database  . Namewrite -output  database: $dbName    

Figure 8.3 PowerShell subsystem Job
This script will log in to your local SQL Server (if you use a named instance to change the instance name) and then loop through the names of each database. You can imagine backing up a database, or checking its properties. The other thing to note is that you must log in and create a database connection. Click OK, then OK. Adjust the workflow for step S1 to make the Step S2 run correctly. Run the job, and view the output results. You will see that the list of databases on the server is output.
What is really interesting about the PowerShell subsystem is that you can query the information from the operating system, or from Active Directory, and then run any scripts supported by your server. Do you want to query SQL Server listening ports? You can use WMI through PowerShell. Do you want to get some information from the registration form? There are many PowerShell scripts on the Web (including many articles in Sqlservercentral). The
ActiveX scripting Subsystem
ActiveX Scripting subsystem allows you to run ActiveX scripts that you can use to complete tasks in the operating system using VBScript or JScript. For completeness, the subsystem is included, but it is not recommended to use ActiveX Scripting in SQL Server Agent. The subsystem is obsolete, which means it will be deleted on a future version of the database. Which subsystem does the
select?
If you have an existing job using these subsystems, you should continue to keep it unless you have a compelling reason to change it. However, if you create a new job or job step, the PowerShell subsystem provides the most convincing capability. In addition, Microsoft has apparently shifted to PowerShell direction as a standard scripting language for all Microsoft server products. It's worth investing your time to learn about PowerShell.
Next
SQL Server Agent CmdExec, PowerShell, and ActiveX subsystems allow your database to perform many tasks, including running batch files or external programs. In addition, using PowerShell can access and control almost any Microsoft product. The PowerShell subsystem is recommended for new job/job steps.
in the next article, we will begin to focus on SQL Server Agent security. Until now, this series has assumed that you are a member of the sysadmin server role. The next step is to use a non-system administrator in SQL Server Agent, while learning more about the job security context.

Eighth SQL Server Agent uses external programs