47fx3w
Last Updated: February 25, 2016
·
420
· dwimbley

Script to test Sql Server Connection

A useful script to test sql server connectivity should a server's IP address change or a ACL rule change resulting in loss of connection. Could easily alter to run daily and send a mail should the connection go down.

[CmdletBinding()]
Param(
    [Parameter(Mandatory=$True)]
    [string]$ServerName,

    [Parameter(Mandatory=$True)]
    [string]$DatabaseName,

    [Parameter(Mandatory=$True)]
    [string]$Username,

    [Parameter(Mandatory=$True)]
    [string]$Password
)

$connectionString = [string]::Format( "server={0};database={1};uid={2};pwd={3};", $ServerName,     $DatabaseName,$Username,$Password) 

$conn = New-Object system.Data.SqlClient.SqlConnection
$conn.connectionstring = $connectionString
$conn.open()

switch ($conn.State)
{
"Open" { Write-Host "Connection Works"; }
Default { Write-Host "The connection is $($conn.State). Error connecting to DB. Check Acl rules or windows firewall"; }
}

Usage

.\Test-SqlServer.ps1 -ServerName <hostname_here> -DatabaseName <db_here> -Username <user_here> -Password <pass_here>