Powershell / Virtualization_2019

PowerShell script that can help you orchestrate SQL VMs with databases in a VMware environment

tapasmahanta124Leave a comment 
# Connect to vCenter Server
Connect-VIServer -Server <vCenter_Server_IP_Address> -User <Username> -Password <Password>

# Define the SQL VMs and their associated databases
$SQLVMs = @{
    "SQLVM1" = @("Database1", "Database2")
    "SQLVM2" = @("Database3", "Database4")
    # Add more SQL VMs and their databases as needed
}

# Loop through each SQL VM
foreach ($SQLVM in $SQLVMs.Keys) {
    $SQLVMDatabases = $SQLVMs[$SQLVM]

    # Power on the SQL VM
    Start-VM -VM $SQLVM -Confirm:$false

    # Wait for the SQL VM to power on
    do {
        Start-Sleep -Seconds 5
        $VM = Get-VM -Name $SQLVM
    } while ($VM.PowerState -ne "PoweredOn")

    # Loop through each database in the SQL VM
    foreach ($Database in $SQLVMDatabases) {
        # Perform any required actions on the database
        # For example, you can use the Invoke-SqlCmd cmdlet to execute SQL queries or scripts against the database
        Invoke-SqlCmd -ServerInstance $SQLVM -Database $Database -Query "SELECT * FROM TableName"
        # Add more actions as needed
    }

    # Power off the SQL VM
    Stop-VM -VM $SQLVM -Confirm:$false
}

# Disconnect from vCenter Server
Disconnect-VIServer -Server <vCenter_Server_IP_Address> -Confirm:$false

Write-Host "SQL VM orchestration completed."

Make sure to replace “, “, and “ with your actual vCenter Server details. In this script, you define the SQL VMs and their associated databases in the `$SQLVMs` hashtable. Each SQL VM is a key in the hashtable, and the value is an array of database names. The script then loops through each SQL VM, powers it on, waits for it to be powered on successfully, and performs any required actions on each database. In the example, it uses the `Invoke-SqlCmd` cmdlet to execute a SELECT statement against each database. You can modify this section to perform any other actions you require, such as backups, restores, or database maintenance tasks. After performing the actions on the databases, the script powers off the SQL VM and proceeds to the next SQL VM in the loop. Finally, the script disconnects from the vCenter Server. Remember to adjust the script as per your specific SQL VM and database configuration and requirements.

Leave a comment