Saturday, May 10, 2014

PowerShell automations with control database

Story behind this post

A while ago I noticed that it isn't possible create smart automations if you haven't way provide control data for scripts.
After lot of thinking and discuss with my colleagues I noticed that it would be useful have SQL database which contains all control data and all the scripts can read them from there.

On this blog post, I will explain how you can create simple server provision database and script which will automatically create new virtual machines using control data on database.

Plan and generate control database

I used DbSchema for generating this database schema. It's trial works 15 days with all features which is enough for generating this kind test databases.

Here is picture of DB and SQL script for create it.

CREATE DATABASE ServerProvision
GO
USE ServerProvision
GO
CREATE TABLE dbo.Server ( 
 Id                   int NOT NULL   IDENTITY,
 Name                 varchar(15) NOT NULL   ,
 Description          varchar(100)    ,
 Installed            bit NOT NULL CONSTRAINT defo_Installed DEFAULT 0  ,
 CONSTRAINT Pk_Server PRIMARY KEY ( Id )
 );


Deploy servers using control database

Following functions can be used for communicating with SQL server. You can just copy / paste them to PowerShell.
Function Connect-ControlDB {
 param (
  [Parameter(Mandatory=$True)]$SQLinstance,
  [Parameter(Mandatory=$True)]$Database,
  [Parameter(Mandatory=$True)]$SQLUser,
  [Parameter(Mandatory=$True)]$SQLPwd
 )
 $global:SQLConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$SQLinstance;Initial Catalog=$Database;User ID=$SQLUser;Password=$SQLPwd");
}

Function New-VMSchedule {
 param (
  [Parameter(Mandatory=$True)]$VMName,
  [Parameter(Mandatory=$True)]$VMDescription
 )
 If (!($SQLConnection)) { Connect-ControlDB }
 $SQLConnection.Open()
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand("INSERT INTO Server (Name,Description) VALUES ('$VMName','$VMDescription')", $SQLConnection);
 $SqlCmd.ExecuteNonQuery()
 $SQLConnection.Close()
}

Function Get-ScheduledProvisions {
 If (!($SQLConnection)) { Connect-ControlDB }
 $SQLConnection.Open()
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand("SELECT * FROM Server WHERE Installed = 0", $SQLConnection);
 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SqlAdapter.SelectCommand = $SqlCmd
 $DataSet = New-Object System.Data.DataSet
 $SqlAdapter.Fill($DataSet)
 $SQLConnection.Close()
 Return $DataSet.Tables[0]
}

Function Set-ProvisionDone {
 param (
  [Parameter(Mandatory=$True)]$Id
 )
 If (!($SQLConnection)) { Connect-ControlDB }
 $SQLConnection.Open()
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand("UPDATE Server SET Installed = 1 WHERE Id = $Id", $SQLConnection);
 $SqlCmd.ExecuteNonQuery()
 $SQLConnection.Close()
}
Then you can schedule server deployments using commands:
Connect-ControlDB -SQLInstance "SQLserver" -Database "ServerProvision" -SQLuser "SQLuser" -SQLPwd "SQLpwd"
New-VMSchedule -VMName "testserver1" -VMDescription "First provision test"
Then you would have example this kind script scheduled on Hyper-V server (of course you need include functions for above to it). Script reads scheduled installations from SQL, deploy them and mark ready.
Connect-ControlDB -SQLInstance "SQLserver" -Database "ServerProvision" -SQLuser "SQLuser" -SQLPwd "SQLpwd"
$VMs = Get-ScheduledProvisions
If ($VMs[0] -gt 0) {
 ForEach ($VM in ($VMs[1..$VMs.count])) {
  New-VM -Name $VM.Name
  Set-VM -Name $VM.Name -Notes $VM.Description
  Set-ProvisionDone -Id $VM.Id
 }
}

No comments:

Post a Comment