Making life easy with PowerShell Functions

In my last blog, I showed you how to quickly update the Log retention for SQL server, and made it look even easier by using a Profile loaded function that gets me an instance of a SQL server SMO object to manipulate, This was a one line essentially:

gss -ComputerName SERVER1

The gss stands for Get-SQLServer (obviously). Since the last blog, I have amended the function slightly to allow for more exact server location, and replaced the -ComputerName parameter, with an -Instance Parameter.

The full code for the function is as below:

#simplify SQL SMO server retrieval
function Get-SQLServer
{
param([string]$Instance, [String]$IP, [String]$DomainSuffix = ".GLOBAL.COM")

#add the domain to the instance
if ($Instance.Contains("\"))
{
$Instance = $Instance.Split("\")[0] + $DomainSuffix + "\" + $Instance.Split("\")[1]
}
else
{
$Instance = $Instance + $DomainSuffix
}

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $Instance -ErrorAction Continue
if (($server.versionString -eq $null) -and $IP.Contains("."))
{
#couldnt open using FQ instance name, then try IP
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $IP -ErrorAction Continue
}
if ($server.versionString -eq $null)
{
Write-Host "Unable to connect to SQL Server: $Instance"
}
else
{
return $server
}
}

#add alias for Get-SQLServer
New-Alias gss Get-SQLServer

Once this is in your profile (I won’t cover old ground by telling you how to load functions in your profile, this excellent article by Ed Wilson himself! will get you going) you can simply call the function as below:

--using full function name
$server = Get-SQLServer -Instance SERVER1

--or using alias for a multi domain network
$server = gss -Instance SERVER2 -DomainSuffix .DOMAIN2.COM

I included an -IP parameter as DNS issues on some of our legacy networks only allow connection via IP, but if you have a fully working DNS and network, you can leave this out.

This function has a reliance on the Microsoft.SqlServer.SMO libraries, so if you use it, you have to put in a reference to them, as below:

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)

Hope this helps in some way.

Thanks for reading.

Advertisements
This entry was posted in SQL Server and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s