Powershell Script to find database server configuration Info


Add-pssnapin SQLServerCmdletsnapin100
Add-pssnapin SQLServerprovidersnapin100
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended")

$sqlserverList = ('ServerName\Instance1','ServerName\Instance2')
foreach ($SqlServer in $SqlServerList)
{
if ([array]::indexof($SqlServerList ,$SqlServer)%2 -eq 0)
{
$C ="DarkRed"
$D = "Black"
}
ELSE
{
$C= "DarkGreen"
$D = "Yellow"
}
$s = New-Object Microsoft.SQLServer.Management.Smo.Server $SQLServer
write-host '>>>>Database Server Configuration for '$SQLServer -foregroundcolor $C -BackgroundColor $d
write-host '1.InstanceName='$s.Name
write-host '2.ServerName =' $s.ComputerNamePhysicalNetBios
write-host '3.DB Service Account='$s.ServiceAccount
write-host '4.Clustered Instance='$s.IsClustered
write-host '5.Agent Service Account='$s.Jobserver.ServiceAccount
}


Advertisement

Fail-safe Operator – Powershell script

Powershell Script to find whether Fail-safe operator is enabled on sql server agent service. Fail-safe operator is recommended to be enabled,so, sql agent can communicate with the fail-safe operator,if it cannot notify the operator of the alert/job.


Add-pssnapin SQLServerCmdletsnapin100
Add-pssnapin SQLServerprovidersnapin100
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended")
$sqlserverList = ('SERVERNAME\INSTANCE1','SERVERNAME\INSTANCE2')
foreach($sqlserver in $sqlserverlist)
{
$s = New-Object Microsoft.SQLServer.Management.Smo.Server $SQLServer
$s.jobserver.alertsystem| select name,failsafeoperator,notificationmethod
}


Powershell

Powershell is a very powerful tool to administer/manage multiple servers and services. I am trying to learn powershell and I will try to blog about as I learn more.

Starting/Stopping service on local/remote computer
–>Starting service on computer
1.Start-Service |where-object {$_.Name -like "MSSQLSERVER"}
–>Stopping Services on remote computer
2.Stop-Service -InputObject (get-service -Computername REMOTECOMPUTERNAME | where-object {$_.Name -like "MSSQLServer"}) -Force
Force is required to stop service when the service has dependent services. It stops all services. In this example, it stops both the SQL Database engine and sql agent service.

3.Measure-command is used to find which powershell query is efficient.

4. Below powershell script can be used to find the failed sql server agent jobs.
Add-pssnapin SQLServerCmdletsnapin100
Add-pssnapin SQLServerprovidersnapin100
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended")

$sqlserverList = ('SERVERNAME\INSTANCENAME','SERVERNAME\INSTANCE2')
foreach($sqlserver in $sqlserverlist)
{
$s = New-Object Microsoft.SQLServer.Management.Smo.Server $SQLServer
$s.JobServer.Jobs|Select Name,OriginatingServer,Isenabled,LastRunOutCome | where-object {$_.IsEnabled -eq 'True' -and $_.LastRunOutcome -eq 'Failed'}
}

Microsoft has a powershell webcast http://technet.microsoft.com/en-us/scriptcenter/dd901154.aspx