RunMD3.ps1 6.94 KB
Newer Older
dwentzel's avatar
dwentzel committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214
param (
	[Parameter(Position=0, Mandatory=$true)] [string]$ServerName,   #(local), SomeServer, SomeServer\InstanceName
	[Parameter(Position=1, Mandatory=$true)] [string]$DatabaseName
)
################################################################################
#  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

################################################################################
#     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"
	
	# Run the script on the server
	$MergedOutput | Out-File -Encoding "Unicode" -FilePath (Join-Path $folderspec "_tempMD3.tmp")  -Force  
	RunSqlCmdForFile (Join-Path $folderspec "_tempMD3.tmp")
	Remove-Item (Join-Path $folderspec "_tempMD3.tmp")
		
	
	
	# 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
	
	InsertMD3LogMessage -Message "Starting MD3 deployment" -Severity "I" -ScriptName "RunMD3.ps1"
	
	# Run all .sql files, then subfolders, alphabetically
	Recurse-Folders $CurrentSQLScriptDirectory
	
	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
}