One of the things I love about PowerShell is how versatile it is, its a wonderful framework which can slot pieces of a puzzle together to create a masterpiece, Now I know it exists I can see every day uses and needs for PowerShell, it fits into every nuck and cranny.

Take for example my SnapReminder script, the fact that you can pull VMware Information from your vCenter, then run the results by Active Directory and finally push it through an SMTP server to complete the final piece of the puzzle astounds me, the only limit to PowerShell is your imagination.

The other day I was watching Twitter when someone asked how to do a particular task, they wanted to pull information from the VMware Infrastructure and update some existing data in an  SQL database, I watched as multiple answers were given to this person…. do a database synchronisation…..buy this  third party tool…..you cant do that…..extract it as a csv, convert it to xml, import it into here, spin around three times and say alacasam !

My instant answer was of course PowerShell (that seams to be my instant answer for most things of late).  The reply was, can you access SQL with PowerShell then ?  YES

I don’t think I personally have come across anything you couldn’t do in PowerShell given enough time and resources !  Lets talk through this scenario.

So firstly we know we need to push some information into a database, a simple generic function in PowerShell (searched Google and amended a little) gives us a nice function that will send a SQL statement to the database, this is a multi functional function as it can both be issued with read, write and modify statements….

Function Connect-SQL ($SqlQuery) {
    # Set the SQL Server
    $SqlServer = "MyDBServer";

    # Set the Database
    $SqlCatalog = "MyTable";

    # Setup SQL Connection
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

    # Setup SQL Command
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection

    # Setup .NET SQLAdapter to execute and fill .NET Dataset
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet

    #Execute and Get Row Count
    $nRecs = $SqlAdapter.Fill($DataSet)

    if ($nRecs -gt 0)
    {
        # Do Stuff
        $dataSet.Tables | Select-Object -Expand Rows
    }
}

All we need to do now is to get some information from the VMware Infrastructure, and anyone who reads my blog will know how to do that… PowerCLI.

So lets say for our example that we would like to update the table of existing information in our database to store some VM information, which host the VM is located on, whether the VM is powered On or Off etc.  We can do this in a simple for loop, pulling and pushing the data using our pre-defined function…

Connect-VIServer MyVIServer

Foreach ($VM in Get-VM){

    # Set the SQL read statement
    $SqlQuery = "SELECT * FROM server WHERE name='$($VMName)' ORDER BY Name"

    # Retrieve the record from the Database
    $Info = Connect-SQL $SqlQuery | Select Name, Location, Poweredup, CompID

    # If the record doesnt exist
    If ($Info -eq $null){
        # Write a notification to the screen
        Write-Host "$VMName Does not exist in the Database"
    }
    Else {
        # Get the Powered on setting ready
        If ($VM.PowerState -eq "PoweredOn"){
            $Poweredup = "True"
        }
        Else {
            $Poweredup = "False"
        }

        # Set the update statement
        $SqlWrite = "UPDATE server SET Poweredup='$Poweredup', location='$($VM.Host)' WHERE compID='$($Info.CompID)'"
        Write-Host "$VMName Updated"

        # Write the record back to theDatabase
        Connect-SQL $SqlWrite
    }
}

And there we have it, a simple script which can automatically pull the information straight out of PowerCLI and dump it back into a database, run this is a scheduled task and you will never have to think about it again.

Just think, if this kind of thing can be done in so little lines, how many other corporate applications can you think of where you would like to sync the data on a regular basis ?!  Your imagination is the door which will open this wonderful language.