# encoding: ascii
# api: powershell
# title: Test-SqlConnection
# description: A series functions to verify Agent, SQL, SSIS, SMB, WMI services or connections. Useful when troubleshooting data center firewall configuration.
# version: 1.0
# type: function
# author: Chad Miller
# license: CC0
# function: Test-Ping
# x-poshcode-id: 2722
# x-archived: 2011-06-14T00:07:47
# x-published: 2011-06-09T13:29:00
#
#
#######################
<#
Version History
v1.0   - Chad Miller - Initial release
#>


#######################
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 = $ServerInstance; `
        $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() }
        }
    }

} #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 ConvertTo-Hashtable
{ 
    param([string]$key, $value) 

    Begin 
    { 
        $hash = @{} 
    } 
    Process 
    { 
        $thisKey = $_.$Key
        $hash.$thisKey = $_.$Value 
    } 
    End 
    { 
        Write-Output $hash 
    }

} #ConvertTo-Hashtable

#######################
function Test-DatabaseOnline
{
    param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] [string[]]$ServerInstance)
    
    begin
    {
$query = @"
SELECT name, DATABASEPROPERTYEX(name,'Status') AS 'Status'
FROM sysdatabases
WHERE DATABASEPROPERTYEX(name,'Status') <> 'ONLINE'
"@
    }
    process
    { 
        try {
            $ServerInstance | foreach {
            $name=$_; `
            $ht = Invoke-Sqlcmd2 -ServerInstance $name -Database master -Query $query | ConvertTo-Hashtable -key name -value status
            if ($ht.count -eq 0)
            { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$true;Message=$null} }
            else
            { new-object psobject -property @{Test="$($myinvocation.mycommand.name)";Args="$name";Result=$false;Message=$ht} }
            }
        }
        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
