VMware Cloud Community

Get SQL Server and Database information before migrating MSSQL 2008 to MSSQL 2016

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


#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}



#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




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?

0 Kudos
1 Reply

There are a few questions in here, let's try with the beginning.

You could try to use the Start-Job cmdlet to run these queries as background tasks.

That at least produces a certain level of parallelism in the script.
Can you try that?

Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos