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
param([string]$Instance, [String]$IP, [String]$DomainSuffix = ".GLOBAL.COM")
#add the domain to the instance
$Instance = $Instance.Split("\") + $DomainSuffix + "\" + $Instance.Split("\")
$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"
#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:
Hope this helps in some way.
Thanks for reading.