· 6 years ago · May 12, 2019, 05:58 AM
1param(
2 [string]$ServerInstance = $OctopusParameters["ServerInstance"],
3 [string]$Database = $OctopusParameters["Database"],
4 [string]$package_version = $OctopusParameters["Octopus.Action.Package.PackageVersion"],
5 [string]$SourceFiles = $OctopusParameters["Octopus.Tentacle.CurrentDeployment.PackageFilePath"],
6 [string]$OutputDir = $OctopusParameters["OutputDir"]
7)
8
9$errorActionPreference = "stop"
10
11#################################### IMPORTANT! ####################################
12# Ensure that you set the following variables in Octopus Deploy: #
13# - ServerInstance #
14# - Database #
15# - OutputDir #
16# They will be used as the default parameters for this script at run time. #
17####################################################################################
18
19# Declaring variabes
20$deployed_by = ([Environment]::UserDomainName + "\" + [Environment]::UserName)
21$deployRequired = $false
22
23# Checking to see whether the __DeployLog exists in target database
24$CheckDeployLogExists = @'
25IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
26 WHERE TABLE_NAME = N'__DeployLog')
27 BEGIN
28 SELECT 'TRUE'
29 END
30ELSE
31 BEGIN
32 SELECT 'FALSE'
33 End
34'@
35
36$DeployLogExists = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $CheckDeployLogExists
37$DeployLogExists = $DeployLogExists[0]
38if($DeployLogExists -eq 'FALSE') {
39 Write-Warning "Table __DeployLog does not exist in $Database on $ServerInstance pre-deployment."
40}
41
42$getLastPackageVersion = @'
43SELECT TOP (1) package_version
44FROM [dbo].[__DeployLog]
45ORDER BY deploy_completed DESC
46'@
47
48# Condition 1: If there is no __DeployLog table we don't know the prior state so we need to deploy the package
49if($DeployLogExists -eq 'FALSE') {
50 Write-Output "There is no __DeployLog table on target database. Assuming re-deployment is required."
51 $deployRequired = $true}
52else{
53 # Condition 2: If the package version has changed we need to deploy the new package
54 $lastPackageVersion = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $getLastPackageVersion
55 try{
56 $lastPackageVersion = $lastPackageVersion[0]
57 }
58 catch{
59 Write-Warning "__DeployLog is empty"
60 $lastPackageVersion = "NULL"
61 }
62 if($lastPackageVersion -ne $package_version){
63 Write-Output "Package version ($package_version) differs from previous package version ($lastPackageVersion), re-deployment is required."
64 $deployRequired = $true
65 }
66}
67
68# If neither condition 1 or 2 above are met, __DeployLog indicates that this
69# package has already been successfully deployed - so we can skip the deployment
70if($deployRequired -eq $false){
71 Write-Output "Skipping the deployment because the __DeployLog table in $Database on $ServerInstance indicates that package $package_version has already been successfully deployed."
72}
73
74# If necesary, deploy database
75if($deployRequired){
76 $deploy_started = (Get-Date -Format "yyyy-MM-dd HH:mm:ss")
77 Write-Output "Deploy started at $deploy_started"
78
79 $targetDB = New-DatabaseConnection -ServerInstance $ServerInstance -Database $Database | Test-DatabaseConnection
80
81 try
82 {
83 # Defining name and path for SQL script
84 $updateScriptName = "update-$package_version.sql"
85 $updateScriptPath = "$OutputDir\$updateScriptName"
86
87 # Deploy the database
88 $syncResult = Sync-DatabaseSchema -Source $SourceFiles -Target $targetDB
89
90 # Export SQL script to disk and upload to Octopus
91 $syncResult.Updatesql | Out-File -FilePath $updateScriptPath
92 if (Test-Path $updateScriptPath) {
93 New-OctopusArtifact $updateScriptPath -Name $updateScriptName
94 }
95 }
96 catch #
97 {
98 $_.Exception.Message
99 "$($Database.Name;) couldn't be validated because $($_.Exception.Message)" | ForEach-Object{
100 write-error $_
101 }
102 }
103
104 $deploy_completed = (Get-Date -Format "yyyy-MM-dd HH:mm:ss")
105 Write-Output "Deploy completed at $deploy_completed"
106}
107
108# Checking if __DeployLog still exists following deployment
109# (it may have been dropped if it wasn't included in source code)
110$DeployLogExists = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $CheckDeployLogExists
111$DeployLogExists = $DeployLogExists[0]
112
113# Creates the __DeployLog table if it does not alreqady exist
114$CreateDeployLogTbl = @'
115CREATE TABLE [dbo].[__DeployLog](
116 [deploy_id] [int] IDENTITY(1,1) PRIMARY KEY,
117 [package_version] [varchar](255) NOT NULL,
118 [deploy_started] [datetime2](7) NOT NULL,
119 [deploy_completed] [datetime2](7) NOT NULL,
120 [deployed_by] [nvarchar](255) NULL
121 )
122GO
123'@
124
125if($DeployLogExists -eq "FALSE") {
126 Write-Warning "Table __DeployLog does not exist in $Database on $ServerInstance post-deployment. It may have been deleted. You should either add the table to your source code or your filter to avoid data loss."
127 Write-Output "Redeploying __DeployLog table"
128 Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $CreateDeployLogTbl
129}
130
131# Updating __DeployLog with info about this deployment
132$updateDeployLog = @"
133INSERT INTO [dbo].[__DeployLog]
134 ([package_version]
135 ,[deploy_started]
136 ,[deploy_completed]
137 ,[deployed_by])
138 VALUES
139 ('$package_version'
140 ,'$deploy_started'
141 ,'$deploy_completed'
142 ,'$deployed_by')
143GO
144"@
145
146if($deployRequired){
147 Write-Output "Updating __DeployLog in $Database on $ServerInstance with following data:"
148 Write-Output "package_version: $package_version"
149 Write-Output "deploy_started: $deploy_started"
150 Write-Output "deploy_completed: $deploy_completed"
151 Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $updateDeployLog
152}