PowerShell Query to find all the SQL Server Instances Running on a list of Remote Windows Server Machine.

# #

# Read in a list of Server Names from a file.

# For each server, query the services to find the SQL server instance names.
# List all the SQL instances found to a log file(outputfile).
# List all the Servers where no SQL instances found to another logfile(Logerrs).
##

$servers = get-content "D:\Primary_Secondary_Check\Step1_Server_Reboot_ServerList.txt"
$outputfile = "D:\Primary_Secondary_Check\Step1_Server_Reboot_InstanceName_Output.txt"
$logerrs = "D:\Primary_Secondary_Check\Step1_Server_Reboot_InstanceName_LogFile.txt"


Get-Date -Format "dd-MMM-yyyy hh:mm:ss" >> $outputfile
Echo "Server, Instance" >> $outputfile

ForEach ($server in $servers)
{
   $instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like "MSSQL*"}
   if (!$?)
   {
      Get-Date -Format "dd-MMM-yyyy hh:mm:ss" >> $logerrs
      Echo "$server - No SQL instance found".ToUpper() >> $logerrs
      Echo "$server - No SQL instance found".ToUpper()
   }
   Else
   {
   
      ForEach ($instance in $instances)
      {
         if ($instance.name -like "MSSQL$*")
         {
            Echo "$server$($instance.name.Replace("MSSQL$","\"))".ToString().ToUpper() >> $outputfile
            Echo "$server$($instance.name.Replace("MSSQL$","\"))".ToString().ToUpper()
         }
         if ($instance.name -eq "MSSQLSERVER")
         {
            Echo "$server".ToString().ToUpper() >> $outputfile
            Echo "$server".ToString().ToUpper()
         }
      }
   }
}

Embed a Powershell script into Chef Recipe

Chef is a Open Source tool which is being used in many companies to automate their infrastructure.

As a part of automation we used the chef recipe to limit the size of application and system log to 100 MB for all sql server which gets provisioned in the domain.

As a part of chief client run the below script runs and make the required changes changes.

Below is an example of using power shell in chef recipe.

For Chef Reference, Click here

PreRequisites: The below code is just a recipe in the chef, I have not enclosed the details about the SQL Tiers. So this post will be most helpful for the people who use chef in their Domain.

Set the MaximumSize for Application and System Log for all the SQL Server windows server to 100MB: Using Powershell in Chef

powershell_script 'Set Application and System log size to all SQL Machines' do
  not_if "(Get-EventLog -List  |  Where-Object {($_.log -eq 'System' `
          -or $_.log -eq 'Application') -and $_.MaximumKilobytes`
          -lt '102400'}).Count -eq 0"
  code <<-EOH
  $Server = $env:computername
  $maxsize=104857600
  write-host $Server
  $Log=Get-EventLog -List  |  Where-Object {($_.log -eq 'System'`
  -or $_.log -eq 'Application') -and $_.MaximumKilobytes -lt '102400'}
Foreach($l in $log)
{
   $logname=$l.LogDisplayName
   Limit-EventLog -LogName $logname -MaximumSize $maxsize -ComputerName $Server
   Write-Host "Changing $logname to 100MB: $Server" -ForegroundColor DarkGreen
}
  EOH
end

Non Chef Users can schedule a task scheduler to run this script across multiple Servers, below is the code snippet for it.

Set the MaximumSize for Application and System Log for all the SQL Server windows server to 100MB: Using Powershell

$Servers = Get-Content D:\ChangeEventLogSize\ServerList.txt
$LogFile='D:\ChangeEventLogSize\ChangeEventLogSize_Log.txt'
$maxsize=104857600 #size is-100 MB

Get-Date -Format "dd-MMM-yyyy hh:mm:ss" | Out-File -filepath $LogFile -Append
Foreach($Server in $Servers)
{
write-host $Server

#Recoring the current value Before Making Changes
"`r`n Before making Changes to: $Server `r`n"| Out-File -filepath $LogFile -Append
Get-EventLog -List -computername $Server  |  where-object {$_.log -eq 'System' -or $_.log -eq 'Application'} | Out-File -filepath $LogFile -Append

#Change the size of the System and Application Log to 100MB
$Log=Get-EventLog -List  |  Where-Object {($_.log -eq 'System' -or $_.log -eq 'Application') -and $_.MaximumKilobytes -eq '102400'}
Foreach($l in $log)
{
   $logname=$l.LogDisplayName
   Limit-EventLog -LogName $logname -MaximumSize $maxsize -ComputerName $Server
   "Changing $logname to 100MB: $Server" | Out-File -filepath $LogFile -Append
}

#Recoring the current value After Making Changes
"`r`n After making Changes to: $Server `r`n" | Out-File -filepath $LogFile -Append
Get-EventLog -List -computername $Server  |  where-object {$_.log -eq 'System' -or $_.log -eq 'Application'} | Out-File -filepath $LogFile -Append

}