RunMD3.ps1 7.17 KB
param (
	[Parameter(Position=0, Mandatory=$true)] [string]$ServerName,   #(local), SomeServer, SomeServer\InstanceName
	[Parameter(Position=1, Mandatory=$true)] [string]$DatabaseName,
	[Parameter(Position=2, Mandatory=$false)] [bool]$ScriptOnly  #do not apply to db, only generate the script
)
################################################################################
#  RunMD3.ps1
#  
#  MD3 = Metadata Driven Database Deployments
#
#  This PoSH script will run all .sql files in the folder that this .ps1 file is found, and all
#  subdirectories.  Specifically it runs the FILES first, then the FOLDERS in the given directory, 
#  alphabetically.  

#  Any folder prefaced with "_" (underscore) is skipped (including sub folders and files)
#
#  Whenever a RAISERROR is encountered this PoSH script will detect that and immediately
#  fail the deployment.  It is up to the installer to fix the issues and redeploy.  
#
#  To Execute:
#    cd to the directory with RunMD3.ps1
#    powershell .\runMD3.ps1 -ServerName "Something" -DatabaseName "SomeDB"
# 
#  System Requirements: 
#    As with any PoSH script, you may need to run powershell Set-ExecutionPolicy Unrestricted first
#    MD3 requires sqlcmd to be in PATH, so you'll need to run this from a machine that has SQL installed on it.  
#
################################################################################

cls

# Always fail the PoSH script on ANY error
$script:ErrorActionPreference = "Stop"

$workingDirectory = (Split-Path $MyInvocation.MyCommand.Path)
$CurrentSQLScriptDirectory = $workingDirectory
$ScriptFile = "DeploymentScript.sql"
$ScriptFilePath = (Join-Path $workingDirectory $ScriptFile)

################################################################################
#     Helper Functions
################################################################################

# runs sqlcmd for a QUERY
function RunSqlCmdForQuery {
	param (
		[Parameter(Position=0, Mandatory=$true)] [string]$Query  #with path
	)
	
	#invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -AbortOnError -InputFile ".\00CreateDatabase.sql" -OutputSQLErrors 1 -Verbose
	# -r1 has no equivalent in invoke-Sqlcmd
	sqlcmd -S $ServerName -d $DatabaseName -b -Q $Query -r1 -E
	$lec = $LastExitCode
	if ($lec -ne 0){
		throw "Previous command failed for Query: $Query"
	}
	
	return

}

# runs sqlcmd for a FILE
function RunSqlCmdForFile {
	param (
		[Parameter(Position=0, Mandatory=$true)] [string]$FileName  #with path
	)
	
	#invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -AbortOnError -InputFile ".\00CreateDatabase.sql" -OutputSQLErrors 1 -Verbose
	# -r1 has no equivalent in invoke-Sqlcmd
	sqlcmd -S $ServerName -d $DatabaseName -b -i $FileName -r1 -E
	$lec = $LastExitCode
	if ($lec -ne 0){
		throw "Previous command failed for File: $FileName"
	}
	
	return

}

# this function performs logging from within PoSH, not SQL
function InsertMD3LogMessage {
	param (
		[Parameter(Position=0, Mandatory=$true)] [string]$Message,
		[Parameter(Position=1, Mandatory=$true)] [string]$Severity,
		[Parameter(Position=2, Mandatory=$false)] [string]$ScriptName
	)

	# Escape single quotes
	$Message = $Message.Replace("'", "''")
	$Severity = $Severity.Replace("'", "''")
	$ScriptName = $ScriptName.Replace("'", "''")

	[string]$qryText = "IF OBJECT_ID('MD3.InsertLogMessage') IS NOT NULL EXEC MD3.InsertLogMessage @Message = '$Message', @Severity = '$Severity', @ProcedureName = 'RunMD3.ps1', @ScriptName = '$ScriptName'"

	RunSqlCmdForQuery $qryText
	
	return
}

