All too often I find myself in projects where there is no efficient strategy around updating databases. Databases should be able to be migrated to the latest version without too much effort. If it’s too hard, developers will start sharing databases, or even worse, use a test environment database directly.
If you usually do migrations by comparing the schema of two databases, now is an opportunity for you to do something better. Besides schema and security, a database also consists of data, and data is troublesome. Large tables takes both time and resources to alter. A tool simply cannot generate resource efficient migrations, or for example figure out where the data in that dropped column should go instead.
Therefore you will always need a process or another tool to run transitional scripts beside the schema comparer. If you instead focus on the transitional script runner and have it logging which scripts that has been run to some persistent storage, you can use that log as a simple means to version your database.
Also, do not forget to include configuration data, or sample data for test and development, in your migration process.
Run Migrations with DbUp and VSTS (or TFS 2015)
A favorite transitional script runner of mine have long been DbUp.
DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date.
One way that you can get started with DbUp is by importing its NuGet-package in a .NET console project. But I prefer to invoke it through PowerShell. That way nothing needs to be compiled, and as long as you have access to your migration scripts you are good to go. The PowerShell way also makes a good match for deployment scenarios with Octopus Deploy or Release Management.
I have made a VSTS Build and Release Task for this purpose. But, if you would like to run DbUp elsewhere, the important part of the task is the following PowerShell script:
|
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 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 |
function Get-TempDir { return $env:LOCALAPPDATA } function Get-ResolvedPaths { param($rootDir) $patterns = @(); $patterns += 'dbup-core*\lib\net35\*.dll' $patterns += 'dbup-sqlserver*\lib\net35\*.dll' $patterns | ForEach-Object { Resolve-Path (Join-Path $rootDir $_) -ErrorAction SilentlyContinue } | Select-Object -ExpandProperty Path } function Get-DllPaths { $tempDir = Join-Path (Get-TempDir) 'DatabaseMigration' $paths = @(Get-ResolvedPaths $tempDir) if ($paths.Length -ne 2) { # 2 <= number of dlls $localDir = Join-Path $PSScriptRoot 'lib' $paths = @(Get-ResolvedPaths $localDir) } return $paths } $dllPaths = @(Get-DllPaths) $dllPaths | ForEach-Object { Add-Type -Path $_ } # Log output is lost after build task is run. This hack solves it. if (-not ([System.Management.Automation.PSTypeName]'VstsUpgradeLog').Type) { Add-Type -TypeDefinition @" using DbUp.Engine.Output; public class VstsUpgradeLog : IUpgradeLog { private System.Action<string> WriteHost { get; set; } public VstsUpgradeLog(System.Action<string> writeHost) { WriteHost = writeHost; } public void WriteInformation(string format, params object[] args) { WriteHost(string.Format(format, args).Trim()); } public void WriteWarning(string format, params object[] args) { // ## is separated from command text so that system.debug mode does not bail out WriteHost("##" + "vso[task.logissue type=warning;]" + string.Format(format, args)); } public void WriteError(string format, params object[] args) { WriteHost("##" + "vso[task.logissue type=error;]" + string.Format(format, args)); } } "@ -Language CSharp -ReferencedAssemblies $dllPaths } if (-not ([System.Management.Automation.PSTypeName]'FileSystemScriptProvider').Type) { # This is a FileSystemScriptProvider inspired of that is implemented in DbUp 4.0, with added ordering feature. Add-Type -TypeDefinition @" using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using DbUp.Engine; using DbUp.Engine.Transactions; using DbUp.Support; public enum FileSearchOrder { Filename = 0, FilePath = 1, FolderStructure = 2 } public class FileSystemScriptOptions { public FileSystemScriptOptions() { Encoding = Encoding.Default; } public bool IncludeSubDirectories { get; set; } public FileSearchOrder Order { get; set; } public Func<string, bool> Filter { get; set; } public Encoding Encoding { get; set; } } public class FileSystemScriptProvider : IScriptProvider { private readonly string directoryPath; private readonly Func<string, bool> filter; private readonly Encoding encoding; private FileSystemScriptOptions options; private int nextRunGroupOrder = 1; public FileSystemScriptProvider(string directoryPath):this(directoryPath, new FileSystemScriptOptions()) { } public FileSystemScriptProvider(string directoryPath, FileSystemScriptOptions options) { if (options==null) throw new ArgumentNullException("options"); this.directoryPath = directoryPath.Replace("/","\\").EndsWith("\\") ? directoryPath.Substring(0, directoryPath.Length - 1) : directoryPath; this.filter = options.Filter; this.encoding = options.Encoding; this.options = options; } public IEnumerable<SqlScript> GetScripts(IConnectionManager connectionManager) { var files = Directory.GetFiles(directoryPath, "*.sql", ShouldSearchSubDirectories()).AsEnumerable(); if (this.filter != null) { files = files.Where(filter); } var infos = files.Select(f => new FileInfo(f)); if (options.Order == FileSearchOrder.Filename) { infos = infos.OrderBy(i => i.Name); } if (options.Order == FileSearchOrder.FilePath) { infos = infos.OrderBy(i => i.FullName); } return infos.Select(i => SqlScriptFromFile(i)).ToArray(); } private SqlScript SqlScriptFromFile(FileInfo file) { using (FileStream fileStream = new FileStream(file.FullName, FileMode.Open, FileAccess.Read)) { var fileName = file.FullName.Substring(directoryPath.Length + 1); var options = new SqlScriptOptions() { ScriptType = ScriptType.RunOnce, RunGroupOrder = this.nextRunGroupOrder++ }; return SqlScript.FromStream(fileName, fileStream, encoding, options); } } private SearchOption ShouldSearchSubDirectories() { return options.IncludeSubDirectories ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly; } } "@ -Language CSharp -ReferencedAssemblies $dllPaths } $configFunc = { param($configuration) $configuration.ScriptExecutor.ExecutionTimeoutSeconds = 0 } function Write-Information { # Used to mock out logs in the tests param($message) Write-Host $message } function New-SchemaIfNotExists { # Work around for https://github.com/DbUp/DbUp/issues/346 param($ConnectionString, $SchemaName) if ($SchemaName -eq 'dbo') { return } $query = "IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'$SchemaName') EXEC(N'CREATE SCHEMA [$SchemaName]')" # PowerShell versions makes Invoke-Sqlcmd with -ConnectionString unreliable. $connection = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString $connection.Open() $cmd = New-Object System.Data.SqlClient.SqlCommand -ArgumentList $query, $connection $cmd.ExecuteNonQuery() $cmd.Dispose() $connection.Dispose() } [Action[string]]$infoDelegate = { param($message) Write-Information $message } function Update-DatabaseWithDbUp { param( [Parameter(Mandatory = $true)][string]$ConnectionString, [string]$ScriptPath = '.', [ValidateSet('NullJournal', 'SqlTable')][string]$Journal = 'SqlTable', [string]$Filter = '.*', [string]$Encoding = "Default", [ValidateSet('NoTransactions', 'TransactionPerScript', 'SingleTransaction')] [string]$TransactionStrategy = 'TransactionPerScript', [string]$JournalSchemaName = 'dbo', [string]$JournalTableName = '_SchemaVersions', [ValidateSet('LogScriptOutput', 'Quiet')] [string]$Logging = 'Quiet', [ValidateSet('SearchAllFolders', 'SearchTopFolderOnly')] [string]$SearchMode = 'SearchTopFolderOnly', [ValidateSet('Filename', 'FilePath', 'FolderStructure')] [string]$Order = 'Filename', [bool]$VariableSubstitution = $false, [string]$VariableSubstitutionPrefix = "DbUp" ) $filterFunc = { param([string]$file) return $file -match $Filter } $options = New-Object FileSystemScriptOptions $options.Filter = $filterFunc if ($Order -eq 'Filename') { $options.Order = 0 } elseif ($Order -eq 'FilePath') { $options.Order = 1 } else { $options.Order = 2 } if ($SearchMode -eq 'SearchAllFolders') { $options.IncludeSubDirectories = $true } if ($Encoding -ne 'Default') { $options.Encoding = [System.Text.Encoding]::GetEncoding([int]::Parse($Encoding)) } $scriptProvider = New-Object FileSystemScriptProvider -ArgumentList $ScriptPath, $options $ScriptPath = Resolve-Path $ScriptPath $dbUp = [DbUp.DeployChanges]::To $dbUp = [SqlServerExtensions]::SqlDatabase($dbUp, $ConnectionString) $dbUp = [StandardExtensions]::WithScripts($dbUp, $scriptProvider) if ($TransactionStrategy -eq 'TransactionPerScript') { $dbUp = [StandardExtensions]::WithTransactionPerScript($dbUp) } elseif ($TransactionStrategy -eq 'SingleTransaction') { $dbUp = [StandardExtensions]::WithTransaction($dbUp) } else { $dbUp = [StandardExtensions]::WithoutTransaction($dbUp) } $dbUp = [StandardExtensions]::LogTo($dbUp, (New-Object VstsUpgradeLog $infoDelegate)) if ($Logging -eq 'LogScriptOutput') { $dbUp = [StandardExtensions]::LogScriptOutput($dbUp) } if ($Journal -eq "NullJournal") { $dbUp = [StandardExtensions]::JournalTo($dbUp, (New-Object DbUp.Helpers.NullJournal)) } else { New-SchemaIfNotExists -SchemaName $JournalSchemaName -ConnectionString $ConnectionString $dbUp = [SqlServerExtensions]::JournalToSqlTable($dbUp, $JournalSchemaName, $JournalTableName) } $dbUp.Configure($configFunc) if ($VariableSubstitution) { if (-not [string]::IsNullOrEmpty($VariableSubstitutionPrefix)) { $VariableSubstitutionPrefix += '_' } Get-ChildItem "env:\$VariableSubstitutionPrefix*" | ForEach-Object { $name = $_.Name.Substring($VariableSubstitutionPrefix.Length) $dbUp = [StandardExtensions]::WithVariable($dbUp, $name, $_.Value) } } else { $dbUp = [StandardExtensions]::WithVariablesDisabled($dbUp) } $result = $dbUp.Build().PerformUpgrade() if (!$result.Successful) { $errorMessage = "" if ($null -ne $result.Error) { $errorMessage = $result.Error.Message } Write-Information "##vso[task.logissue type=error;]Database migration failed. $errorMessage" Write-Information "##vso[task.complete result=Failed;]" } return $result.Successful } function New-DatabaseWithDbUp { param([string]$ConnectionString) $for = [DbUp.EnsureDatabase]::For [SqlServerExtensions]::SqlDatabase($for, $ConnectionString, (New-Object VstsUpgradeLog $infoDelegate)) } |
Run Your Tools Often
As with all deployment tools, you should run them often. The more you run them the higher the probability gets that you will have found all mistakes before deployment is made to the production environment. This does not only mean that the same tool should be run in your CI builds, but also that each developer should use it to set up their own personal database. Never underestimate the value of dogfooding your deployment tools!
Non-transitional Changes
All database objects do not need to be changed transitionally like tables. For example regarding stored procedures and security objects, a more pragmatic approach is to keep a set of idempotent scripts that are run on each deploy. This is supported by the PowerShell script above with the Journal parameter.
8 Responses
Jonathan Counihan
Thanks for the post.
I’m trying to do something similar, but I can’t get DbUp to log any changes to the powershell console.
My config looks like this:
$dbUp = [DbUp.DeployChanges]::To
$dbUp = [SqlServerExtensions]::SqlDatabase($dbUp, $connectionString)
$dbUp = [StandardExtensions]::LogScriptOutput($dbUp)
$dbUp = [StandardExtensions]::LogToConsole($dbUp)
$dbUp = [StandardExtensions]::WithScriptsEmbeddedInAssembly($dbUp, $ScriptsAssembly)
Do you actually get output in console? Like the results of the scripts?
Johan Classon
Normally I am happy with only getting errors and a list of scripts that is run, which is handled by calling the LogToConsole() method. IMHO, migration scripts are (should be?) run so often that one can have faith that once that they are invoked they do the right thing.
Until now, I never tried to use LogScriptOutput()… But I tested now, and I do get output in the console from all SQL print statements, and select statements. (DbUp executes the scripts with a simple IDbCommand.ExecuteReader())
This is how a dummy migration run looks for me:
If you build a console application, do you get log output then? What type of log output are you after? Number of affected rows?
Jonathan Counihan
Actually all I am after is the DbUp output to indicate where in the process it gets to, in case something fails. (we are trying to use this for our CI server)
So far I cannot get DbUp to print anything to the powershell console, even the “Beginning database upgrade” message etc. I was trying different combos of $dbUp = [StandardExtensions]::LogToConsole($dbUp) and so far no luck.
Next step is to use a exe wrapper around the DbUp dll if I can’t get the powershell script to work directly.
Devops Online Training
great article i ever seen, thanks for sharing ,this article was very useful for me while learning the devops, thanks for sharing please share more article like this.
Johan Classon
Glad you found it useful. Good luck with your path down the devops road!
JamesD
Fantastic work, the developers usage page is very vague, this was exactly what I needed.
I have two deployed changes: Schema and Recreatable.
Schema is versioned using the SchemaVersion table
While the Recreatables deploy will always run drop-recreate SP changes, this tutorial set me on the right path to achieve this, many thanks.
Nitin Jain
I am trying to run above scripts… Could you please let me know path parameters are we need to set before use above line of code…
While running whole copied script it give multiple error..
Can you please share what is correct way to use your PS scripts and parameter lists to be set before or update in code.
Johan Classon
I think you can find how to invoke the PowerShell script above here:
https://github.com/johanclasson/vso-agent-tasks/blob/master/DbUpMigration/task/Update-Database.ps1
https://github.com/johanclasson/vso-agent-tasks/blob/master/DbUpMigration/task/Update-DatabaseWithDbUp.ps1