SQL Server Powershell – Checking SQL Server connectivity using powershell

Alright!! Powershell requirement are coming very frequently these days to me. As I am new to it, I decided to try some basic things. Here is the one.

Problem Statement – How to check whether SQL server is running from remote machine using powershell?

Applicable technology – SQL Server 2008 and above, Windows powershell.

The first step that we will perform is to change the execution policy of powershell of the machine on which we will be running following powershell code.

The Set-ExecutionPolicy cmdlet changes the user preference for the Windows PowerShell execution policy. The execution policy is part of the security strategy of Windows PowerShell.

RemoteSigned: Requires that all scripts and configuration files downloaded from the Internet be signed by a trusted publisher.

The code in powershell to check SQL Server connectivity is as follows –

#set execution policy

set-executionpolicy remotesigned

function IsSQLDBAvailable([string] $SQLServer)

{

            try

            {

                        $Connection = New-Object System.Data.SQLClient.SQLConnection

                        $Connection.ConnectionString = "server=$SQLServer;Database=Master;trusted_connection=true;"

                        $Connection.Open()

                        return $true;

            }

            catch [System.Exception]

            {

                        return $false;

            }

}

$SSODB = $False

while ($SSODB -eq $False)

{

$SSODB = IsSQLDBAvailable ("Your SQL Server Name\ IP \Your SQL Server named instance")

            start-sleep -s 2

            write-host "Waiting for SQL DB ..."

}

write-host " Connected to SQL server - $SSODB `r`n"

Run above code in powershell on machine from where you want to check the SQL server connectivity.

If you stop the MSSQLSERVER Service on SQL Server machine then, you will keep receiving message as “Waiting for SQL DB…” The instance you start the MSSQLSERVER service, the result will be true.

Cheers…

Please give food to all my fishes swimming at the bottom. It's fun!! Try it!!
Thanks for reading!!

Happy Coding!!