by JBrooks
23. February 2017 15:44
Part 4 of 4 – Main PowerShell Script
You can download all of the code here. Below is the main PowerShell script called Process-InBasket.ps1. It might be scheduled to run every few minutes. It will look for new commands and if any it will call OneScriptClient passing in the parameters found in the command.
Then it will run if it finds a new *.sql file in the In-Basket directory. It will restore a resent backup of production, apply the script and then email the developers with “Success” or “Failed” in the subject. The body of the email will be the log and attached will be a file with the results of the script running.
<#
.SYNOPSIS
Process new SQL Scripts files by applying them to a SQL database.
.DESCRIPTION
Process-InBasket loads a Config file that controls all variables in the process.
It then looks to a directory to search for new *.SQL files.
Once a new file is found, a backup of the database is restored to SQL Server.
The script is then applied.
The process then looks for an SQL errors.
Then it sends an email of the log with "Success" or "Failure" in the subject.
.PARAMETER ConfigFile
The config file to use to process the deployment. Default: ./TestDeployConfig.confg
.EXAMPLE
PowerShell "C:\Work\Deploy\Process-InBasket.ps1" ./TestDeployConfig.xml
#>
[CmdletBinding()]
param (
[string] $ConfigFile='./TestDeployConfig.xml'
)
cls
$VerbosePreference = 'Continue'
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
# get a temp file name so we can start logging
$Private:logFileName = [IO.Path]::GetTempFileName()
Set-Location $PSScriptRoot
# load the support scripts
. ".\DeployUtils\Load-DeployConfig.ps1"
. ".\DeployUtils\SQLServer.ps1"
. ".\DeployUtils\Utils.ps1"
Log "Root: $PSScriptRoot" $logFileName
Log ("Loading Config File $ConfigFile") -LogFileName $logFileName
#This will also create and load C.FileSettings.LogFileName
$Config = Load -ConfigFileName $ConfigFile -LogFileName $logFileName
Log ""
Log ("Processing Matches: "+ $Config.C.FileSettings.InFileMask.InnerText)
$Private:processCount = 0
$Private:ret;
foreach($Private:CmdFile in (Get-ChildItem $Config.C.DirSettings.InCmdDir.InnerText))
{
$content = Get-Content $CmdFile.FullName
$cmdArgs = $content[0]
$author = $content[1]
$cmd = '"C:\Program Files\DigitalTools\OneScript Client\OneScriptCMD.exe" ' + `
$cmdArgs + ' -do ' + $Config.C.DirSettings.InBasketDir.InnerText
Invoke-Expression "& $cmd"
Move-Item -Path "$($CmdFile.FullName)" -Destination `
$Config.C.DirSettings.ArchiveDir.InnerText -Force
}
foreach($Private:SQLFile in `
(Get-ChildItem $Config.C.FileSettings.InFileMask.InnerText))
{
Log ""
Log ("Processing: "+ $SQLFile)
$Private:dt = Get-Date -Format yyyyMMdd_HHmmss
$Private:temp = $Config.C.FileSettings.LogFileFormat.InnerText.Replace("{0}", $dt)
if($processCount -eq 0)
{
Log 'Changing log file to $temp'
Move-Item -Path $logFileName -Destination $temp
$Config.C.FileSettings.LogFileName = $temp
$Private:DBSourceFile = Get-ChildItem `
$Config.C.FileSettings.DBSourceFileMask.InnerText | `
Sort-Object Name -Descending | Select-Object $_.Name -First 1
Log ("SQL Backup File: " + $DBSourceFile)
if(!$DBSourceFile)
{
Write-Error ("Searched: " + `
$Config.C.FileSettings.DBSourceFileMask.InnerText)
Write-Error "No SQL Backup File Found."
Log "No SQL Backup File Found."
Send-ToEmail -Subject "Deploy Failed!, No SQL Backup File Found" `
-LogFileName $Config.C.FileSettings.LogFileName
exit -1
}
}
else
{
$Config.C.FileSettings.LogFileName = $temp
Log(("Processing file number: " + ($processCount + 1).ToString()))
Log ("")
}
Log ("Log File: " + $Config.C.FileSettings.LogFileName)
$Private:buildResultsFile = `
$Config.C.FileSettings.BuildResultsFileFormat.InnerText.Replace("{0}", $dt)
Log ("Build Results File: " + $buildResultsFile)
$ret = RestoreSQLServer $Config.C.DBSettings.Server $Config.C.DBSettings.Database `
$Config.C.DirSettings.DBFilesDir.InnerText $DBSourceFile
if($ret -ne $true)
{
Write-Error "Restore Database Failed"
Log "Restore Database Failed"
Send-ToEmail -Subject "Deploy Failed!, Restore Database Failed" -LogFileName `
$Config.C.FileSettings.LogFileName
exit -1
}
Log ("Running: "+ $SQLFile)
$ret = RunSQLFile $Config.C.DBSettings.Server $Config.C.DBSettings.Database `
$SQLFile $buildResultsFile
if($ret -ne $true)
{
Write-Error "Running SQL Script Failed"
Log "Running SQL Script Failed"
Send-ToEmail -Subject "Deploy Failed!, Running SQL Script Failed" `
-LogFileName $Config.C.FileSettings.LogFileName
exit -1
}
Log "SQL Script Run Successful"
$Private:hasError = CheckForErrors $buildResultsFile
if($hasError -eq $true)
{
Write-Error "SQL Script Had Errors"
Log "SQL Script Had Errors"
Send-ToEmail -Subject "Deploy Failed!, SQL Script Had Errors" `
-LogFileName $Config.C.FileSettings.LogFileName `
-Attachmentpath $buildResultsFile
}
else
{
Log (("Deployment Successful"))
Log (("Completed " + $SQLFile))
Send-ToEmail -Subject "Deploy Script Successful" -LogFileName `
$Config.C.FileSettings.LogFileName -Attachmentpath "$buildResultsFile";
}
Move-Item -Path "$buildResultsFile" -Destination `
$Config.C.DirSettings.ArchiveDir.InnerText -Force
$SQLFile = $SQLFile -replace ".sql$", ".*"
Move-Item -Path "$SQLFile" -Destination `
$Config.C.DirSettings.ArchiveDir.InnerText -Force
$processCount = $processCount + 1
}
switch($processCount)
{
0 {Log (("No files found to process"))}
1 {Log (("1 Script Processed"))}
default {Log (($processCount.ToString() + " Scripts Processed"))}
}
by JBrooks
17. February 2017 17:47
Part 3 of 4 - Directories and Configuration
Below are the default directories I use. These could all be under c:\DeployTest or something.
| Directory | Type of Files |
| Archive | Completed files are moved to once processed |
| DatabaseFiles | SQL Server database data files |
| DBBackup | Resent backups of production for restoring |
| DeployUtils | PowerShell support utilities |
| InBasket | Incomming files needing to be processed |
| InCmds | Incomming commands used to trigger processing |
| Logs | Log files |
| Working | Process working files |
|
I didn't want to hard code the directory paths because I could make changes like moving the Archive directory to a shared drive like "\\MyShare\MyProject\Archives". I will also have multiple project using my PowerShell code so having config files makes it easier to work with.
The config file for the directories above looks like:
<?xml version="1.0" encoding="UTF-8" ?>
<C>
<DirSettings>
<BaseDir>C:\DeployTest\</BaseDir>
<InBasketDir Parent="BaseDir">InBasket\</InBasketDir>
<WorkingDir Parent="BaseDir">Working\</WorkingDir>
<DBSourceDir Parent="BaseDir">DBBackup\</DBSourceDir>
<ArchiveDir Parent="BaseDir">Archive\</ArchiveDir>
<DBFilesDir Parent="BaseDir">DatabaseFiles\</DBFilesDir>
<InCmdDir Parent="BaseDir">InCmds\</InCmdDir>
<LogDir Parent="BaseDir">Logs\</LogDir>
</DirSettings>
<FileSettings>
<InFileMask Parent="InBasketDir">Northwind_Update_*.sql</InFileMask>
<DBSourceFileMask Parent="DBSourceDir">Northwind_*.bak</DBSourceFileMask>
<BuildResultsFileFormat Parent="LogDir">BuildResults_{0}.log</BuildResultsFileFormat>
<LogFileFormat Parent="LogDir">TestDeploy_{0}.log</LogFileFormat>
</FileSettings>
<DBSettings>
<Server>.</Server>
<Database>Northwind</Database>
<BackupDB>0</BackupDB>
</DBSettings>
<EmailSettings>
<SendEmails>1</SendEmails>
<SMTPServer>smtp.gmail.com</SMTPServer>
<SMTPPort>587</SMTPPort>
<UseSsl>1</UseSsl>
<SMTPUserName>JimInDE@GMail.com</SMTPUserName>
<SMTPPassword>Someday13!</SMTPPassword>
<From>JBrooks@DigitalTools.com</From>
<To>JBrooks@DigitalTools.com</To>
</EmailSettings>
</C>
Typically you would have one of these per project. This might be called something like NorthwindDeployTest.xml. Notice that there is a "BaseDir" element and then other directories are a relative path under it. They have an attribute Parent="BaseDir". This can be removed if you want to use a different directory not under the BaseDir.
So our example where archives were moved to a shared drive would change:
<ArchiveDir Parent="BaseDir">Archive\</ArchiveDir>
To:
<ArchiveDir>\\MyShare\MyProject\Archives\</ArchiveDir>
Now all that we need is a PowerShell script to read this XML config file and then process it. One of the files under DeployUtils is Load-DeployConfig.ps1 and that is what it does.
Function Load {
param (
[string] $ConfigFileName = 'C:\Work\Deploy\TestDeployConfig.xml',
[string] $LogFileName
)
Write-Host "this is it: $LogFileName"
# Do not make $Config private, it is used by Log()
Log ("Loading Config File $ConfigFileName") -LogFileName $LogFileName
[xml]$Config = Get-Content $ConfigFileName
Log 'Adding LogFileName to Config' $LogFileName
$logFileNode = $Config.SelectSingleNode('//C/FileSettings/LogFileName')
if(!$logFileNode)
{
$Config.c.FileSettings.AppendChild($Config.CreateElement("LogFileName"))
}
$Config.C.FileSettings.LogFileName = $LogFileName
Write-Host "Loaded: $($Config.C.FileSettings.LogFileName)" $LogFileName
#Now do not need the optional LogFileName since it defaults to $Config.C.FileSettings.LogFileName
Log ""
Log "Process Config File"
#fill in the parent for all of the elements with a Parent attribute
foreach($setting in $Config.SelectNodes("//*[@Parent]"))
{
Log "$($setting.InnerText) ==>"
$parent = $Config.C.DirSettings.SelectSingleNode("//" + $setting.Parent)
# if($parent -ne $null)
$setting.InnerText = $parent.InnerText + $setting.InnerText
Log "`t`t$($setting.InnerText)"
}
Log ""
return $Config
}
In your main PowerShell script you would use this to load the config like:
$Config = Load -ConfigFileName $ConfigFile -LogFileName $logFileName
Then you can use the variables like:
Move-Item -Path "$SQLFile" -Destination $Config.C.DirSettings.ArchiveDir.InnerText
by JBrooks
12. February 2017 17:36
Part 2 of 4 - Commit Hook
I’ve created a simple Subversion post commit hook by creating a batch file called post-commit.bat and saving it to my …\SVN\hooks directory. This batch file only has 1 line that calls a PowerShell script:
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -noprofile -executionpolicy bypass -file C:\DeployTest\DeployUtils\CreateOneScriptCMD.ps1 "%1" "%2"
(This should all be on a single line.) If you run this to test you need to “Run as Administrator.”
The arguments passed to the batch file are just passed to the PowerShell script file. The %1 is the path to the Subversion repository and the %2 is the revision number just created by the commit.
The CreateOneScriptCMD.ps1 PowerShell script will look at the directories just committed and then create a text file with the appropriate OneScript Command arguments. I then have a scheduled process that runs every few minutes that will see this new file and then do the real work. I did it this disconnected way so the developer doesn’t have to wait for the full process to complete when he does a commit.
Below is the CreateOneScriptCMD.ps1 script
param (
[Parameter(Mandatory=$true)]
[string]$REPOS,
[Parameter(Mandatory=$true)]
[string]$REV
)
Set-Location $PSScriptRoot
cls
$VerbosePreference = 'Continue'
#hash table of paths to arguements found in OneScriptClient
$Private:mapPathToArgs = @{"NorthwindDB/branches/" = "-s 1 -p 3 -fb -ra -b {0}" }
$Private:index = -1
$Private:branchPath
$Private:author
$Private:args
foreach($Private:dir in svnlook dirs-changed $REPOS --revision $REV) {
Write-Verbose "Checking $dir"
foreach($Private:key in $mapPathToArgs.keys)
{
$index = $dir.indexOf($key)
Write-Verbose "index = $index"
if($index -gt -1) {
Write-Verbose "matched $key"
# $author = svnlook author $REPOS --revision $REV
# Write-Verbose "Author = $author"
Write-Verbose "Matched $dir"
$index = $index + $key.Length
Write-Verbose "New index $index"
$len = $dir.indexOf("/", $index) - $index
Write-Verbose "Len = $len"
$branchPath = $dir.Substring($index, $len)
Write-Verbose "Branch is $branchPath"
$args = $mapPathToArgs[$key] -f $branchPath
break
}
}
if($args)
{
break
}
}
if($args)
{
$Private:dt = Get-Date -Format yyyyMMdd_HHmmss
$Private:cmdFileName = "..\InCmds\OneScriptCmd_$dt.txt"
$args | Out-File $cmdFileName -Append
# $author | Out-File $CmdFileName -Append
}
This will generate a file named something like: C:\DeployTest\InCmds\OneScriptCmd_20170212_215709.txt
And its content will be a single line containing the arguments to use when creating the release script from OneScriptCMD in the next step. Its content will look something like:
-s 1 -p 3 -fb -ra -b "3.2"
These are the same arguments created from within the OneScript Client Windows program.
by JBrooks
5. February 2017 06:23
Part 1 of 4 – Overview
For the next few blog posts I’m going to walk through having our new product OneScript creating a SQL release script automatically after a Subversion commit and then have a process that will automatically do the following:
- Restore a resent backup of our production database to our Deployment Test SQL Server instance.
- Apply the release script that OneScript just generated to the database.
- Determine if applying the script had any errors.
- Send an email to the developers with “Success” or “Failed” in the subject line. Have the process log as the body of the email and have the results of applying the SQL release script as an attachment.
This will tell the developers almost immediately if the SQL they just checked in causes an error in the release script.
You can download all of the completed code from here.
Most of this is done with PowerShell. If you don’t know PowerShell I highly encourage you to learn it. It is easy to learn and the next version of Windows will not have the command anymore - only PowerShell. I recommend the book below.
Learn Windows PowerShell in a Month of Lunches
Subscribe