Database Continuous Integration – DeployTest

Database Continuous Integration – DeployTest

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></SMTPServer> <SMTPPort>587</SMTPPort> <UseSsl>1</UseSsl> <SMTPUserName></SMTPUserName> <SMTPPassword>Someday13!</SMTPPassword> <From></From> <To></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>



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


Development | OneScript | PowerShell | Continuous Integration | SQL

Add comment

  Country flag

  • Comment
  • Preview

One Script

OneScript Continuous Integration for you database Scripts in version control are automatically combined into a release script.