# encoding: ascii
# api: powershell
# title: Test-SqlConnection
# description: A series of functions to verify Agent, SQL, SSIS, SMB, WMI services or connections. Useful when troubleshooting data center firewall configuration.
# version: 1.0
# type: script
# author: Chad Miller
# license: CC0
# function: Test-Ping
# x-poshcode-id: 2732
# x-archived: 2011-10-25T12:36:30
# x-published: 2011-06-13T18:35:00
#
#
#######################
<#
Version History
v1.0 - Chad Miller - Initial release
v1.1 - Chad Miller - Fixed issues, added colorized HTML output
#>
Add-Type -AssemblyName System.Xml.Linq
$Script:CMServer = 'MyServer'
#######################
function Test-Ping
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ComputerName)
process
{
$ComputerName | foreach {$result=Test-Connection -ComputerName $_ -Count 1 -Quiet;
new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args=$_;Result=$result;Message=$null}}
}
} #Test-Ping
#######################
function Test-Wmi
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ComputerName)
process
{
try {
$ComputerName | foreach {$name=$_; Get-WmiObject -ComputerName $name -Class Win32_ComputerSystem -ErrorAction 'Stop' | out-null;
new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$true;Message=$null}}
}
catch { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message="$($_.ToString())"} }
}
} #Test-Wmi
#######################
function Test-Port
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ComputerName
,[Parameter(Mandatory=$true)] [int32]$Port)
Process {
try {
$Computername | foreach {
$sock = new-object System.Net.Sockets.Socket -ArgumentList $([System.Net.Sockets.AddressFamily]::InterNetwork),$([System.Net.Sockets.SocketType]::Stream),$([System.Net.Sockets.ProtocolType]::Tcp); $name=$_; `
$sock.Connect($name,$Port)
new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$true;Message=$null}
$sock.Close()}
}
catch { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message="$($_.ToString())"} }
}
} #Test-Port
#######################
function Test-SMB
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ComputerName)
process
{ Test-Port $ComputerName 445 }
} #Test-SMB
#######################
function Test-SSIS
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ComputerName
,[Parameter(Mandatory=$true)] [ValidateSet("2005", "2008")] [string]$Version
)
#Note: Unlike the database engine and replication SSIS is not backwards compatible
# Once an assembly is loaded, you can unload it. This means you need to fire up a powershell.exe process
# and mixing between 2005 and 2008 SSIS connections are not permitted in same powershell process.
# You'll need to test 2005 and 2008 SSIS in separate powershell.exe processes
Begin {
$ErrorAction = 'Stop'
if ($Version -eq 2008)
{ add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" }
else
{ add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" }
}
process
{
$ComputerName | foreach {
$name=$_; `
if ((test-SSISService -ComputerName $name).Result)
{
try {
$app = new-object ("Microsoft.SqlServer.Dts.Runtime.Application")
$out = $null
$app.GetServerInfo($name,[ref]$out) | out-null
new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$true;Message=$null}
}
catch { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message="$($_.ToString())"}}
}
else
{ new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message='SSIS Not Running'} }
}
}
} #Test-SSIS
#######################
function Test-SQL
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ServerInstance)
process {
$ServerInstance | foreach {
$name = $_; `
if ((test-Ping $(ConvertTo-ComputerName $name)).Result)
{
$connectionString = "Data Source={0};Integrated Security=true;Initial Catalog=master;Connect Timeout=3;" -f $name
$sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString
try {
$sqlConn.Open()
new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$true;Message=$null}
}
catch { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message="$($_.ToString())"} }
finally { $sqlConn.Dispose() }
}
else
{ new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message='Host Unreachable'} }
}
}
} #Test-SQL
#######################
function Test-AgentService
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ComputerName)
process
{
try {
$ComputerName | foreach {
$name=$_; `
if (Get-WmiObject -Class Win32_Service -ComputerName $name -Filter {Name Like 'SQLAgent%' and State='Stopped'} -ErrorAction 'Stop') {
new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message=$null} }
else {
new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$true;Message=$null} }
}
}
catch { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message="$($_.ToString())"} }
}
} #Test-AgentService
#######################
function Test-SqlService
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ComputerName)
process
{
try {
$ComputerName | foreach {
$name=$_; `
if (Get-WmiObject -ComputerName $name `
-query "select Name,State from Win32_Service where (NOT Name Like 'MSSQLServerADHelper%') AND (Name Like 'MSSQL%' OR Name Like 'SQLServer%') And State='Stopped'" `
-ErrorAction 'Stop')
{ new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message=$null} }
else
{ new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$true;Message=$null} }
}
}
catch { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message="$($_.ToString())"} }
}
} #Test-SqlService
#######################
function Test-SSISService
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ComputerName)
process
{
try {
$ComputerName | foreach {
$name=$_; `
if (Get-WmiObject -Class Win32_Service -ComputerName $ComputerName -Filter {Name Like 'MsDtsServer%' And State='Stopped'} -ErrorAction 'Stop')
{ new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message=$null} }
else
{ new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$true;Message=$null} }
}
}
catch { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message="$($_.ToString())"} }
}
} #Test-SSISService
#######################
<#
.SYNOPSIS
Runs a T-SQL script.
.DESCRIPTION
Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified
.INPUTS
None
You cannot pipe objects to Invoke-Sqlcmd2
.OUTPUTS
System.Data.DataTable
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"
This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
StartTime
-----------
2010-08-12 21:21:03.593
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"
This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose
This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
VERBOSE: hello world
.NOTES
Version History
v1.0 - Chad Miller - Initial release
v1.1 - Chad Miller - Fixed Issue with connection closing
v1.2 - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
v1.3 - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
#>
function Invoke-Sqlcmd2
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
[Parameter(Position=1, Mandatory=$false)] [string]$Database,
[Parameter(Position=2, Mandatory=$false)] [string]$Query,
[Parameter(Position=3, Mandatory=$false)] [string]$Username,
[Parameter(Position=4, Mandatory=$false)] [string]$Password,
[Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
[Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
[Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile,
[Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow"
)
if ($InputFile)
{
$filePath = $(resolve-path $InputFile).path
$Query = [System.IO.File]::ReadAllText("$filePath")
}
$conn=new-object System.Data.SqlClient.SQLConnection
if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
$conn.ConnectionString=$ConnectionString
#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
if ($PSBoundParameters.Verbose)
{
$conn.FireInfoMessageEventOnUserErrors=$true
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
$conn.add_InfoMessage($handler)
}
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
switch ($As)
{
'DataSet' { Write-Output ($ds) }
'DataTable' { Write-Output ($ds.Tables) }
'DataRow' { Write-Output ($ds.Tables[0]) }
}
} #Invoke-Sqlcmd2
#######################
function Test-DatabaseOnline
{
param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ServerInstance)
begin
{
$query = @"
SELECT name
FROM sysdatabases
WHERE DATABASEPROPERTYEX(name,'Status') <> 'ONLINE'
"@
}
process
{
try {
$ServerInstance | foreach {
$name=$_; `
$out = Invoke-Sqlcmd2 -ServerInstance $name -Database master -Query $query -ConnectionTimeout 5 | foreach {$_.name}
if ($out)
{$out = $out -join ","; new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message=$out} }
else
{ new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$true;Message=$null} }
}
}
catch { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message="$($_.ToString())"} }
}
} #Test-DatabaseOnline
#######################
filter ConvertTo-ComputerName
{
param ($ServerInstance)
if ($_)
{ $ServerInstance = $_ }
$ServerInstance -replace "\\.*|,.*"
} #ConvertTo-ComputerName
#######################
filter ConvertTo-NamePortNumber
{
param ($ServerInstance)
if ($_)
{ $ServerInstance = $_ }
$work = $ServerInstance -split ","
if ($work[1])
{ new-object psobject -Property @{ComputerName=$work[0];PortNumber=$work[1]} }
} #ConvertTo-NamePortNumber
#######################
function Get-CMServer
{
param($ServerInstance,$GroupName,[switch]$UsePort)
$query = @"
FROM msdb.dbo.sysmanagement_shared_registered_servers s
JOIN msdb.dbo.sysmanagement_shared_server_groups g
ON s.server_group_id = g.server_group_id
WHERE 1 = 1
"@
if ($UsePort) {
$query = "SELECT DISTINCT s.server_name AS 'name'`n" + $query + "`nAND PATINDEX('%,%', s.server_name) <> 0"
}
else {
$query = "SELECT DISTINCT s.name`n" + $query
}
if ($GroupName) {
$query = $query + "`nAND g.name = '$GroupName'"
}
#Write-Host $query
Invoke-SqlCmd2 -ServerInstance $ServerInstance -Database msdb -Query $query | foreach {$_.name}
} #Get-CMServer
#######################
filter Get-SqlVersion2
{
param($ServerInstance)
$query = @"
select CAST(SERVERPROPERTY('ServerName') AS varchar(128)) AS 'server_name',
CASE CONVERT(int, LEFT(CONVERT(varchar(10),SERVERPROPERTY('ProductVersion')),1))
WHEN 1 THEN '2008'
WHEN 9 THEN '2005'
WHEN 8 THEN '2000'
END AS 'version'
"@
if ($_)
{ $ServerInstance = $_ }
Invoke-SqlCmd2 -ServerInstance $ServerInstance -Database master -Query $query -ConnectionTimeout 5
} #Get-SqlVersion2
#######################
function Test-Main
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [ValidateSet("Ping","WMI","SMB","Port","SQL","Database","SSIS","Agent")] [string]$Test,
[Parameter(Position=1, Mandatory=$false)] [string]$GroupName,
[Parameter(Position=2, Mandatory=$false)] [string]$SqlVersion='2008'
)
#HTML colorize code adapted from post by Joel Bennet
#http://stackoverflow.com/questions/4559233/technique-for-selectively-formatting-data-in-a-powershell-pipeline-and-output-as
switch ($test)
{
'Ping' { $html = Get-CMServer $Script:CMServer $GroupName | ConvertTo-ComputerName | Test-Ping | ConvertTo-Html }
'WMI' { $html = Get-CMServer $Script:CMServer $GroupName | ConvertTo-ComputerName | Test-WMI | ConvertTo-Html }
'SMB' { $html = Get-CMServer $Script:CMServer $GroupName | ConvertTo-ComputerName | Test-SMB | ConvertTo-Html }
'Port' { $html = Get-CMServer $Script:CMServer $GroupName -UsePort | ConvertTo-NamePortNumber | foreach {Test-Port $_.ComputerName $_.PortNumber} | ConvertTo-Html }
'SQL' { $html = Get-CMServer $Script:CMServer $GroupName | Test-SQL | ConvertTo-Html }
'Database' { $html = Get-CMServer $Script:CMServer $GroupName | Test-DatabaseOnline | ConvertTo-Html }
'SSIS' { $html = Get-CMServer $Script:CMServer $GroupName | Get-SqlVersion2 | where {$_.version -eq $SqlVersion } | foreach {$_.server_name} | ConvertTo-ComputerName | Test-SSIS -Version $SqlVersion | ConvertTo-Html }
'Agent' { $html = Get-CMServer $Script:CMServer $GroupName | ConvertTo-ComputerName | Test-AgentService | ConvertTo-Html }
}
$xml = [System.Xml.Linq.XDocument]::Parse("$html")
# Find the index of the column you want to format:
$index = (($xml.Descendants("{http://www.w3.org/1999/xhtml}th") | Where-Object { $_.Value -eq "Result" }).NodesBeforeSelf() | Measure-Object).Count
# Format the column based on whatever rules you have:
switch($xml.Descendants("{http://www.w3.org/1999/xhtml}td") | Where { ($_.NodesBeforeSelf() | Measure).Count -eq $index } ) {
{'True' -eq $_.Value } { $_.SetAttributeValue( "style", "background: green;"); continue }
{'False' -eq $_.Value } { $_.SetAttributeValue( "style", "background: red;"); continue }
}
# Save the html out to a file
$xml.Save("$pwd/$test.html")
# Open the thing in your browser to see what we've wrought
ii .\$test.html
} #Test-Main