Writing utility scripts are one way to become smarter when working with computers. From time to time I get asked about some tasks folks use repetitively and are being counterproductive. On this note, I wanted to paint a scenario which I saw one of the DBA’s doing on their environment of multiple SQL Server – restarting or stopping the SQL Server Agent Services. He was painstakingly expanding each of the notes using SSMS and stopping the services.
The reason for that is beyond the scope here. But I do remember it was a highly available AlwaysOn Cluster of SQL Server running. When I saw that, I immediately asked him if this was something he wanted to automate? The immediate reaction was – “Definitely Yes. If you can.”
So gave a simple powershell script to stop the Agent Services on his patch of multiple servers. The script was as shown below:
foreach($replica in "localhost", "Server2", "Server3")
{
"StartAgent instance " + $replica.tostring() + " "
(Get-wmiobject -ComputerName $replica Win32_Service -Filter "Name='SQLSERVERAGENT'" ).InvokeMethod("StopService",$null)
}
This quick script allowed for stopping and obviously with a simple change, I was also able to start the 3 SQL Server instances which were stopped. You might want to change this in your environments accordingly.
foreach($replica in "localhost", "Server2", "Server3")
{
"StartAgent instance " + $replica.tostring() + " "
(Get-wmiobject -ComputerName $replica Win32_Service -Filter "Name='SQLSERVERAGENT'" ).InvokeMethod("StartService",$null)
}
But interestingly, when I did it the first time on the server – I was getting an error.
PS C:\WINDOWS\system32> E:\StartSQLAgent.PS1 File E:\StartSQLAgent.PS1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_execution_policies?view=powershell-5.1. + CategoryInfo : SecurityError: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : UnauthorizedAccess
If you go through the error and do it as per the documentation, we need to make sure the PowerShell window has been started in “Administrator mode” and post this, I need to run the below scrip
t:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned [/code]
This will showup and big dialog box as executed from my “Windows PowerShell ISE” window:
Accept the same with “Yes to All” or “Yes” and we are ready to run the above script.
The more I explore and look at Windows PowerShell, more automated I seem to make processes that I start to love this technology. Do let me know if you every did something like this in your environments?
Reference: Pinal Dave (https://blog.sqlauthority.com)