# this function performs logging by injecting a dynamic SQL string
function InsertSQLforMD3LogMessage {
	param (
		[Parameter(Position=0, Mandatory=$true)] [string]$Message,
		[Parameter(Position=1, Mandatory=$true)] [string]$Severity,
		[Parameter(Position=2, Mandatory=$false)] [string]$ScriptName
	)

	# Escape single quotes
	$Message = $Message.Replace("'", "''")
	$Severity = $Severity.Replace("'", "''")
	$ScriptName = $ScriptName.Replace("'", "''")

	[string]$qryText = "IF OBJECT_ID('MD3.InsertLogMessage') IS NOT NULL EXEC MD3.InsertLogMessage @Message = '$Message', @Severity = '$Severity', @ProcedureName = 'RunMD3.ps1', @ScriptName = '$ScriptName'"

	return $qryText
	
	return
}

function RunCreateDbScript {

	#needs to be a separate script and process because we are connecting to master to do the work.  

	$sqlScript = Join-Path $workingDirectory "00Pre-install\00CreateDatabase.sql"
    "Running script $sqlScript" | Out-Host
	
	sqlcmd -S $ServerName -d "master" -b -i $sqlScript -r1 -E
	$lec = $LastExitCode
	if ($lec -ne 0){
		throw "Previous command failed for master File: $sqlScript"
	}
}

function Recurse-Folders {
	param (
		[Parameter(Position=0, Mandatory=$true)] [string]$folderspec
	)
	
	InsertMD3LogMessage -Message "Running folder...$folderspec" -Severity "I" -ScriptName "RunMD3.ps1"
	
	## new method
	[string]$MergedOutput = "`n"
	
	# within a folder, process FILES first, then subfolders
	foreach ($sqlScript in Get-ChildItem -Path $folderspec -Filter "*.sql" | Sort-Object Name) {

		$sqlScript = $sqlScript.FullName
		$MergedOutput += "`n GO `n"
		$MergedOutput += (InsertSQLforMD3LogMessage -Message "Running script...$sqlScript" -Severity "I" -ScriptName $sqlScript)
		$MergedOutput += "`n GO `n"
		$MergedOutput += [System.IO.File]::ReadAllText("$sqlScript")
		$MergedOutput += "`n GO `n"
		
	}
	
	#check for Open transactions
	$MergedOutput += "IF OBJECT_ID('MD3.OpenTransactionsCheck') IS NOT NULL EXEC MD3.OpenTransactionsCheck"
	
	# Append this code to existing ScriptFile
	$MergedOutput | Out-File -Encoding "Unicode" -FilePath $ScriptFilePath -Append
	
	# now, starting with folderspec, traverse the directory tree , skip all folders that start with _ (underscore)
	foreach ($subfolder in (Get-ChildItem $folderspec | Where-Object {$_.PSIsContainer -and $_.Name -notlike "_*"}) | Sort-Object Name){
		$subfolder = $subfolder.FullName

		Recurse-Folders $subfolder
	
	}
	
}

################################################################################
#     Main
################################################################################
Try
{
	[int]$exitCode = 0

	# Run the "master only" script 
	RunCreateDbScript
	
	# Prepare the ScriptFile
	"`n" | Out-File -Encoding "Unicode" -FilePath $ScriptFilePath -Force  
	
	
	InsertMD3LogMessage -Message "Starting MD3 deployment" -Severity "I" -ScriptName "RunMD3.ps1"
	
	# Merge all .sql files, then subfolders, alphabetically
	Recurse-Folders $CurrentSQLScriptDirectory
	
	# Now run all files
	RunSqlCmdForFile $ScriptFilePath
	
	InsertMD3LogMessage -Message "Completed MD3 deployment" -Severity "I" -ScriptName "RunMD3.ps1"
	Write-Host "MD3 has completed with success.  WARNINGs are displayed above."

	$exitCode = 0
}
Catch
{
	Write-Host "Error running script: $sqlScript" -ForegroundColor Red

	# Loop through all errors
	$currentException = $_.Exception

	While ($currentException) {
		Write-Host $currentException.Message
		$lastException = $currentException
		if ($currentException.GetType().Name -eq "SqlException") {break}
		$currentException = $currentException.InnerException
	}
	
	Write-Host "Error:  deployment FAILED" -ForegroundColor Red
	$exitCode = 1
}
Finally {
	$script:ErrorActionPreference = "SilentlyContinue"
	foreach ($err in $Error) {Throw $err}
	Exit $exitCode
}