Test Database Connectivity

Use the SqlConnection class to test a connection. You don't have to load any SQL assemblies.

Helper function:

function Test-SQLConnection
{    
    [OutputType([bool])]
    Param
    (
        [Parameter(Mandatory=$true,
                    ValueFromPipelineByPropertyName=$true,
                    Position=0)]
        $ConnectionString
    )
    try
    {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString;
        $sqlConnection.Open();
        $sqlConnection.Close();

        return $true;
    }
    catch
    {
        return $false;
    }
}

Usage example:

Test-SQLConnection "Data Source=localhost;database=myDB;User ID=myUser;Password=myPassword;"

This is basically the same as Martin's answer, only the connection string is build from the parameters, and the time taken to connect is measured.

e.g:

Test-SQLDatabase -Server SQLServer -Database SomeDB -Username SQLUser -Password password

or

Test-SQLDatabase -Server Server1\SQLExpress -Database SomeDB -UseWindowsAuthentication

.

function Test-SQLDatabase 
{
    param( 
    [Parameter(Position=0, Mandatory=$True, ValueFromPipeline=$True)] [string] $Server,
    [Parameter(Position=1, Mandatory=$True)] [string] $Database,
    [Parameter(Position=2, Mandatory=$True, ParameterSetName="SQLAuth")] [string] $Username,
    [Parameter(Position=3, Mandatory=$True, ParameterSetName="SQLAuth")] [string] $Password,
    [Parameter(Position=2, Mandatory=$True, ParameterSetName="WindowsAuth")] [switch] $UseWindowsAuthentication
    )

    # connect to the database, then immediatly close the connection. If an exception occurrs it indicates the conneciton was not successful. 
    process { 
        $dbConnection = New-Object System.Data.SqlClient.SqlConnection
        if (!$UseWindowsAuthentication) {
            $dbConnection.ConnectionString = "Data Source=$Server; uid=$Username; pwd=$Password; Database=$Database;Integrated Security=False"
            $authentication = "SQL ($Username)"
        }
        else {
            $dbConnection.ConnectionString = "Data Source=$Server; Database=$Database;Integrated Security=True;"
            $authentication = "Windows ($env:USERNAME)"
        }
        try {
            $connectionTime = measure-command {$dbConnection.Open()}
            $Result = @{
                Connection = "Successful"
                ElapsedTime = $connectionTime.TotalSeconds
                Server = $Server
                Database = $Database
                User = $authentication}
        }
        # exceptions will be raised if the database connection failed.
        catch {
                $Result = @{
                Connection = "Failed"
                ElapsedTime = $connectionTime.TotalSeconds
                Server = $Server
                Database = $Database
                User = $authentication}
        }
        Finally{
            # close the database connection
            $dbConnection.Close()
            #return the results as an object
            $outputObject = New-Object -Property $Result -TypeName psobject
            write-output $outputObject 
        }
    }
}

That depends on what you actually want to test. If you just want to verify that you can connect to the port on the remote host something like this will do:

$server = 'servername'
$port   = 1433

$tcp = New-Object Net.Sockets.TcpClient
if ([void]$tcp.Connect($server, $port)) {
  'connected'
} else {
  'not connected'
}
$tcp.Dispose()

If you want to verify that a connection to an SQL Server instance can be established you'll need something like this:

$dbhost = 'servername'
$dbinst = 'instance'
$dbname = 'master'

$username = ...
$password = ...

$cs = "Server=$dbhost\$dbinst;Database=$dbname;User Id=$username;" +
      "Password=$password;"

$cn = New-Object -COM 'ADODB.Connection'
$cn.ConnectionString = $cs
try {
  $cn.Open()
  if ($cn.State -eq 1) {
    'connected'
    $cn.Close()
  } else {
    'not connected'
  }
} catch {
  'not connected'
}

I've used the ConnectionState enum to check the database connection state.

Documentation on this enum can be found here

It can be accessed with the following: [System.Data.ConnectionState]::Open

Other options are Broken, Closed, Connecting, Executing, and Fetching.

Example:

class Database
{
    [System.Data.SqlClient.SqlConnection]$Connection

    [void]Connect
    {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $sqlConnection.ConnectionString = $connectionString
        $sqlConnection.Open()

        $this.Connection = $sqlConnection
    }
    
    [bool]IsConnected
    {
        return $this.Connection.State -eq [System.Data.ConnectionState]::Open
    }
}