Trying to build powercli tool, to collect Database Server information before making migration plan.
I need a MSSQL Server and Database overview, By VLANs.
I made an attempt, but it's very slow and bulky. Can it be made quicker/Less bulky? Can't figure out how to speed things up with get-view/parallel/async please help!
#Get PoweredOn Windows VMs in selected VLAN --> Result 47 VMs in 38 sec
$SelectedVLAN = "2033_dmz1"
$VMGuestInfo = Get-Cluster *-windows | Get-VM | where {$_.PowerState -match "PoweredOn"} | where { ($_ | Get-VirtualPortGroup | where {$_.name -match $SelectedVLAN})}
$VMGuest = $VMGuestInfo.name
$VMGuest
#Get MSSQL servers from above list --> Result 7 VMs out of 47 with SQL in 5 min (can this be run in parallel/async)
$svcName = "mssqlserver"
$scriptoutput = @()
$VMSWithMSSQL = @()
$guestScript = "(get-service $svcName | ConvertTo-CSV -NoTypeInformation)"
foreach ($VM in $VMGuest)
{
#$scriptoutput += $VM
$scriptoutput = (Invoke-VMScript -vm $VM -scriptText $guestScript -GuestUser 'XXXXXXX' -GuestPassword 'XXXXXXX' -WarningAction SilentlyContinue | ConvertFrom-CSV).name
$VMSWithMSSQL += if($scriptoutput -eq "$svcName"){$VM}
}
$VMSWithMSSQL
#Get SQL information --> Result 3 Database servers returning databases 4 with
$result = @()
$Report = @()
$result = $VMSWithMSSQL | ForEach-Object `
{
$Server = $_
$Item = (Invoke-VMScript -VM $Server -ScriptText {Invoke-Sqlcmd -ServerInstance $Server -Database master -query "(SELECT name FROM [master].[sys].[databases] WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource'))"} -GuestUser 'XXXX' -GuestPassword 'XXXXXXX' –WarningAction SilentlyContinue).ScriptOutput
$Item1 = $item.Split('',[System.StringSplitOptions]::RemoveEmptyEntries)
$Item2 = $Item1
$Item3 = $Item2[2..$Item2.count]
New-Object PSObject -Property @{
Server = $Server
Databases = $Item3
}
}
$result
Resulting report on single VLAN: 2033_dmz1
Server : DBServer01
Databases : {DB1, DB2, DB3, DB4}
Server : DBServer02
Databases : {'Invoke-Sqlcmd', is, not, recognized...}
It looks like User/Password isn't correct for Server showing "Databases : {'Invoke-Sqlcmd', is, not, recognized...}"
What would be the best way to make credentials check/Pick another from list, before proceeding with the SQL query?
How can I get the following information into my report: Database server version, Database collation, Database collation and Containment type?
