Sunday, March 3, 2013

SelfNote: PowerShell on SQL Jobs

You know what. The lack of "Start" button on Win8 makes me really learn PowerShell. I feel I am more like a Unix admin than an average user. :-D

 # disable backup job on a server  
 function Disable-BackupJob($serverName)  
 {  
   invoke-command -computerName $serverName -ScriptBlock { `  
     param($serverName); `  
     [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null; `  
     $srv = New-Object Microsoft.SqlServer.Management.SMO.Server($serverName); `  
     $jobs = $srv.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE} | Where-Object { $_.Name -like '*backup*' }; `  
     foreach ($job in $jobs) `  
     { `  
       write-host "$serverName.$job is to FALSE"; `  
       $job.IsEnabled = $false; `  
       $jobs.Alter(); `  
     }; `  
     $jobs = $srv.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE} | Where-Object { $_.Name -like '*backup*' }; `  
     if ($jobs.Count -eq 0) { write-host "$serverName Done." } else { write-host "$serverName failed!" }; `  
   }`  
   -ArgumentList $serverName  
 }  

Saturday, March 2, 2013

SelfNote: PowerShell scripts

the following three functions are use PowerShell to set password, move cluster, and test connection to a database server.

 #set account username and password  
 function Set-Password($computerName, $serviceName, $serviceAccount, $password)  
 {    
   invoke-command -computerName $computerName -ScriptBlock `  
   { param($computerName, $serviceName, $serviceAccount, $password); `  
     write-host "on computer " $env:ComputerName "working on " $serviceName; `  
     $filter = "Name='" + $serviceName + "' "; `  
     $sqlservice=Get-WMIObject win32_service -filter $filter;`  
     $result = $sqlservice.change($null,$null,$null,$null,$null,$null, $serviceaccount,$password,$null,$null,$null);`  
     if ($result.ReturnValue -eq 0) { write-host $computerName " done!"; } else { write-host $computerName " failed!"; } `  
   } `  
   -ArgumentList $computerName,$serviceName,$serviceAccount,$password `  
 }  
 # move cluster  
 function Move-Cluster($computerName)  
 {  
   invoke-command -computerName $computerName -ScriptBlock `  
   { `  
     import-module failoverclusters;`  
     $result = Move-ClusterGroup sqlgroup;`  
     write-host "owner node = " $result.OwnerNode; `  
     $result = Move-ClusterGroup sqlgroup;`  
     write-host "owner node = " $result.OwnerNode; `  
   }  
 }  
 #test connection  
 function Test-Connection($servername)  
 {  
   $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;  
   $SqlConnection.ConnectionString = "Server=$servername;Database=master;Integrated Security=True";  
   try  
   {  
     $SqlConnection.Open();  
     write-host "$servername connection OK."      
   }  
   catch  
   {  
     write-host "$servername connection failed"  
     write-host $error[0]  
   }  
   finally  
   {  
     $SqlConnection.Close();  
   }  
 }