PowerShell / Microsoft.SqlServer.Smo – Is Server up?

Background

Stole a code for my Bro A.M.

Hit him up on Linked In to give him on credit.

Relating to these Industry luminaries on a different level now, no more bootlegging.

Legit Stealing.

Wish the same on the next ones behind me on the Street; swear on my Mama.

Code

Outline

  1. param
    • Accept the following parameters
      • $sqlInstance
  2. Strict Mode
    • Version 2
  3. Declare Variables
    • $bSQLInstanceConnected ( Boolean )
  4. Load Modules
    • Microsoft.SqlServer.Smo
      • Partial Name
      • Using Partial Name as it is safe in this case
  5. Instantiate Object
    • Type Microsoft.SqlServer.Management.SMO.Server
    • Parameters
      • $sqlInstance
    • Result is saved in variable $srv
  6. Capture $srv.Information.Version
  7. If $srv.Information.Version is null, then tag connection as failed
  8. Else display $srvInformation.Version

PowerShell

param (
    [Parameter(Mandatory=$true)][string]$sqlInstance
)
#Strict Mode
Set-StrictMode -Version 2.0

[Boolean]$bSQLInstanceConnected = $false;

#Load
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

#Get a server object which corresponds to the passed in sql server instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $sqlInstance

#Server Information Version
$srvInfoVersion = $srv.Information.Version

if ( ($srvInfoVersion -eq $null) )
{

	$bSQLInstanceConnected = $false;

}
else
{

	$bSQLInstanceConnected = $true;

}

if ($bSQLInstanceConnected -eq $true )
{

	$log = "Connected to SQL Instance {0}" -f $sqlInstance;

	Write-Host ($log);

	$srvInfoVersionMessage = "`tSQL Information Version :- {0}" -f $srv.Information.Version

	Write-Host $srvInfoVersionMessage

}
else
{

	$log = "SQL Instance {0} is not available" -f $sqlInstance;

	Write-Host ($log);

}

Output

localhost.connected.20190719.0926PM

Source Code

GitHub

DanielAdeniji/SQLServerPowershellSMO
Link

 

References

  1. Microsoft
    • Docs > .NET > .NET API Browser > Microsoft.SqlServer.Management.Smo
      • Namespaces
        Link
      • Namespace
        Link
      • Class – Server
        Link

        • Property – Server.Information
          Link
        • Property – Server.ConnectionContext
          Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s