DbUp is a nice open source project that allows you to deploy Sql-scripts via a .Net executable, thus enabling deployment via a pipeline like Octopus Deploy. DbUp tracks which scripts have already been run on your database so you don’t have to worry about double-updates to your database.
The basic problems with idempotency and databases remain of course, but that is a completely different story and you still have to manage that.
DbUp has a nice fluent interface, but doing programmatic configuration unfortunately rather breaks that. It took me a while to figure out so here is how I managed to change the excution timeout in my DbUp deployment C# program for a long running script.
First use DeploymentChanges.To.SqlDatabase(connectionstring).WithScriptsEmbeddedInAssembly( … ) to create a variable which is an UpgradeEngineBuilder.
Then invoke the Configuration callback action on the UpgradeEngineBuilder like so:
c.ScriptExecutor.ExecutionTimeoutSeconds = 30 * 60; // 30 minutes in seconds
Console.WriteLine("Configure ExecutionTimeoutSeconds to " + c.ScriptExecutor.ExecutionTimeoutSeconds);
After that you can use the fluent interface again to invoke the Sql-script:
var upgradeEngine = upgradeEngineBuilder.LogToConsole().Build();
var result = upgradeEngine.PerformUpgrade();