As I mentioned earlier in this series, it is possible to use the SQL Server Agent to execute PowerShell scripts. If you’ve worked with the SQL Server Agent to run jobs on your server, you already know that whenever you define a job step, you have to define a job step “type”. In SQL Server 2008 (and later), when you create a new job step, and click on the Type drop down, you see the following menu.
The most frequently used step types are T-SQL and the SQL Server Integration Services Package; however, you can see now that we have the PowerShell type. Selecting this tells the SQL Agent to invoke the SQLPS environment to run whatever commands are entered in the job step.
This screenshot simply shows the Job Step dialog box with some sample PS code. One thing to be very cognizant of: When creating a PowerShell job step, there is only one security context available, which is the “SQL Server Agent Service Account”. That means that if you intend to execute PowerShell scripts/commands from SQL Agent Job steps, the service account that the SQL Server Agent is running as must have appropriate permissions. This includes access to the folder where the scripts actually live, as well as any output directories. This also means that the you have to set the signing and restriction permissions on the SQL Server itself (as discussed earlier in this series). Once all of that is done, getting the SQL Server Agent Job setup to run your script is just like creating any other SQL Agent Job.
There’s one other “gotcha” to keep in mind. Everytime a SQL Agent Job step executes PowerShell code, it opens it own SQLPS.exe process. This process alone consumes about 20MB of memory (not counting any work its doing). If you have a server executing quite a few PowerShell job steps, this could get ugly. Be sure to keep tabs on how many PowerShell job steps are running on a server; try to avoid running too many of these types of jobs on very busy production servers. In fact, since the primary use for PowerShell for most DBAs is administrative in nature, try to use a “Admin” server for your PowerShell jobs. This will help logically separate the workload as well as prevent SQLPS.exe processes from using precious memory on your production SQL Servers.
That’s it; you can use many of the scripts and commands discussed in this series to create jobs to help you manage your servers, gather information, and keep you informed on your environment.
The most frequently used step types are T-SQL and the SQL Server Integration Services Package; however, you can see now that we have the PowerShell type. Selecting this tells the SQL Agent to invoke the SQLPS environment to run whatever commands are entered in the job step.
This screenshot simply shows the Job Step dialog box with some sample PS code. One thing to be very cognizant of: When creating a PowerShell job step, there is only one security context available, which is the “SQL Server Agent Service Account”. That means that if you intend to execute PowerShell scripts/commands from SQL Agent Job steps, the service account that the SQL Server Agent is running as must have appropriate permissions. This includes access to the folder where the scripts actually live, as well as any output directories. This also means that the you have to set the signing and restriction permissions on the SQL Server itself (as discussed earlier in this series). Once all of that is done, getting the SQL Server Agent Job setup to run your script is just like creating any other SQL Agent Job.
There’s one other “gotcha” to keep in mind. Everytime a SQL Agent Job step executes PowerShell code, it opens it own SQLPS.exe process. This process alone consumes about 20MB of memory (not counting any work its doing). If you have a server executing quite a few PowerShell job steps, this could get ugly. Be sure to keep tabs on how many PowerShell job steps are running on a server; try to avoid running too many of these types of jobs on very busy production servers. In fact, since the primary use for PowerShell for most DBAs is administrative in nature, try to use a “Admin” server for your PowerShell jobs. This will help logically separate the workload as well as prevent SQLPS.exe processes from using precious memory on your production SQL Servers.
That’s it; you can use many of the scripts and commands discussed in this series to create jobs to help you manage your servers, gather information, and keep you informed on your environment.
No comments:
Post a Comment
Hi,
Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.