Skip to content

Commit feec56f

Browse files
sqlrodblokesqlrodbloke
committed
Initial Upload
Initial upload of SQLConnectCheckPSJobs
1 parent 461ec66 commit feec56f

1 file changed

Lines changed: 231 additions & 0 deletions

File tree

‎SQLEstateCheckPSJobs/SQLConnectCheckPSJobs.ps1‎

Lines changed: 231 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,231 @@
1+
# Install-Module -Name ThreadJob -Force
2+
# import-module ThreadJob
3+
4+
$TaskName='SQLHeartbeat'
5+
$CMSServer='MYCMSSERVER'
6+
$CMSGroup='PROD'
7+
$Noformat=$false
8+
$Failuresonly=$false
9+
10+
$throttle = [int]$env:NUMBER_OF_PROCESSORS+1
11+
$threaded = $true
12+
13+
$Output=$null
14+
clear
15+
16+
$serverlist=@()
17+
18+
# Get target servers to check from CMS
19+
If ($CMSGroup)
20+
{
21+
$Serverlist=(Get-DbaRegServer -SqlInstance $CMSServer -Group $CMSGroup | select Servername).Servername
22+
}
23+
# Or If you want just to include an array of servers you can:
24+
#$serverlist='LAPTOP2','LAPTOP2\NAMED1'
25+
26+
# Or pull from an inventory table:
27+
#$serverlist=Invoke-Sqlcmd -ServerInstance MyinventoryServer -Database MyinventoryDB -Query "Select servername from MyinventoryTable"
28+
29+
$Totalcount=($Serverlist).count
30+
If ($Totalcount -eq 0){
31+
Write-Host 'No servers to check' -ForegroundColor Yellow
32+
Break
33+
}
34+
# Script Block of what we actually want to run.
35+
$GetSQLInfo = {
36+
param(
37+
$full_instance
38+
)
39+
try{
40+
# split server and instance
41+
if ($full_instance -like '*\*')
42+
{
43+
$server = $full_instance.substring(0,$full_instance.IndexOf('\'))
44+
$instance = $full_instance.Replace("$server\",'')
45+
$instancename = "MSSQL`$$instance"
46+
}
47+
else
48+
{
49+
$server = $full_instance
50+
$instance = 'MSSQLSERVER'
51+
$instancename= 'MSSQLSERVER'
52+
53+
}
54+
# Extra check for fast-fail
55+
if (Test-Connection -ComputerName $server -Quiet -Count 1) {
56+
$Ispingable=$true
57+
# Check if SQL service started
58+
If( (get-service -ComputerName $server | where Name -eq $instancename).Status -ne 'Stopped' )
59+
{
60+
$IsSQLServiceUp=$true
61+
# Get some data from SQL
62+
$sqlstmt="DECLARE @auth varchar(8)
63+
IF EXISTS (SELECT * FROM sys.dm_exec_connections WHERE auth_scheme='KERBEROS')
64+
SET @auth ='KERBEROS'
65+
ELSE
66+
SET @auth ='NTLM'
67+
SELECT SERVERPROPERTY('ServerName') AS SQLinstance,SERVERPROPERTY('Edition') AS Edition,SERVERPROPERTY('ProductVersion') AS VersionNumber,SERVERPROPERTY('IsHadrEnabled') AS Hadr, @Auth AS AuthScheme,
68+
(select status_desc from sys.dm_server_services where servicename LIKE 'SQL Server Agent%') AS SQLAgentStatus"
69+
try {
70+
$sqldata=Invoke-Dbaquery -SQLInstance $full_instance -Database 'master' -Query $sqlstmt -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
71+
72+
If ($sqldata){
73+
$IsSqlresponding=$true
74+
# Express detect, blank agent data
75+
If ($sqldata.Edition -like '*Express*'){
76+
$sqldata.SQLAgentStatus = $null
77+
}
78+
}
79+
}
80+
catch {
81+
$IsSqlresponding=$false
82+
}
83+
}
84+
Else {
85+
$IsSQLServiceUp=$false
86+
}
87+
88+
# Build status output from vars
89+
$Detail=[PSCustomObject]@{
90+
SQLInstance = $full_instance
91+
IsPingable = $Ispingable
92+
IsSQLServiceUp = $IsSQLServiceUp
93+
IsSqlresponding = $IsSqlresponding}
94+
95+
# Add SQL info if we have any
96+
If ($sqldata) {
97+
98+
$Detail | Add-Member -MemberType NoteProperty "Edition" -Value $sqldata.Edition
99+
$Detail | Add-Member -MemberType NoteProperty "VersionNumber" -Value $sqldata.VersionNumber
100+
$Detail | Add-Member -MemberType NoteProperty "Hadr" -Value $sqldata.Hadr
101+
$Detail | Add-Member -MemberType NoteProperty "Authscheme" -Value $sqldata.AuthScheme
102+
$Detail | Add-Member -MemberType NoteProperty "SQLAgentStatus" -Value $sqldata.SQLAgentStatus
103+
}
104+
}
105+
Else {
106+
$Detail=[PSCustomObject]@{
107+
SQLInstance = $full_instance
108+
IsPingable = $false
109+
}
110+
}
111+
$Detail # Return info
112+
}
113+
# Theres an error somewhere, return this fact.
114+
catch{
115+
$Detail=[PSCustomObject]@{
116+
SQLInstance = "$full_instance - ERROR IN COLLECTION "}
117+
}
118+
}
119+
120+
# End of Script Block
121+
122+
$i=0
123+
ForEach($Server in $serverlist)
124+
{
125+
$i++
126+
while( @(Get-Job -State Running).Count -ge $Throttle)
127+
{
128+
Write-Host "Max concurrency reached...Throttling..." -ForegroundColor Yellow
129+
Start-Sleep -Milliseconds 2000
130+
}
131+
132+
Write-Progress -Activity $TaskName -Status "Starting threads..." -CurrentOperation "$i of $TotalCount Started" -PercentComplete ($i/$TotalCount*100)
133+
134+
# Call a job for the task for the server Threaded or normal depending on param
135+
if (-not $threaded) {
136+
$ID=(Start-Job -Name $TaskName-$server -ScriptBlock $GetSQLInfo -ArgumentList $server).ID
137+
}
138+
Else {
139+
$ID=(Start-ThreadJob -Name $TaskName-$server -ScriptBlock $GetSQLInfo -ArgumentList $server -ThrottleLimit $Throttle).ID
140+
}
141+
142+
Write-Verbose "Started Job $ID for Target: $Server"
143+
}
144+
145+
clear
146+
Write-Host "Threads started for all targets. Collating." -ForegroundColor Cyan
147+
$completed=@(Get-Job -State Completed).count
148+
149+
while( @(Get-Job -State Running | Where Name -like "$TaskName*" ).Count -gt 0){
150+
$completed=@(Get-Job -State Completed).count
151+
Write-Progress -Activity $TaskName -Status "Completed threads..." -CurrentOperation "$completed of $TotalCount Completed" -PercentComplete ($completed/$TotalCount*100)
152+
Start-Sleep -Milliseconds 500
153+
}
154+
Write-Progress -Completed -Activity "Done."
155+
# Return results
156+
$Output=Get-Job | Where Name -like "$TaskName*" | Wait-Job | Receive-Job | Select *
157+
$Jobs=Get-Job | Where Name -like "$TaskName*"
158+
159+
If ($Noformat){
160+
If (-not $Failuresonly){
161+
$output | Select SQLInstance, IsPingable, IsSQLServiceUp, IsSQLResponding, Edition, VersionNumber, Hadr, AuthScheme, SQLAgentStatus | Where {$null -ne ($_.SQLInstance)}
162+
}
163+
Else {
164+
foreach ($line in $output){
165+
If(($line.IsPingable -eq $false ) -or ($line.IsSQLServiceUp -eq $False) -or ($line.IsSQLResponding -eq $false) -or ($line.SQLAgentStatus -eq 'Stopped')) {
166+
$line | Select SQLInstance, IsPingable, IsSQLServiceUp, IsSQLResponding, Edition, VersionNumber, Hadr, AuthScheme, SQLAgentStatus | Where {$null -ne ($_.SQLInstance)}
167+
}
168+
}
169+
}
170+
}
171+
Else
172+
# Lets present our findings in a pretty way
173+
{
174+
foreach ($line in $output)
175+
{
176+
If ($line.IsPingable -eq $false)
177+
{
178+
Write-Host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable)" -ForegroundColor Red
179+
}
180+
181+
If (($line.IsPingable -eq $True) -and ($line.IsSQLServiceUp -eq $False) )
182+
{
183+
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
184+
Write-host "SQL Service Up: $($line.IsSQLServiceUp) " -ForegroundColor Red
185+
}
186+
If (($line.IsSQLServiceUp -eq $True) -and ($line.IsSQLResponding -eq $false) )
187+
{
188+
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
189+
Write-host "SQL Service Up: $($line.IsSQLServiceUp), " -ForegroundColor Green -NoNewline; Write-host "SQL Responding: $($line.IsSQLResponding)" -ForegroundColor Red
190+
}
191+
192+
# SQL Agent down
193+
If (($line.IsSQLResponding -eq $True) -and ($line.SQLAgentStatus -eq 'Stopped'))
194+
{
195+
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
196+
Write-host "SQL Service Up: $($line.IsSQLServiceUp) " -ForegroundColor Green -NoNewline; Write-host " SQL Responding: $($line.IsSQLResponding), " -ForegroundColor Green -NoNewline;`
197+
Write-Host " SQLAgent Status: $($line.SQLAgentStatus), " -ForegroundColor Red -NoNewline; Write-host " Edition: $($line.Edition), Version: $($line.VersionNumber), Hadr: $($line.hadr), Auth: $($line.AuthScheme)" -ForegroundColor Green
198+
}
199+
200+
If (-not $Failuresonly){
201+
# ALL OK
202+
If (($line.Ispingable -eq $True) -and ($line.IsSQLServiceUp -eq $True) -and ($line.IsSQLResponding -eq $true) -and ('Running' -eq $line.SQLAgentStatus))
203+
{
204+
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
205+
Write-host "SQL Service Up: $($line.IsSQLServiceUp), " -ForegroundColor Green -NoNewline; Write-host "SQL Responding: $($line.IsSQLResponding), " -ForegroundColor Green -NoNewline;`
206+
Write-Host "SQLAgent Status: $($line.SQLAgentStatus), " -ForegroundColor Green -NoNewline; Write-host "Edition: $($line.Edition), Version: $($line.VersionNumber), Hadr: $($line.hadr), Auth: $($line.AuthScheme)" -ForegroundColor Green
207+
}
208+
If (($line.ConnectSuccess -eq $True) -and ($line.IsPingable -eq $True) -and ($line.IsSQLResponding -eq $true) -and ($null -eq $line.SQLAgentStatus))
209+
{
210+
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
211+
Write-host "SQL Service Up: $($line.IsSQLServiceUp), " -ForegroundColor Green -NoNewline; Write-host "SQL Responding: $($line.IsSQLResponding), " -ForegroundColor Green -NoNewline;`
212+
Write-Host "SQLAgent Status: N/A, " -ForegroundColor Green -NoNewline; Write-host "Edition: $($line.Edition), Version: $($line.VersionNumber), Hadr: $($line.hadr), Auth: $($line.AuthScheme)" -ForegroundColor Green
213+
}
214+
}
215+
}
216+
217+
Write-Host "`nJOB SUMMARY: " -ForegroundColor DarkGreen
218+
Write-Host "Target Count: $Totalcount"
219+
Write-Host "Not Completed: "($Jobs | Where State -ne 'Completed').count
220+
Write-Host "Completed: "($Jobs | Where State -eq 'Completed').Count
221+
Write-Host "Success %: "(($Jobs | Where State -eq 'Completed').count/$TotalCount*100)
222+
223+
<#Output anything not marked as completed.#>
224+
If (($Jobs | Where State -eq 'Completed').count -ne 0)
225+
{
226+
$Jobs | Where State -ne 'Completed' | Select Id, Name, State
227+
}
228+
}
229+
230+
<#Now Tidy Up#>
231+
Get-Job | Remove-Job -Force

0 commit comments

Comments
 (0)