QuickBlog – Use PowerShell to submit SQLServicePack job to multiple Server

This was an interesting discussion in the LinkedIn “How to Install SQL Service Pack by PowerShell?”.  I got the chance to create and test this one out. I scratched the previous script I posted trying to show probe a concept, and ended up creating a new smaller script. Funny!! I created a smaller script!

Well, I just confirmed that’s possible to submit an unattended SQL ServicePack installer as a job using PowerShell Remoting. I’m using my Hyper-V Virtual Domain I ran the script from a Windows 7 VM and submitted one job to two servers. I could see the the jobs processing on the server Task Manager.

But, it was tedious? If you’re a newbie maybe it’s a little over your head but not impossible. That’s the intention of PowerShell Remoting, to be able to do these things like this, and I’m just scratching the surface. Please, Take advantage of these features in PS V2.0, and more enhancements has been done in PS v3.0 with the inclusion of Workflows.

Still, you will need to use PowerShell “Enable-PSRemoting -force” in all the servers. I know, this may be an issue but you need to configure it on all the servers in order to take advantage of PowerShell Remoting. Now, I’m creating session on each computer so I can run as jobs, and your credential is Important to be included. All this is done from your desktop, no more running to the server room.

This time I did test the new script using Sessions in PS Remoting:

[sourcecode language=”powershell”]
## – Get your credential information top connect to servers:
$getCred = Get-Credential ‘Domain99\UserName99’
$servers = "Server1","Server2";
$Jobsession = New-PSSession -Computername $servers -Credential $getCred;

## – display the sessions:
$Jobsession

## – Submit jobs to background process on selected servers:
Invoke-Command -Session $Jobsession -AsJob -JobName ‘TestBackgroundInstall’ `
-ScriptBlock {
new-psdrive -name SQLInstallDrive -psprovider FileSystem -root \\WIN8Server1\install;
cd SQLInstallDrive:;
& ./SQLServer2008R2SP1-KB2528583-x64-ENU.exe /allinstances;
};

## To Display jobs:
get-job

## – To Close PS Sessions and remove variabler:
Remove-PSSession $Jobsession
Remove-Variable Jobsession
[/sourcecode]

In the “Invoke-Command“, the “-ScriptBlock” parameter will hold the code you’re executing on the server as a background job.  Inside the ‘-scriptblock { .. }’ parameter, I’m executing three commands:

1. Creating the PSdrive to the “Install” shared folder.
2. Changing directory (this one could be optional).
3. Finally, run the SQLServer2008R2SP1-KB2528583-x64-ENU.exe SQL Service Pack,

This way you’re not holding the PowerShell Console hostage. You could even make this script more robust. You could add the parameter “-ThrottleLimit” to specify the max number of concurrent connections, to minimize the network traffic. This is just a start, this code can be improve a lot.

Check out the TechNet Tip link on PS Remoting:
http://technet.microsoft.com/en-us/magazine/ff700227.aspx

Added: Please, check this TechEd 2011 video of my college Don Jones talking about “Windows PowerShell Remoting: Definitely NOT Just for Servers”:
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/WCL321

Of course, there are products out there to help manage/automate your Microsoft security, and service packs.  But you will still need to invest time configuring the application.

Well!! This was a good one.
🙂