Azure SQL How To - SQLVM

Task 1: For licensing purpose, collect the following information of all SQLVMs in the subscription and put the report in an Excel spreadsheet:

• Resource Group Name

• SQLVM Name

• SQLVM Version

• SQLVM SKU

• Number of Virtual cores

• SQLVM Size

Solution with PowerShell script:

Set-AzContext *
$Location = 'eastus2'

$OutPutFile = ".\PS-Write-Output.csv"
Clear-Content -Path $OutPutFile

# Get SQLVM info
$SQLVM = Get-AzSqlVM | Select-Object Name, Resourcegroupname, Sku, Offer | Sort-Object ResourceGroupName, Name
$SQLVMNames = $SQLVM.Name

# Get VM info
$AzureVM = Get-AzVm | Select-Object Name -ExpandProperty HardwareProfile | Sort-Object Name
$AzureVMSizeInfo = Get-AzVMSize -Location $Location | Select-Object Name, NumberOfCores

# First, Add column names as the first line in the csv file
Add-Content -Path $OutPutFile "ResourceGroupName;SQLVMName;SQLVMVersion;SQLVMSku;NumberOfVcores;AzureVMSize"

foreach ($SQLVMName in $SQLVMNames) {
    $SQLVMSku = ($SQLVM | Where-Object Name -eq $SQLVMName).Sku
    $SQLVMVersion = ($SQLVM | Where-Object Name -eq $SQLVMName).Offer
    $RGName = ($SQLVM | Where-Object Name -eq $SQLVMName).ResourceGroupName
    $AzureVMSize = ($AzureVM | Where-Object name -eq $SQLVMName).VmSize
    $NumberOfVcores = ($AzureVMSizeInfo | Where-Object name -eq $AzureVMSize).NumberOfCores
    $NewLine = $RGName + ";" + $SQLVMName + ";" + $SQLVMVersion + ";" + $SQLVMSku + ";" + $NumberOfVcores + ";" + $AzureVMSize
    Add-Content -Path $OutPutFile $NewLine
}

# Import the content of $OutPutFile to an Excel workbook

Task 2: Find out the following network information of all SQLVMs in the subscription and put the report in an Excel spreadsheet:

• Resource Group Name

• Virtual Network Name

• Subnet Name

• Private IP Address

The solution with PowerShell script:

Set-AzContext *

$OutPutFile = ".\PS-Write-Output.csv"
Clear-Content -Path $OutPutFile

$SQLVM = Get-AzSqlVM | Select-Object Name, Resourcegroupname
$SQLVMNames = $SQLVM.Name
Write-Output "ResourceGroupName;VMName;VNetName;VNetSubnetName;PrivateIP"
foreach ($SQLVMName in $SQLVMNames) {
    $RGName = ($SQLVM | Where-Object Name -eq $SQLVMName).ResourceGroupName
    
    $NICResource = (Get-AzVM -Name $SQLVMName).NetworkProfile.NetworkInterfaces 

    $AzureVMIPAddress = (Get-AzNetworkInterface -ResourceId $NICResource.Id).IpConfigurations.PrivateIpAddress
    $SubnetId = (Get-AzNetworkInterface -ResourceId $NICResource.Id).IpConfigurations.subnet.id 

$SubnetIDArray = $SubnetId.Split("/")
    
    $NewLine = $RGName + ";" + $SQLVMName + ";" + $SubnetIDArray[8] + ";" + $SubnetIDArray[10] + ";" + $AzureVMIPAddress

    Add-Content -Path $OutPutFile $NewLine
}

More coming ...